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

Когда вы копируете ячейку, в которой есть формула, например 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. Подсчет количества строк, удовлетворяющих условиям (формула СЧЁТЕСЛИМН)

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

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

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

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

=СЧЁТЕСЛИМН(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.

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

 

 

PS

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

 

16/02/2015 в 18:17
ExcelSkype

Функция ВПР() безусловно очень полезная, но, чтобы не вводить Ваших читателей в заблуждение, а главное, чтобы у них не возникло ошибок при работе с этой функцией, желательно также рассказать про параметр «интервальный_просмотр»
В приведенном Вами примере это параметр неважен только по той причине, что искомые значения отсортированы по алфавиту, однако, если они не будут отсортированы, то применять формулу в таком виде, как Вы советуете чревато плохими последствиями (она будет возвращать некорректный результат)