Функции в Excel – это вычислительные инструменты пригодны для самых разных отраслей деятельности: финансы, статистика, банковское дело, бухгалтерский учет, инженерство и проектирование, анализ исследовательских данных и т.д.
Лишь мизерная часть возможностей вычислительных функций включено в данный учебник с уроками по Excel. На простых примерах и далее будем рассматривать практическое применение функций.
Функция вычисления среднего числа
Как найти среднее арифметическое число в Excel? Создайте таблицу, так как показано на рисунке:
В ячейках D5 и E5 введем функции, которые помогут получить среднее значение оценок успеваемости по урокам Английского и Математики. Ячейка E4 не имеет значения, поэтому результат будет вычислен из 2 оценок.
Функция среднее значение в Excel: =СРЗНАЧ() в ячейке E5 игнорирует текст. Так же она проигнорирует пустую ячейку. Но если в ячейке будет значение 0, то результат естественно измениться.
В Excel еще существует функция =СРЗНАЧА() – среднее значение арифметическое число. Она отличается от предыдущей тем, что:
- =СРЗНАЧ() – пропускает ячейки которые не содержат чисел;
- =СРЗНАЧА() – пропускает только пустые ячейки, а текстовые значения воспринимает как 0.
Функция подсчета количества значений в Excel
- В ячейки D6 и E6 нашей таблицы введите функцию подсчета количества числовых значений. Функция позволит нам узнать количество поставленных оценок.
- Перейдите в ячейку D6 и выберите инструмент из выпадающего списка: «Главная»-«Сумма»-«Число».
- На этот рас нам не подходит автоматическое определение диапазона ячеек, поэтому его нужно исправить на D2:D4. После чего жмем Enter.
- Из D6 в ячейку E6 скопируйте функцию =СЧЕТ() – это функция Excel для подсчета количества не пустых ячеек.
На данном примере хорошо видно, что функция =СЧЕТ() игнорирует ячейки, которые не содержат числа или пусты.
Функция ЕСЛИ в Excel
В ячейки D7 и E7 введем логическую функцию, которая позволит нам проверить, все ли студенты имеют оценки. Пример использования функции ЕСЛИ:
Описание аргументов функции: =ЕСЛИ(). В ячейке A4 у нас количество всех студентов, а в ячейке D6 и E6 – количество оценок. Функция ЕСЛИ() проверяет, совпадают ли показатели D6 и E6 со значением A4. Если совпадают, то получаем ответ ДА, а если нет – ответ НЕТ.
В логическом выражении для удобства применяется 2 типа ссылок: относительная и абсолютная. Это позволяет нам скопировать формулу без ошибок в результатах ее вычисления.
Примечание. Закладка «Формулы» предоставляет доступ только к наиболее часто используемым функциям. Больше можно получить, вызвав окно «Мастер функций» нажав на кнопку «Вставить функцию» вначале строки формул. Или нажать комбинацию клавиш SHIFT+F3.
Как округлять числа в Excel
Функция =ОКРУГЛ() более точна и полезнее чем округление с помощью формата ячеек. В этом легко убедиться на практике.
Теперь вы знаете, как пользоваться функцией ОКРУГЛ в Excel.
Описание аргументов функции =ОКРУГЛ():
- Первый аргумент – это ссылка на ячейку значение, которой нужно округлить.
- Второй аргумент – это количество знаков после запятой, которое нужно оставить после округления.
Внимание! Форматирование ячеек только отображает округление но не изменяет значение, а =ОКРУГЛ() – округляет значение. Поэтому для вычислений и расчетов нужно использовать функцию =ОКРУГЛ(), так как форматирование ячеек приведет к ошибочным значениям в результатах.
В данной категории собраны несколько специфические функции для работы с упорядоченными данными. Не зря категория включает в себя упоминание про базы данных: таблицы для работы этих функций должны отвечать определенным требованиям:
- таблица должна обязательно содержать заголовки столбцов. Эти заголовки должны располагаться строго в одной строке, не должны содержать объединенных и пустых ячеек.
- таблица должна быть неделимая, т.е. не должна содержать полностью пустых строк и столбцов, а так же объединенных ячеек
- в каждом столбце должна содержаться однотипная информация: если в столбце должны содержаться даты, значит кроме дат там не должно быть ничего другого; если в столбце числа(суммы, кол-во) - значит должны быть только числа. Не следует при отсутствии чисел оставлять ячейку пустой или ставить пробел. Вместо этого необходимо ставить 0.
Для разбора функции баз данных возьмем следующий пример таблицы:
Данная таблица отвечает всем требованиям для работы с функциями баз данных, однако, чтобы более удобно и гибко работать с функциями баз данных лучше сместить таблицу данных на несколько строк вниз, а выше добавить таблицу критериев, где будут формироваться условия отбора данных из основной таблицы:
Именно для этой таблицы будут приведены все примеры описаний функций. И критерии заданы следующие: отбирать из поля "Дерево" Яблони с высотой больше 3 и меньше 6 и Вишни, со значением в поле "Возраст" больше 8. Если посмотреть на таблицу данных(из которой будут отбираться данные и производится расчеты функциями), то этим критериям отвечают только две строки: строки 9 и 10 листа.
Как видно, в качестве критериев можно указывать выражение в виде: >6 , 0 (не равно нулю), >=7 , "Яблоня" .
Все функции из категории баз данных имеют три одинаковых аргумента:
Функция(база_данных; поле; критерий)
база_данных
- ссылка на ячейки данных таблицы, включая заголовок(A6:E12).
поле
- в данном аргументе можно записать как непосредственно текст с названием столбца в кавычках ("Дерево", "Возраст" или "Урожай"), так и число, задающее положение столбца в таблице: 1 - для первого поля(столбца) в таблице "Дерево", 2 - для второго поля "Высота", 3 - для третьего поля "Возраст" и так далее.
критерий
- ссылка на диапазон ячеек с условиями отбора(A1:F3). Функция отберет данные из таблицы, которые удовлетворяют условиям, указанным в ячейках критериев. В ссылке на критерии обязательно должны быть включены названия столбцов, для которых выполняется отбор данных.
- ДСРЗНАЧ (DAVERAGE)
- Вычисляет среднее значение выбранных записей базы данных:
=ДСРЗНАЧ(A6:E12 ;5; A1:F3)
=ДСРЗНАЧ(A6:E12 ;"Прибыль"; A1:F3)
=DAVERAGE(A6:E12 ,5, A1:F3) вернет значение 90 000р. , т.к. сумма прибыли отобранных записей равна 180 000р., а всего отобрано 2 записи. 180 000/2 = 90 000 . - БСЧЁТ (DCOUNT)
- Подсчитывает количество числовых ячеек в базе данных:
=БСЧЁТ(A6:E12 ;5; A1:F3)
=БСЧЁТ(A6:E12 ;"Прибыль"; A1:F3)
=DCOUNT(A6:E12 ,5, A1:F3) вернет число 2 , т.к. только две строки в таблице отвечают критериям - БСЧЁТА (DCOUNTA)
- Подсчитывает количество непустых ячеек в базе данных:
=БСЧЁТА(A6:E12 ;4; A1:F3)
=БСЧЁТА(A6:E12 ;"Прибыль"; A1:F3)
=DCOUNTA(A6:E12 ,4, A1:F3) вернет 2, т.е. подсчитает в отвечающих критериям строках количество непустых ячеек в столбце "Прибыль" - БИЗВЛЕЧЬ (DGET)
- Извлекает из базы данных одну запись, удовлетворяющую заданному условию:
=БИЗВЛЕЧЬ(A6:E12 ;5; A1:F3)
=БИЗВЛЕЧЬ(A6:E12 ;"Прибыль"; A1:F3)
=DGET(A6:E12 ,5, A1:F3) для заданных условий вернет значение ошибки #ЧИСЛО! (#NUM!) , т.к. этим условиям отвечает более одной записи. Если же указать диапазон для критерия как:
=БИЗВЛЕЧЬ(A6:E12 ;5; A1:F2) то функция вернет значение 75 000р. , т.е. единственную запись о прибыли для Яблонь с высотой больше 3 и меньше 6 (в данный промежуток попадает лишь строка 10 - Яблона, высота 5) - ДМАКС (DMAX)
- Находит максимальное значение среди выделенных записей базы данных:
=ДМАКС(A6:E12 ;5; A1:F3)
=ДМАКС(A6:E12 ;"Прибыль"; A1:F3)
=DMAX(A6:E12 ,5, A1:F3) вернет сумму 105 000р. , т.к. это максимальная прибыль из всех отвечающих критериям строк. - ДМИН (DMIN)
- Находит минимальное значение среди выделенных записей базы данных:
=ДМИН(A6:E12 ;5; A1:F3)
=ДМИН(A6:E12 ;"Прибыль"; A1:F3)
=DMIN(A6:E12 ,5, A1:F3) вернет сумму 75 000р. , т.к. это минимальная прибыль из всех строк, отвечающих критериям - БДПРОИЗВЕД (DPRODUCT)
- Перемножает значения определенного поля в записях базы данных, удовлетворяющих условию:
=БДПРОИЗВЕД(A6:E12 ;3; A1:F3)
=БДПРОИЗВЕД(A6:E12 ;"Возраст"; A1:F3)
=DPRODUCT(A6:E12 ,3, A1:F3) вернет 210 , т.к. будут перемножены все значения столбца "Возраст", отвечающие критериям(14*15=210) - ДСТАНДОТКЛ (DSTDEV)
- Оценивает стандартное отклонение по выборке из выделенных записей базы данных:
=ДСТАНДОТКЛ(A6:E12 ;4; A1:F3)
=ДСТАНДОТКЛ(A6:E12 ;"Урожайность"; A1:F3)
=DSTDEV(A6:E12 ,4, A1:F3) вернет 0,707107 , т.е. оценку стандартного отклонения урожайности по указанным критериям. - ДСТАНДОТКЛП (DSTDEVP)
- Вычисляет стандартное отклонение по генеральной совокупности из выделенных записей базы данных:
=ДСТАНДОТКЛП(A6:E12 ;4; A1:F3)
=ДСТАНДОТКЛП(A6:E12 ;"Урожайность"; A1:F3)
=DSTDEVP(A6:E12 ,4, A1:F3) вернет 0,5 , т.е. точное стандартное отклонение урожайности по указанным критериям, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду. - БДСУММ (DSUM)
- Суммирует числа в поле для записей базы данных, удовлетворяющих условию:
=БДСУММ(A6:E12 ;5; A1:F3)
=БДСУММ(A6:E12 ;"Прибыль"; A1:F3)
=DSUM(A6:E12 ,5, A1:F3) вернет сумму прибыли всех строк, отвечающих критериям, т.е. 180 000р.
=БДСУММ(A6:E12 ;5; A1:A2)
=DSUM(A6:E12 ,5, A1:A2) вернет сумму прибыли от всех Яблонь, т.е. 225 000р. - БДДИСП (DVAR)
- Оценивает дисперсию по выборке из выделенных записей базы данных:
=БДДИСП(A6:E12 ;4; A1:A2)
=БДДИСП(A6:E12 ;"Урожайность"; A1:A2)
=DVAR(A6:E12 ,4, A1:A2) вернет 0,5 , что будет оценкой дисперсии урожайности по указанным критериям, если считать, что данные в таблице являются выборкой из генеральной совокупности всех деревьев в саду - БДДИСПП (DVARP)
- Вычисляет дисперсию по генеральной совокупности из выделенных записей базы данных:
=БДДИСПП(A6:E12 ;4; A1:A2)
=БДДИСПП(A6:E12 ;"Урожайность"; A1:A2)
=DVARP(A6:E12 ,4, A1:A2) вернет 10,66667 , т.е. точную дисперсию урожайности Яблонь и Вишень, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду
Одна из самых популярных функций в приложении Excel – ЕСЛИ . Это логическое сравнение значения и получаемого результата. Если говорить проще, то функция может звучать так:
ЕСЛИ условие истинно, то выполняем это, а иначе делаем что-либо еще
Синтаксис этой функции достаточно прост:
ЕСЛИ (логическое_условие; значение_в_случае_ИСТИНА; значение в случае_ЛОЖЬ)
Разберем подробнее:
- Логическое_условие – значение, которое будем проверять
- Значение_в_случае_ИСТИНА – действие, когда требование выполняется
- Значение в случае_ЛОЖЬ – действие, когда Логическое_условие не выполняется
Как использовать функцию
Пример №1
На скриншотах ниже показан самый просто вариант использования. Мы сначала проверяем
заданное условие А1>25
. ЕСЛИ это требование выполняется
, тогда выводим в ячейку «больше 25
», иначе «меньше или равно 25
».
Пример №2
Теперь более сложное задание. Ниже мы рассмотрим пример переаттестации сотрудников предприятия. Изначально таблица выглядит так:
Нам необходимо в столбце С вывести результаты переаттестации, которые могут принимать бинарное значение: СДАЛ или НЕ СДАЛ . Критерии у нас будут такими: кто набрал более 45 балов , тот считается сдавшим экзамен, ну а все остальные нет.
Для выполнения задачи необходимо :
Используем несколько условий
При необходимости, функцию ЕСЛИ можно вложить друг в друга. Таким образом, мы расширим варианты решений.
Для примера, возьмем переаттестацию сотрудников, которую рассмотрели раньше. Изменим критерии результата и выставим каждому оценку : Плохо, Хорошо и Отлично. Отлично будем ставить, когда баллы превысят 60 . Оценку Хорошо можно будет получить, набрав от 45 до 60 балов . Ну и в остальных случаях ставим Плохо .
Как видно из примера, вместо второго и третьего значения функции можно подставлять условие. Таким способом добавляем необходимое число вложений. Однако стоит отметить , что после добавления 3-5 вложений работать с формулой станет практически невозможно, т.к. она будет очень громоздкой.
Другие варианты использования функции
Находим сумму столбцов или ячеек
К примеру, есть необходимость просуммировать каждую вторую ячейку столбца. В этом случае поможет использование оператора СУММЕСЛИ
. Он позволит работать только с теми столбцами, которые попадают под наши требования. Мы будет рассматривать случай, когда необходимо суммировать элементы в нечетных строках.
Для решения задачи, нам потребуется вставить дополнительный столбец и пронумеровать строки 1 и 0. Именно эти данные мы будем использовать в нашей формуле. В примере видно, какую формулу мы использовали.
Синтаксис : СУММЕСЛИ(диапазон_ячеек; используемое_условие; [диапазон_суммирования])
- Диапазон – обязательный аргумент. Тут задается диапазон, который оценивается на соответствие условию.
- Используемое_условие – тут указываем, какие именно ячейки необходимо суммировать.
- Диапазон_суммирования – указываем, какой именно диапазон использовать для суммирования.
Количество повторений элементов на листе
В этом случае мы можем посчитать, сколько раз заданный элемент встречается на листе. Для этого используем функцию СЧЕТЕСЛИ
. Она считает количество ячеек, которые совпадают
с заданным значением.
В этом примере мы считаем, сколько клиентов находится в каждом городе. В формуле мы задаем первым диапазон, а вторым значения, которые следует искать.
Используем СЧЕТЕСЛИ и СУММЕСЛИ при подсчете данных
В примере мы постараемся определить усредненный объем дохода
от клиентов по каждому городу. Для этого поделим суммарный доход города на количество клиентов.
Поиск нескольких вхождений элемента в списке
Если у нас есть постоянно обновляемый список данных, в котором по мере роста могут появляться дубликаты , то поиск вхождений в этом списке может оказаться затруднительным. Для решения этой задачи проще всего воспользоваться функцией ВПР и СЧЕТЕСЛИ .
Для начала, добавим
дополнительный столбец, который добавить порядковый номер вхождения к имени клиента. В результате, первое упоминание элемента будет выдавать Имя1, следующее Имя2 и т.д.
Используем поиск по клиенту Кристина Агилера. Используя формулу ВПР («Кристина Агилера3», диапазон_поиска, 2, ЛОЖЬ), мы получим третий номер этого клиента. В формуле, последним значением ставим ЛОЖЬ, поскольку список не сортирован, и нам необходимо точное совпадение элементов.
Как сократить количество вложений ЕСЛИ
Версии до EXCEL 2003 включительно поддерживали до 7 уровней вложенности ЕСЛИ. Начиная с версии Excel 2007, это ограничение убрали . Но стоит заметить, что ниже 3-4 уровня вложенности мало кто опускается.
Для того чтобы уменьшить количество использования вложений ЕСЛИ, можно использовать функцию ВЫБОР . Она работает со значениями или действиями из заданного списка по номеру индекса.
Синтаксис : ВЫБОР (номер_индекса; значение_индекса1; значение_индекса2; [значение_индекса3];…)
Для примера, функция ВЫБОР (1; «Третий»; «Второй»; «Первый») , вернет нам слово «Третий », если мы ее добавим в ячейку.
Стоит отметить, что имеются некоторые ограничения. В частности, в качестве индекса могут выступать только числа
.
Сегодня мы рассмотрим функцию ЕСЛИ .
Функция ЕСЛИ часто используется в Excel для решения многих задач. Знать ее очень полезно. В данной статье мы попробуем рассказать про ее работу на простых примерах, достаточно один раз разобраться с конструкцией функцией ЕСЛИ и вы сможете применять ее и в самых сложных вариантах.
Функция ЕСЛИ проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет.
Синтаксис функции ЕСЛИ очень простой:
ЕСЛИ(лог_выражение ; [значение_если_истина ]; [значение_если_ложь ])
лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.
Что это значит? Выражение при вычислении дает значение ИСТИНА если это выражение верно.
В этой части необходимо проверить на соответствие выражения.
Например:
ЕСЛИ(А1=10 ; [значение_если_истина]; [значение_если_ложь]) - если А1 равно 10, то выражение А1=10 даст значение ИСТИНА, а если не равно 10, то ЛОЖЬ
Другой пример
ЕСЛИ(А1>30 ; [значение_если_истина]; [значение_если_ложь]) - если в ячейки А1 число больше 30, то А1>30 вернет ИСТИНА, а если меньше, то ЛОЖЬ
Еще пример
ЕСЛИ(С1=”Да” ; [значение_если_истина]; [значение_если_ложь]) - если в ячейки C1 содержится слово “Да” то выражение вернет значение ИСТИНА, а если нет, то С1=”Да” вернет ЛОЖЬ
ЕСЛИ(лог_выражение ; [значение_если_истина ]; [значение_если_ложь ])
значение_если_истина , значение_если_ложь – как видно из их названия, это то что необходимо сделать в зависимости от того, что вернул лог выражения : ИСТИНА и ЛОЖЬ
Пример использования функции ЕСЛИ в Excel
Рассмотрим использование функции ЕСЛИ на практическом примере. У нас есть таблица заказов, которую мы использовали при рассмотрении работы . Нам необходимо заполнить столбец по заказам Ведер (ошибочно на картинке указано «Заказы Cтолов»), то есть необходимо выбрать только заказы с Ведрами. Это можно сделать различными способами, но мы с вами будет использовать функцию ЕСЛИ, чтобы показать ее работу на примере. (см.рисунок)
Для решения поставленной задачи напишем формулу с использованием функции ЕСЛИ
ЕСЛИ(A3="Ведро";D3;"-")
Как вы смогли заметить аргументы функции ЕСЛИ разделены точкой с запятой.
Итак, первый аргумент (лог выражения ) A3="Ведро" проверяет содержится ли в ячейке А3 слово «Ведро», если содержится, то выполняется второй аргумент функции ЕСЛИ (значение_если_истина ), в нашем случае это D3 (т.е стоимость заказа), если в ячейка А3 не равна слову «Ведро», то выполняется третий аргумент функции ЕСЛИ (значение_если_ложь ), в нашем случае это «-» (т.е будет написано тире).
Таким образом, в ячейки E3 появится значение D3, т.е число 240.
В этом уроке мы познакомимся с функцией ПРОСМОТР , которая позволяет извлекать нужную информацию из электронных таблиц Excel. На самом деле, Excel располагает несколькими функциями по поиску информации в книге, и каждая из них имеет свои преимущества и недостатки. Далее Вы узнаете в каких случаях следует применять именно функцию ПРОСМОТР , рассмотрите несколько примеров, а также познакомитесь с ее вариантами записи.
Варианты записи функции ПРОСМОТР
Начнем с того, что функция ПРОСМОТР имеет две формы записи: векторная и массив. Вводя функцию на рабочий лист, Excel напоминает Вам об этом следующим образом:
Форма массива
Форма массива очень похожа на функции ВПР и ГПР . Основная разница в том, что ГПР ищет значение в первой строке диапазона, ВПР в первом столбце, а функция ПРОСМОТР либо в первом столбце, либо в первой строке, в зависимости от размерности массива. Есть и другие отличия, но они менее существенны.
Данную форму записи мы подробно разбирать не будем, поскольку она давно устарела и оставлена в Excel только для совместимости с ранними версиями программы. Вместо нее рекомендуется использовать функции ВПР или ГПР .
Векторная форма
Функция ПРОСМОТР (в векторной форме) просматривает диапазон, который состоит из одной строки или одного столбца. Находит в нем заданное значение и возвращает результат из соответствующей ячейки второго диапазона, который также состоит из одной строки или столбца.
Вот это да! Это ж надо такое понаписать… Чтобы стало понятней, рассмотрим небольшой пример.
Пример 1
На рисунке ниже представлена таблица, где указаны номера телефонов и фамилии сотрудников. Наша задача по фамилии сотрудника определить его номер телефона.
В данном примере функцию ВПР не применить, поскольку просматриваемый столбец не является крайним левым. Именно в таких случаях можно использовать функцию ПРОСМОТР . Формула будет выглядеть следующим образом:
Первым аргументом функции ПРОСМОТР является ячейка C1, где мы указываем искомое значение, т.е. фамилию. Диапазон B1:B7 является просматриваемым, его еще называют просматриваемый вектор. Из соответствующей ячейки диапазона A1:A7 функция ПРОСМОТР возвращает результат, такой диапазон также называют вектором результатов. Нажав Enter , убеждаемся, что все верно.
Пример 2
Функцию ПРОСМОТР в Excel удобно использовать, когда векторы просмотра и результатов относятся к разным таблицам, располагаются в отдаленных частях листа или же вовсе на разных листах. Самое главное, чтобы оба вектора имели одинаковую размерность.
На рисунке ниже Вы можете увидеть один из таких примеров:
Как видите, диапазоны смещены друг относительно друга, как по вертикали, так и по горизонтали, но формула все равно вернет правильный результат. Главное, чтобы размерность векторов совпадала. Нажав Enter , мы получим требуемый результат:
При использовании функции ПРОСМОТР в Excel значения в просматриваемом векторе должны быть отсортированы в порядке возрастания, иначе она может вернуть неверный результат.
Так вот коротко и на примерах мы познакомились с функцией ПРОСМОТР и научились использовать ее в рабочих книгах Excel. Надеюсь, что данная информация оказалась для Вас полезной, и Вы обязательно найдете ей применение. Всего Вам доброго и успехов в изучении Excel.
