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

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

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

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

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

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

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

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

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

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

Тут все осталось таковым же, не считая еще 1-го условия (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

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