Как написать формулу в Excel? Обучение. Самые нужные формулы

excelДобрый день.

Когда-то, написать самостоятельно формулу в Excel - для меня было чем-то невероятным. И даже, несмотря на то, что часто приходилось работать в этой программе, ничего кроме текста не набивал...

Как оказалось, большинство формул не представляют из себя ничего сложного и с ними легко можно работать, даже, начинающему пользователю компьютера. В статье, как раз, хотелось бы раскрыть самые нужные формулы, с которыми чаще всего и приходится работать...

И так, начнем...

1. Основные операции и азы. Обучение основам Excel.

Все действия в статье будут показываться в Excel версии 2007г.

После запуска программы Excel - появляется окно со множеством клеточек - наша таблица. Главная особенность программы в том, что она может считать (как калькулятор) ваши формулы, которые вы напишете. Кстати, добавить формулу можно в каждую ячейку!

Формула должна начинаться со знака "=". Это обязательное условие. Дальше вы пишите то, что вам нужно посчитать: например, "=2+3" (без кавычек) и нажимаете по клавише Enter - в результате вы увидите, что в ячейке появился результат "5". См. скриншот ниже.

2014-03-29 08_19_23-Microsoft Excel - Книга1

Важно! Несмотря на то, что в ячейке А1 написано число "5" - оно считается по формуле ("=2+3"). Если в соседней ячейке просто текстом написать "5" - то при наведении курсора на эту ячейку - в редакторе формулы (строка сверху, Fx) - вы увидите простое число "5".

А теперь представьте, что в ячейку вы можете писать не просто значение 2+3, а номера ячеек, значения которых нужно сложить. Допустим так "=B2+C2".

2014-03-29 08_26_56-Microsoft Excel - Книга1

Естественно, что в B2 и C2 должны быть какие-нибудь числа, иначе Excel покажет нам в ячейке A1 результат равный 0.

И еще одно важное замечание...

Когда вы копируете ячейку, в которой есть формула, например A1 - и вставляете ее в другую ячейку - то копируется не значение "5", а сама формула!

Причем, формула изменится прямо-пропорционально: т.е. если A1 скопировать в A2 - то формула в ячейке A2 будет равна "=B3+C3". Excel сам меняет автоматически вашу формулу: если A1=B2+C2, то логично, что A2=B3+C3 (все цифры увеличились на 1).

2014-03-29 08_35_39-Microsoft Excel - Книга1

Результат, кстати, в A2=0, т.к. ячейки B3 и С3 не заданы, а значит равны 0.

Таким образом можно написать формулу один раз, а затем ее скопировать во все ячейки нужного столбца - и Excel сам произведет расчет в каждой строчки вашей таблицы!

Если вы не хотите, чтобы B2 и С2 изменялись при копировании и всегда были привязаны к этим ячейкам, то просто добавьте к ним значок "$". Пример ниже.

excel

Таким образом, куда бы вы ни скопировали ячейку A1 - она всегда будет ссылаться на привязанные ячейки.

 

2. Сложение значений в строках (формула СУММ и СУММЕСЛИМН)

Можно, конечно, каждую ячейку складывать, делая формулу A1+A2+A3 и т.п. Но чтобы так не мучатся, есть в Excel специальная формула, которая сложит все значения в ячейках, которые вы выделите!

Возьмем простой пример. Есть на складе несколько наименований товара, причем мы знаем, сколько каждого товара по отдельности в кг. есть на складе. Попробуем посчитать, а сколько всего в кг. груза на складе.

Для этого переходим в ячейку, в которой будет показываться результат и пишем формулу: "=СУММ(C2:C5)". См. скриншот ниже.

2014-03-29 08_48_17-Microsoft Excel - Книга1

В результате все ячейки в выделенном диапазоне будут просуммированы, а вы увидите результат.

2014-03-29 08_48_31-Microsoft Excel - Книга1

 

2.1. Сложение с условием (с условиями)

А теперь представим, что у нас есть определенные условия, т.е. сложить надо не все значения в ячейках (Кг, на складе), а лишь определенные, скажем, с ценой (1 кг.) меньше 100.

Для этого есть замечательная формула "СУММЕСЛИМН". Сразу же пример, а затем пояснение каждого символа в формуле.

2014-03-29 09_15_41-Microsoft Excel - Книга1

=СУММЕСЛИМН(C2:C5;B2:B5;"<100"), где:

C2:C5 - та колонка (те ячейки), которые будут суммироваться;

B2:B5 - колонка, по которой будет проверяться условие (т.е. цена, например, менее 100);

"<100" - само условие, обратите внимание, что условие пишется в кавычках.

 

Ничего сложного в этой формуле нет, главное соблюдать соразмерность: C2:C5;B2:B5 - правильно; C2:C6;B2:B5 - неправильно. Т.е. диапазон суммирования и диапазон условий должны быть соразмерны, иначе формула вернет ошибку.

Важно! Условий для суммы может быть много, т.е. можно проверять не по 1-й колонке, а сразу по 10, задав множество условий.

 

3. Подсчет количества строк, удовлетворяющих условиям (формула СЧЁТЕСЛИМН)

Довольно часто-встречающаяся задача: подсчитать  не сумму значений в ячейках, а количество таких ячеек, удовлетворяющих определенным условиям. Иногда, условий очень много.

И так... начнем.

В этой же примере попробуем посчитать количество наименования товара с ценой больше 90 (если окинуть взглядом, то и так можно сказать, что таких товаров 2: мандарины и апельсины).

2014-03-29 09_27_36-Microsoft Excel - Книга1

Для подсчета товаров в нужной ячейке написали следующую формулу (см. выше):

=СЧЁТЕСЛИМН(B2:B5;">90"), где:

B2:B5 - диапазон, по которому будут проверять, по заданному нами условию;

">90" - само условие, заключается в кавычки.

 

Теперь попробуем немного усложнить наш пример, и добавим счет еще по одному условию: с ценой больше 90 + количество на складе менее 20 кг.

2014-03-29 09_27_15-Microsoft Excel - Книга1

Формула приобретает вид:

=СЧЁТЕСЛИМН(B2:B6;">90";C2:C6;"<20")

Здесь все осталось таким же, кроме еще одного условия (C2:C6;"<20"). Кстати, таких условий может быть очень много!

Понятно, что для такой маленькой таблицы никто не будет писать такие формулы, а вот для таблицы из нескольких сотен строк - это уже совсем другое дело. Для примера же эта таблица - более чем наглядна.

 

4. Поиск и подстановка значений из одной таблицы в другую (формула ВПР)

Представим, что к нам пришла новая таблица, с новыми ценниками для товара. Хорошо, если наименований 10-20 - можно и в ручную их все "перезабить". А если таких наименований сотни? Гораздо быстрее, если бы Excel самостоятельно нашел в совпадающие наименования из одной таблицы в другой, а затем скопировал новые ценники в старую нашу таблицу.

Для такой задачи используется формула ВПР. В свое время сам "мудрил" с логическими формулами "ЕСЛИ" пока не встретил эту замечательную штуку!

И так, начнем...

Вот наш пример + новая таблица с ценниками. Сейчас нам нужно автоматически подставить новые ценники из новой таблицы в старую (новые ценники красные).

2014-03-29 10_01_05-Microsoft Excel - Книга1

Ставим курсор в ячейку B2 - т.е. в первую ячейку, где нам нужно изменить ценник автоматически. Далее пишем формулу, как на скриншоте ниже (после скриншота будет подробное пояснение к ней).

2014-03-29 10_00_33-Microsoft Excel - Книга1

=ВПР(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.

Надеюсь что кому-то пригодятся разобранные примеры и помогут ускорить его работу. Удачных экспериментов!

PS

А какие формулы используете вы, можно ли как-то упростить формулы приведенные в статье? Например, на слабых компьютерах, при изменении каких-то значений в больших таблицах, где производятся автоматически расчеты - компьютер зависает на пару секунд, пересчитывая и показывая новые результаты...

 

 

Опубликовано 29 Мар 2014 в рубрике «Excel».


Социальные кнопки:

ExcelSkype
2015-02-16 18:17:56
Функция ВПР() безусловно очень полезная, но, чтобы не вводить Ваших читателей в заблуждение, а главное, чтобы у них не возникло ошибок при работе с этой функцией, желательно также рассказать про параметр "интервальный_просмотр" В приведенном Вами примере это параметр неважен только по той причине, что искомые значения отсортированы по алфавиту, однако, если они не будут отсортированы, то применять формулу в таком виде, как Вы советуете чревато плохими последствиями (она будет возвращать некорректный результат)
Александр
2016-05-16 11:49:40
Здравствуйте! Подскажите пожалуйста, как в Excel представить формулу: Скорректированная стоимость = = Стоимость * (К1 + К2 + … + КN – (N - 1); где: К1, К2, КN - коэффициенты, отличные от 1 N – количество коэффициентов, отличных от 1.
Ирина Дитковская
2017-06-06 10:30:48
подскажите как составить формулу( как по наполнению сосудов предположим "общая" 100000 "аванс" 55000 "доплата 1" 0 "доплата 2" 0 "50%" "20%" "8%" "5%" "2%" "2%" "2%" "1%" "3%" "7%" пришла сумма аванса распределить в 50% и остаток в следующую, и т.д. при доплате наполнится 20% чтоб остаток переходил в в следующий процент и т.д. (возможно, что аванс составит менее 50%)
Сергей
2017-06-13 09:03:48
Подскажите пожалуйста, как прописать формулу. Есть значения 3,12,24 и 100, если выпадает какая-то цифра из этих четырех, то считается определенная математическая формула