Добрый день.
Когда-то, написать самостоятельно формулу в Excel - для меня было чем-то невероятным. И даже, несмотря на то, что часто приходилось работать в этой программе, ничего кроме текста не набивал…
Как оказалось, большинство формул не представляют из себя ничего сложного и с ними легко можно работать, даже, начинающему пользователю компьютера. В статье, как раз, хотелось бы раскрыть самые нужные формулы, с которыми чаще всего и приходится работать…
И так, начнем…
1. Основные операции и азы. Обучение основам Excel.
Все действия в статье будут показываться в Excel версии 2007г.
После запуска программы Excel - появляется окно со множеством клеточек - наша таблица. Главная особенность программы в том, что она может считать (как калькулятор) ваши формулы, которые вы напишете. Кстати, добавить формулу можно в каждую ячейку!
Формула должна начинаться со знака «=». Это обязательное условие. Дальше вы пишите то, что вам нужно посчитать: например, «=2+3» (без кавычек) и нажимаете по клавише Enter - в результате вы увидите, что в ячейке появился результат «5». См. скриншот ниже.
Важно! Несмотря на то, что в ячейке А1 написано число «5» - оно считается по формуле («=2+3»). Если в соседней ячейке просто текстом написать «5» - то при наведении курсора на эту ячейку - в редакторе формулы (строка сверху, Fx ) - вы увидите простое число «5».
А теперь представьте, что в ячейку вы можете писать не просто значение 2+3, а номера ячеек, значения которых нужно сложить. Допустим так «=B2+C2».
Естественно, что в B2 и C2 должны быть какие-нибудь числа, иначе Excel покажет нам в ячейке A1 результат равный 0.
И еще одно важное замечание…
Когда вы копируете ячейку, в которой есть формула, например A1 - и вставляете ее в другую ячейку - то копируется не значение «5», а сама формула!
Причем, формула изменится прямо-пропорционально: т.е. если A1 скопировать в A2 - то формула в ячейке A2 будет равна «=B3+C3». Excel сам меняет автоматически вашу формулу: если A1=B2+C2, то логично, что A2=B3+C3 (все цифры увеличились на 1).
Результат, кстати, в A2=0, т.к. ячейки B3 и С3 не заданы, а значит равны 0.
Таким образом можно написать формулу один раз, а затем ее скопировать во все ячейки нужного столбца - и Excel сам произведет расчет в каждой строчки вашей таблицы!
Если вы не хотите, чтобы B2 и С2 изменялись при копировании и всегда были привязаны к этим ячейкам, то просто добавьте к ним значок «$». Пример ниже.
Таким образом, куда бы вы ни скопировали ячейку A1 - она всегда будет ссылаться на привязанные ячейки.
2. Сложение значений в строках (формула СУММ и СУММЕСЛИМН)
Можно, конечно, каждую ячейку складывать, делая формулу A1+A2+A3 и т.п. Но чтобы так не мучатся, есть в Excel специальная формула, которая сложит все значения в ячейках, которые вы выделите!
Возьмем простой пример. Есть на складе несколько наименований товара, причем мы знаем, сколько каждого товара по отдельности в кг. есть на складе. Попробуем посчитать, а сколько всего в кг. груза на складе.
Для этого переходим в ячейку, в которой будет показываться результат и пишем формулу: «=СУММ(C2:C5)». См. скриншот ниже.
В результате все ячейки в выделенном диапазоне будут просуммированы, а вы увидите результат.
2.1. Сложение с условием (с условиями)
А теперь представим, что у нас есть определенные условия, т.е. сложить надо не все значения в ячейках (Кг, на складе), а лишь определенные, скажем, с ценой (1 кг.) меньше 100.
Для этого есть замечательная формула «СУММЕСЛИМН «. Сразу же пример, а затем пояснение каждого символа в формуле.
=СУММЕСЛИМН(C2:C5 ;B2:B5 ;«<100» ) , где:
C2:C5 - та колонка (те ячейки), которые будут суммироваться;
B2:B5
- колонка, по которой будет проверяться условие (т.е. цена, например, менее 100);
«<100»
- само условие, обратите внимание, что условие пишется в кавычках.
Ничего сложного в этой формуле нет, главное соблюдать соразмерность: C2:C5;B2:B5 - правильно; C2:C6;B2:B5 - неправильно. Т.е. диапазон суммирования и диапазон условий должны быть соразмерны, иначе формула вернет ошибку.
Важно! Условий для суммы может быть много, т.е. можно проверять не по 1-й колонке, а сразу по 10, задав множество условий.
3. Подсчет количества строк, удовлетворяющих условиям (формула СЧЁТЕСЛИМН)
Довольно часто-встречающаяся задача: подсчитать не сумму значений в ячейках, а количество таких ячеек, удовлетворяющих определенным условиям. Иногда, условий очень много.
И так… начнем.
Для подсчета товаров в нужной ячейке написали следующую формулу (см. выше):
=СЧЁТЕСЛИМН(B2:B5 ;«>90» ) , где:
B2:B5
- диапазон, по которому будут проверять, по заданному нами условию;
«>90» -само условие, заключается в кавычки.
Теперь попробуем немного усложнить наш пример, и добавим счет еще по одному условию: с ценой больше 90 + количество на складе менее 20 кг.
Формула приобретает вид:
=СЧЁТЕСЛИМН(B2:B6;»>90″;C2:C6 ;«<20» )
Здесь все осталось таким же, кроме еще одного условия (C2:C6;»<20″ ). Кстати, таких условий может быть очень много!
Понятно, что для такой маленькой таблицы никто не будет писать такие формулы, а вот для таблицы из нескольких сотен строк - это уже совсем другое дело. Для примера же эта таблица - более чем наглядна.
4. Поиск и подстановка значений из одной таблицы в другую (формула ВПР)
Представим, что к нам пришла новая таблица, с новыми ценниками для товара. Хорошо, если наименований 10-20 - можно и в ручную их все «перезабить». А если таких наименований сотни? Гораздо быстрее, если бы Excel самостоятельно нашел в совпадающие наименования из одной таблицы в другой, а затем скопировал новые ценники в старую нашу таблицу.
Для такой задачи используется формула ВПР . В свое время сам «мудрил» с логическими формулами «ЕСЛИ» пока не встретил эту замечательную штуку!
И так, начнем…
Вот наш пример + новая таблица с ценниками. Сейчас нам нужно автоматически подставить новые ценники из новой таблицы в старую (новые ценники красные ).
Ставим курсор в ячейку B2 - т.е. в первую ячейку, где нам нужно изменить ценник автоматически. Далее пишем формулу, как на скриншоте ниже (после скриншота будет подробное пояснение к ней).
=ВПР(A2 ;$D$2:$E$5 ;2 ) , где
A2 - то значение, которое мы будем искать, чтобы взять новый ценник. В нашем случае ищем в новой таблице слово «яблоки».
$D$2:$E$5 - выделяем полностью нашу новую таблицу (D2:E5, выделение идет от верхнего левого угла к правому нижнему по диагонали), т.е. там, где будет производится поиск. Знак «$» в этой формуле необходим для того, чтобы при копировании этой формулы в другие ячейки - D2:E5 не менялись!
Важно! Поиск слова «яблоки» будет вестись только в первой колонке вашей выделенной таблицы, в данном примере «яблоки» будет искаться в колонке D.
2 - Когда слово «яблоки» будет найдено, функция должна знать, из какого столбика выделенной таблицы (D2:E5) скопировать нужное значение. В нашем примере копировать из колонки 2 (E), т.к. в первой колонке (D) мы производили поиск. Если ваша выделенная таблица для поиска будет состоять из 10 колонок, то в первой колонке производится поиск, а со 2 по 10 колонки - вы можете выбрать число для копирования.
Чтобы формула =ВПР(A2;$D$2:$E$5;2) подставила новые значения и для других наименований товара - просто скопируйте ее в другие ячейки столбца с ценниками товара (в нашем примере копируйте в ячейки B3:B5). Формула автоматически произведет поиск и копирование значения из нужной вам колонки новой таблицы.
5. Заключение
В статье мы рассмотрели основы работы с Excel, с того как начать писать формулы. Привели примеры самых распространенных формул, с которыми очень часто приходится работать большинству, кто работает в Excel.
Надеюсь что кому-то пригодятся разобранные примеры и помогут ускорить его работу. Удачных экспериментов!
А какие формулы используете вы, можно ли как-то упростить формулы приведенные в статье? Например, на слабых компьютерах, при изменении каких-то значений в больших таблицах, где производятся автоматически расчеты - компьютер зависает на пару секунд, пересчитывая и показывая новые результаты…
Функция СУММ относится к категории: «Математические». Нажмите комбинацию горячих клавиш SHIFT+F3 для вызова мастера функций, и вы быстро ее там найдете.
Использование этой функции существенно расширяет возможности процесса суммирования значений ячеек в программе Excel. На практике ее рассмотрим возможности и настройки при суммировании нескольких диапазонов.
Как в таблице Excel посчитать сумму столбца?
Просуммируем значение ячеек A1, A2 и A3 с помощью функции суммирования. За одно и узнаем, для чего используется функция сумм.
В результате в ячейке A4 отображается результат вычисления. Саму функцию и ее параметры можно увидеть в строке формул.
Примечание. Вместо использования инструмента «Сумма» на главной панели, можно в ячейку A4 сразу ввести функцию с параметрами вручную. Результат будет одинаковый.
Поправка автоматического распознания диапазонов
При введении функции с помощью кнопки на панели инструментов или при использовании мастера функций (SHIFT+F3). функция СУММ() относится к группе формул «Математические». Автоматически распознанные диапазоны не всегда являются подходящими для пользователя. Их можно при необходимости быстро и легко поправить.
Допустим, нам нужно просуммировать несколько диапазонов ячеек, как показано на рисунке:
- Перейдите в ячейку D1 и выберите инструмент «Сумма».
- Удерживая клавишу CTRL дополнительно выделите мышкой диапазон A2:B2 и ячейку A3.
- После выделения диапазонов нажмите Enter и в ячейке D4 сразу отобразиться результат суммирования значений ячеек всех диапазонов.
Обратите внимание на синтаксис в параметрах функции при выделении нескольких диапазонов. Они разделены между собой (;).
В параметрах функции СУММ могут содержаться:
- ссылки на отдельные ячейки;
- ссылки на диапазоны ячеек как смежные, так и несмежные;
- целые и дробные числа.
В параметрах функций все аргументы должны быть разделены точкой с запятой.
Для наглядного примера рассмотрим разные варианты суммирования значений ячеек, которые дают один и тот же результат. Для этого заполните ячейки A1, A2 и A3 числами 1, 2 и 3 соответственно. А диапазон ячеек B1:B5 заполните следующими формулами и функциями:
- =A1+A2+A3+5;
- =СУММ(A1:A3)+5;
- =СУММ(A1;A2;A3)+5;
- =СУММ(A1:A3;5);
- =СУММ(A1;A2;A3;5).
При любом варианте мы получаем один и тот же результат вычисления – число 11. Последовательно пройдите курсором от B1 и до B5. В каждой ячейке нажмите F2, чтобы увидеть цветную подсветку ссылок для более понятного анализа синтаксиса записи параметров функций.
Одновременное суммирование столбцов
В Excel можно одновременно просуммировать несколько смежных и несмежных столбцов.
Заполняете столбцы, так как показано на рисунке:
- Выделите диапазон A1:B3 и удерживая клавишу CTRL так же выделите столбец D1:D3.
- На панели «Главная» щелкните по инструменту «Сумма» (или нажмите ALT+=).
Примечание. Данная функция автоматически подставляет формат ячеек которых она суммирует.
Для большинства пользователей Excel - это программа, которая умеет работать с таблицами и в которую можно поместить соответствующую информацию в табличном виде. Как правило, мало кто знает все возможности данной программы, да и вряд ли кто-то об этом сильно задумывался. А Excel способен выполнять большое количество операций, и одна из них – считать числа.
Простые операции в MS Excel
В программу Microsoft Excel как бы встроен суперсовременный калькулятор с большим количеством функций и возможностей.
Итак, первое, что нужно знать: все вычисления в Excel называются формулами и все они начинаются со знака «равно» (=). Например, нужно посчитать сумму 5+5. Если выбрать любую ячейку и внутри нее написать 5+5, а затем нажать кнопку Enter, то программа ничего не посчитает – в ячейке будет просто написано «5+5». А вот если перед этим выражением поставить знак равно (=5+5), тогда Excel выдаст нам результат, то есть 10.
Также нужно знать основные арифметические операторы для работы в программе Excel. Это стандартные функции: сложение, вычитание, умножение и деление. А также Excel предлагает возведение в степень и процент. С первыми четырьмя функциями все понятно. Возведение в степень пишется как ^ (Shift+6). Например, 5^2 это будет пять в квадрате или пять во второй степени.
А что касается процента, то если поставить знак % после любого числа, оно будет делиться на 100. Например, если написать 12%, то получится 0,12. С помощью этого знака легко считать проценты. Например, если нужно посчитать 7 процентов из 50, то формула будет выглядеть так: =50*7%.
Одна из популярных операций, которую часто делают в Excel – это расчет суммы. Допустим, есть таблица с полями «Наименование», «Количество», «Цена» и «Сумма». Все они заполнены, только поле «Сумма» - пустое. Чтобы посчитать сумму и заполнить автоматически свободный столбец, сначала нужно выбрать ячейку, куда надо писать формулу, и поставить знак равно. Затем щелкаете мышкой на нужное число в поле «Количество», набираете знак умножения, после этого щелкаете уже на число в поле «Цена» и нажимаете Enter. Программа посчитает это выражение. Если нажать на ячейку суммы, то можно увидеть примерно такую формулу: = B2*C2. Это означает, что были посчитаны не какие-то конкретные числа, а числа, которые были в этих ячейках. Если написать другие числа в этих же ячейках, Excel автоматически пересчитает формулу - и значение суммы изменится.
Если же нужно, например, посчитать количество всех товаров, которые записаны в таблице, это можно сделать, выбрав значок автосуммы в панели инструментов (он похож на букву Е). После этого нужно будет указать диапазон ячеек, которые нужно посчитать (в данном случае все числа в поле «Количество»), нажать Enter - и программа выдаст полученное значение. Также можно сделать это вручную, поочередно указывая все ячейки поля «Количество» и ставя между ними знак сложения. (=A1+A2+…A10). Результат получится тот же.
Если Вы работаете в программе Excel: строите графики, составляете таблицы, различные отчеты и прочее, то, чтобы сложить определенные данные Вам не потребуется искать калькулятор. Сумма чисел в Excel рассчитывается в пару кликов мышкой, и сделать это можно различными способами.
Используя выделение
Самый простой – позволит сложить все числа, которые будут в выбранной области. Выделите необходимый диапазон и посмотрите на строку состояния. Здесь будет указано «Среднее» для всех значений, «Количество» выделенных ячеек и «Сумма» значений. Выделять можете пустые блоки и те, где есть текст, поскольку прибавляются только числа.
Если в Вашей таблице есть строка «Итоги» – такой способ не подойдет. Конечно, можно вписать туда число, но если данные для таблицы изменятся, то нужно не забыть изменить их и в блоке «Итоги» . Это не совсем удобно, да и в Эксель можно применить другие способы, которые будут автоматически пересчитывать результат в блоке.
С помощью автосуммы
Просуммировать числа в столбце можно используя кнопку «Автосумма» . Для этого, выделите пустую ячейку в столбце, сразу под значениями. Затем перейдите на вкладку «Формулы» и нажмите «Автосумма» . Эксель автоматически выделит верхние блоки, до первого пустого. Нажмите «Enter» , чтобы всё посчиталось.
Также можете сначала выделить блоки в столбце, учитывая и пустые, и с текстом – они просто не будут учтены при расчете, а потом нажать «Автосумма» . Результат появится в первой пустой ячейке выделенного столбца.
Используя формулу
Сделать нужный нам расчёт в Excel можно используя всем знакомую математическую формулу. Поставьте «=» в нужной ячейке, затем мышкой выделяйте все нужные. Между ними не забывайте ставить знак плюса. Потом нажмите «Enter» .
Самый удобный способ для расчета – это использование функции СУММ. Поставьте «=» в ячейке, затем наберите «СУММ» , откройте скобку «(» и выделите нужный диапазон. Поставьте «)» и нажмите «Enter» .
Также прописать функцию можно прямо в строке формул.
Если в качестве аргумента для функции указать диапазон блоков, в которых, помимо чисел, содержатся и текст, и пустые блоки – функция вернет правильный результат. Это потому, что для расчета будут учитываться только ячейки с числами.
Оценить статью:Excel – мощный редактор для работы с большим объемом данных и ведения различных расчётов при помощи формул. Но как правило, не каждый пользователь знает про все возможности, которые есть в этой программе. Например, как в Excel посчитать сумму в столбце. Некоторые по незнанию используют для этого калькулятор. В данной статье будут рассмотрены различные формулы, которые могут пригодиться большинству офисных работников.
Для демонстрации возможностей этой функции, нужно будет сделать следующие действия.
- Первым делом следует создать какую-нибудь простую таблицу с несколькими значениями. Разместим их всех в один столбик.
- Затем выделите все ячейки. После этого нажмите на иконку «Суммы», расположенную в правой части панели инструментов.
- В результате этого редактор автоматически посчитает сумму и выведет ее на следующей строке.
Для того чтобы вывести итоговый результат в отдельном месте, нужно сделать немного иные действия.
- Снова нажмите на иконку «Суммы».
- В результате этого Эксель предложит сосчитать всё то, что находится выше. Но там ведь находятся и пустые ячейки.
- Поэтому выделяем только те, которые нам нужны, иначе будут считаться и пустые значения (в момент выделения ваш курсор примет вид белого крестика).
- Для того чтобы изменения вступили в силу, нужно нажать на клавиатуре на клавишу Enter .
Строка состояния
Существует еще один очень простой способ. Узнать общую сумму значений в ячейках можно обычным выделением. Как только вы это сделаете, результат отобразится в строке состояния, расположенной в нижней части программы.
Кроме этого, мы увидим количество элементов и их среднее значение.
Минус данного метода в том, что результат сложения на листе нигде не выводится.
Использование функций
Найти сумму цифр в ячейках можно и при помощи формул. Делается это следующим образом.
- Кликните на какую-нибудь ячейку.
- Перейдите в строку ввода уравнений.
- Введите там следующий текст.
- В результате этого у вас появится подсказка, что дальше нужно вводить числа.
- Укажите там диапазон ячеек, в которых находятся значения. Вводить нужно сначала букву, а потом цифру.
- Либо вы можете просто выделить эти ячейки.
- В итоге должно получиться следующее.
- Далее нажмите на клавиатуре на клавишу Enter . Только после этого вы сможете узнать результат.
Преимущество данного метода в том, что вы можете вручную указывать выборочный диапазон ячеек.
Суммирование произведений двух столбцов
Иногда на практике нужно вывести не просто сумму, но еще сделать дополнительные математические действия в разных колонках. Например, как в представленном случае.
Для вывода общей суммы необходимо сделать следующие действия.
- Сделайте активной нужную ячейку.
- Кликните на строку ввода формулы.
- Напишите следующую команду.
- Выделите вручную значения в одной из колонок. Например, в нашем случае выделим столбец «Количество».
Если в формуле автоматически не появится закрывающая скобка, ее нужно будет добавить вручную.
- Затем сдвигаемся в формуле правее и ставим символ «*» (знак умножения). Если вы работаете на ноутбуке, то нужно будет нажать на сочетание клавиш Shift +8 .
- После этого пишем еще раз следующий код.
- На этот раз выделяем другой столбец.
- Обязательно проверяем наличие закрывающей скобки.
Для завершения нужно нажать на клавишу Enter . В результате этого вы увидите сумму произведения двух столбцов.
Так же бывают случаи, когда нужно складывать не всё подряд, а лишь некоторую часть ячеек. Для этого нужно использовать дополнительные условия, которые будут служить аналогом фильтра значений.
Проверку можно делать как по числовому, так и по текстовому формату данных. Рассмотрим оба случая.
Текстовое значение
- Для начала добавим в таблице новую строку для вычислений.
- Перейдите в нужную ячейку. Затем кликните на строку ввода формул.
- Введите там следующую команду.
- В результате этого редактор Excel выведет вам подсказку, что нужно сначала указать диапазон значений, затем критерий отбора и в конце сам диапазон суммирования.
- Выделяем сначала колонку, по которой будем делать проверку. В нашем случае это столбец «Статус», в котором есть два состояния – «пусто» и «продано».
- После выделения обязательно добавляем символ «;» (точка с запятой), иначе будет ошибка в формуле. Затем пишем слово, по которому будем делать фильтр. В нашем случае это «Продано». Писать необходимо в кавычках. Это обязательно. Разумеется, после этого снова ставим символ «;».
- Теперь выделяем тот столбец, по которому будут происходить расчеты.
- Добавляем закрывающую скобку «)».
- Для проверки работы формулы нажимаем на клавиатуре на клавишу Enter . В результате описанных выше действий должны посчитаться значения только тех ячеек, напротив которых написано слово «Продано».
Это очень удобно, особенно если в вашем отчете огромное количество информации.
Численное значение
В этом случае для проверки будем использовать графу «Стоимость». Для расчета выполним следующие действия.
- Добавим еще одну строку, в которой будем выводить «Количество дорогих товаров».
- Перейдите в нужную ячейку. После этого начните вводить эту же функцию.
- В качестве условия выделяем столбец «Стоимость».
- Обязательно дописываем символ «;». Только после этого можно будет перейти к следующему аргументу.
- Затем в кавычках указываем «>20». Это условие означает – считать всё то, что больше 20. Здесь используются обычные математические операторы – всё как в школе. В конце снова ставим символ «;».
- Только теперь можно указать столбец, в котором будут производиться расчёты.
- Ставим закрывающую скобку «)».
- Для проверки результата нажимаем на клавиатуре кнопку Enter .
Благодаря этому мы смогли найти количество всех дорогих товаров. Таким же способом можно посчитать что угодно. Всё зависит от условия.
Сумма с несколькими условиями
Но иногда всё намного сложней. Например, могут быть случаи, когда нужно посчитать в столбце сумму всех одновременно и дорогих, и проданных продуктов. Рассмотрим, как правильно составить формулу для этой ситуации.
- Сначала добавим новую строку.
- Выделяем нужную ячейку и переходим в строку ввода функций.
- На этот раз вводим другую формулу.
Буквы МН означают, что будет использовано много условий.
У этой функции аргументы расположены в ином порядке (в отличие от СУММЕСЛИ).
- Здесь мы сразу выделяем тот столбец, в котором указано количество товаров. В прошлый раз мы это делали в последнюю очередь – здесь всё наоборот.
- Обязательно закрываем аргумент точкой с запятой. Затем смотрим на подсказку, что делать дальше.
- Указываем диапазон первого условия. В нашем случае это графа «Стоимость».
- Для того чтобы не было ошибок, дописываем символ «;». Затем в двойных кавычках пишем условие сортировки. Например, в нашем случае это «>20». Также закрываем точкой с запятой. Если вы часто работаете в Excel или планируете там работать – нужно привыкать все аргументы закрывать этим символом.
- Далее мы видим подсказку, что нужно проделать те же самые действия, но теперь для второго условия. Их может быть огромное количество. Просто каждый раз необходимо выделять нужный диапазон и затем указывать условие. Выделяем столбец «Статус».
- Ставим точку с запятой и пишем в кавычках слово «Продано». В конце нашей формулы обязательно ставим закрывающую скобку.
Для завершения работы с редактором функций нажмите на клавишу Enter . Сразу после этого вы увидите результат.
В нашем случае мы видим, что дорогих товаров у нас 18, а продано из них всего 10.
Заключение
В данной статье были рассмотрены основные возможности редактора Excel для расчета суммы значений в столбцах:
- через панель инструментов;
- обычное сложение при помощи одной функции;
- с учетом одного условия;
- применение сложных формул.
Если у вас что-то не получилось, возможно вы делаете что-то не так. Большой процент ошибок многие допускают именно из-за синтаксиса, то есть забывают дописывать кавычки или скобки.
Видеоинструкция
Если у вас что-то не получилось или остались какие-нибудь вопросы, ниже прилагается видеоролик, в котором можно увидеть дополнительные замечания к описанным выше инструкциям.