Как присвоить числовое значение слову для сложения в Excel 2016: пошаговое руководство

Как в Excel присвоить цену разным видам вина и посчитать общую сумму продаж по бутылкам

Часто в учёте продаж товаров, например бутылок вина разных видов, нужно быстро подсчитать итоговую сумму продаж на основе количества каждого вида и их цены. В этой статье разберём, как в Excel сделать такой подсчёт, когда для каждого столбца с количеством бутылок необходимо умножить это количество на цену, соответствующую каждому виду вина, и получить общую сумму по строке.

Проблема

У пользователя есть таблица с разными видами вина в столбцах и количеством продаж в каждой строке. В конце строки надо вывести общий доход — сумму умножения количества проданных бутылок каждого вида на их цену.

Трудности возникли из-за того, что в испанской версии Excel используется точка с запятой ; вместо запятой , в формулах, что приводит к ошибкам #NOMBRE# или #VALOR# при неправильном синтаксисе. Также некоторые решения, найденные в интернете, не работали корректно.

Решения

Для подсчёта суммы продаж по бутылкам рекомендуются два основных варианта — с использованием функций SUMPRODUCT и MMULT. Разберём подробнее каждый из них.

Вариант 1. Формула SUMPRODUCT с индексами и поиском (рекомендуемый)

Идея — создать отдельную таблицу соответствия между названиями видов вина и их ценами, а затем в каждой строке использовать формулу, которая:

• Определяет цену для каждого столбца с помощью функций INDEX и MATCH.
• Перемножает количество бутылок с ценами и суммирует получившиеся продукты.

Шаги реализации:

1. Создайте таблицу прайс-листа. Например, в столбце O перечислите виды вин, а рядом в столбце P — их цены (числа).
2. Добавьте формулу в колонку итоговой суммы продаж. На примере с вашей таблицей и прайсом формула в ячейке итогового столбца (например, L2) будет такой:

   =SUMPRODUCT(A2:J2; INDEX($P$2:$P$11; MATCH($A$1:$J$1; $O$2:$O$11; 0)))

   Обратите внимание на использование точек с запятой ; — это важно для испанской версии Excel.

3. Протяните формулу вниз по всем строкам с данными.

Эта формула работает так:

• MATCH($A$1:$J$1; $O$2:$O$11; 0)
• INDEX($P$2:$P$11; ...)
• SUMPRODUCT перемножает количество бутылок по каждому виду на цену и суммирует результаты.

Такой подход универсален: можно добавить новые виды вина в прайс и не менять основную формулу. При этом Excel корректно обрабатывает пустые ячейки.

Вариант 2. Использование функции MMULT

Этот способ подходит, если порядок столбцов с вином в вашей основной таблице точно совпадает с порядком цен в прайс-листе.

Пример формулы в испанской версии Excel (например, в ячейке с итогом U2):

=MMULT(N(+$J2:$S2); $AC$2:$AC$11)

Объяснение:

• N() — функция, превращающая пустые ячейки в нули, чтобы MMULT смогла корректно работать с только числовыми данными.
• MMULT — матричное умножение, которая перемножает массив количества по каждому виду на массив цен и возвращает сумму.

Этот метод работает быстрее для больших таблиц, но имеет меньше гибкости — нужно строго соблюдать порядок столбцов и цен.

Особенности, ошибки и как их избежать

• В испанской (и многих других локализованных) версиях Excel нужно использовать точку с запятой ; вместо запятой , в формулах.
• Ошибка #NOMBRE# (в английской Excel это #NAME?) означает, что Excel не распознал название функции — обычно причина неправильного написания функции или использования локализованных версий с другими именами. Убедитесь, что функции написаны правильно для испанской версии.
• Ошибка #VALOR# возникает, если в формуле используются некорректные аргументы (например, текст вместо числа). Применение функции N() помогает заменить пустые значения на 0.
• При использовании формул массива (если надо) в старых версиях Excel нужно нажимать Ctrl + Shift + Enter. Однако в последних версиях Excel многие массивные формулы работают без этого.

Улучшения и рекомендации по оформлению таблицы

• Храните цены в отдельной таблице-прайс-листе, чтобы не дублировать их в основной таблице — так меньше ошибок и проще обновлять прайс.
• Используйте «замороженные» заголовки, чтобы удобно ориентироваться при прокрутке больших таблиц.
• Центрируйте значения количества бутылок в ячейках, чтобы визуально связать их с названиями вин.
• Можно добавить дополнительные строчки для статистики — например, суммарные продажи по каждому виду, анализ движения товара и т.п.
• Для упрощения работы создайте именованные диапазоны для прайс-листа и диапазонов с данными — так формулы будут легче читать и поддерживать.

Выводы

Для подсчёта стоимости продаж по разным видам вина в Excel рекомендуется:

• Создать таблицу с ценами для каждого вида и использовать функцию SUMPRODUCT с INDEX и MATCH для универсальной и понятной формулы.
• Если порядок столбцов строго фиксирован и совпадает с порядком цен, можно применить матричное умножение через MMULT.
• Всегда учитывайте локализацию Excel — в испанской версии используйте точку с запятой ; в формулах.
• Для уменьшения ошибок и упрощения поддержки таблицы не дублируйте цены, а храните их в отдельном прайс-листе.

Следуя этим рекомендациям, вы сможете избежать распространённых ошибок и быстро получать нужные итоги, экономя время и повышая точность учёта.