Основные формулы применяемые при решении задач
Напоминаю, что любые вычисления начинаются в Excel со знака "равно" =, затем следует формула. На самом деле нет необходимости знать ВСЕ формулы в Excel, достаточно овладеть следующими формулами.
СУММ
Вычисляет общую сумму данных в ячейках из диапазона.
=СУММ(диапазон)
диапазон можно выделить, либо прописать вручную (в данном случае диапазон суммирования D2:D11)
СРЗНАЧ
Вычисляет среднее значение данных в ячейках из диапазона.
=СРЗНАЧ(диапазон)
диапазон можно выделить, либо прописать вручную (в данном случае диапазон усреднения B2:B5)
СЧЕТЕСЛИ
Вычисляет количество ячеек, удовлетворяющих условию, из диапазона.
=СЧЕТЕСЛИ(диапазон; условие)
диапазон это столбец (или часть столбца), к которому будет применяться условие
Условие описывается в кавычках и допускает стандартные виды сравнения: = , >, <, <=, >=, <> (например, "<3" означает условие меньше 3; ">=5" - означает больше либо равно 5)
Т.к. столбец кол-во отвечает за количество купленного товара, то именно по нему и будет производиться расчет: если значение в ячейке равно 2, то считаем данную ячейку, после применения формулы получаем количество таких ячеек.
На самом деле все это очень похоже на фильтр, вы бы также применили его к столбцу кол-во, а затем посчитали бы количество значений.
Еще один пример
Т.к. столбец количество отвечает за количество купленного товара, то именно по нему и будет производиться расчет: если значение в ячейке меньше 4 (строго), то считаем данную ячейку, после применения формулы получаем количество таких ячеек.
СЧЁТЕСЛИМН
Функция СЧЁТЕСЛИМН применяет критерии к ячейкам в нескольких диапазонах и вычисляет количество соответствий всем критериям.
СУММЕСЛИ
Вычисляет сумму ячеек, удовлетворяющих условию, из диапазона.
=СУММЕСЛИ(диапазон; условие, диапазон суммирования)
диапазон в данном случае будет обозначать столбец (или часть столбца), к которому будет применяться условие.
условие описывается в кавычках и допускает стандартные виды сравнения: = , >, <, <=, >=, <> (например, "<3" означает условие меньше 3; ">=5" - означает больше либо равно 5)
диапазон суммирования в данном случае будет обозначать столбец (или часть столбца), по которому будет производиться суммирование.
Довольно похоже на применение фильтра, вы бы выбрали столбец кол-во и применили фильтр с условием выбрать только ячейки со значением 2. А затем посчитали бы сумму оставшихся ячеек в столбце общее. Только теперь вычисления идут при помощи формулы.
СУММЕСЛИМН
Функция СУММЕСЛИМН — суммирует все аргументы, удовлетворяющие нескольким условиям. Например, с помощью функции СУММЕСЛИМН можно найти число всех розничных продавцов, (1) проживающих в одном регионе, (2) чей доход превышает установленный уровень.
СРЗНАЧЕСЛИ
Вычисляет сумму ячеек, удовлетворяющих условию, из диапазона.
=СРЗНАЧЕСЛИ(диапазон; условие, диапазон усреднения)
диапазон в данном случае будет обозначать столбец (или часть столбца), к которому будет применяться условие.
условие описывается в кавычках и допускает стандартные виды сравнения: = , >, <, <=, >=, <> (например, "<3" означает условие меньше 3; ">=5" - означает больше либо равно 5)
диапазон усреднения в данном случае будет обозначать столбец (или часть столбца), по которому будет производиться расчет среднего значения.
Довольно похоже на применение фильтра, вы бы выбрали столбец кол-во и применили фильтр с условием выбрать только ячейки со значением 2 и меньше. А затем посчитали бы среднее значение оставшихся ячеек в столбце стоимость. Только теперь вычисления идут при помощи формулы.
МАКС
Вычисляет максимальное значение ячейки из диапазона.
=МАКС(диапазон)
диапазон в данном случае будет обозначать столбец (или часть столбца), к которому будет применяться условие.
МАКСЕСЛИ
Функция МАКСЕСЛИ возвращает максимальное значение из заданных определенными условиями или критериями ячеек
МАКСЕСЛИ(макс_диапазон;диапазон_условия1;условие1;[диапазон_условия2;условие2];…)
МИН
Вычисляет минимальное значение ячейки из диапазона.
=МИН(диапазон)
диапазон в данном случае будет обозначать столбец (или часть столбца), к которому будет применяться условие.
ЕСЛИ - И - ИЛИ
Если условий несколько, либо они сложные (с применением логических операций И, ИЛИ...), то применяется формула ЕСЛИ.
Рассмотрим пример, вычислить количество школьников, набравших по физике больше 60 баллов и по информатике больше 50 баллов.
Решение происходит следующим образом, если в СТРОКЕ данных у ученика баллы по физике > 60 И баллы по информатике > 50, то ставим в столбец справа 1, иначе ставим 0. Таким образом мы получим столбец с 1 и 0, в котором будем видеть подходит строка под условие или нет. Затем остается сложить все значения ячеек в столбце с помощью формулы СУММ и таким образом будет вычислено количество учеников, подходящих под условие.
Ссылки в Excel
Относительная ссылка
По умолчанию, все ссылки в Excel являются относительными. При копировании формул, они изменяются на основании относительного расположения строк и столбцов. Например, если Вы скопируете формулу =A1+B1 из строки 1 в строку 2, формула превратится в =A2+B2.
Относительные ссылки выручают, когда вы копируете формулу во множество ячеек, например при растягивании:
Абсолютная ссылка
В формулах Excel абсолютная ссылка сопровождается добавлением знака доллара ($). Он может предшествовать ссылке на столбец, строку или тому и другому.
Как правило, при создании формул, которые содержат абсолютные ссылки, используется следующий формат: AA2. Два других формата используются значительно реже.
Абсолютная ссылка по сути является константой, она не будет изменяться при копировании.
Допустим, есть налог 20% и надо посчитать стоимость товара с налогом.
В примере показано, что будет без применения абсолютной ссылки и с применением.
Разные полезности
Если нужно выделить диапазон в столбце, а строк очень много, то мышкой это делать затруднительно. В этом случае нужно встать мышкой на нужную ячейку и нажать комбинацию клавиш: Ctrl + Shift + стрелка вниз , чтобы выделить всё до последней используемой ячейки в столбце.
Также, можно использовать Ctrl + Shift + End, если нужно выделить диапазон, захватывая столбцы справа от текущего)
Чтобы вернуться к первой выделенной ячейке можно нажать Ctrl + Backspace. Это вернёт вас к активной ячейке, с которой вы начали выделение, при этом сохраняя выделение.