Использование стандартных функций
Функции производят определенные действия с данными и на основе их выдают результат этих действий. Аргументы могут быть представлены в виде ссылки, числа, логического значения, текста. Каждая функция использует группу аргументов определенного типа. Для вставки стандартной функции используют несколько приёмов. Рассмотрим их на конкретных примерах.
Задание №2. Вычислить сумму оклада и премии. Результат разместить в столбец “Начислено”.
Для выполнения задания произведите следующие действия:
Курсор установите на ячейку F3.
Введите с помощью клавиатуры =D3+E3.
Нажмите клавишу <Enter>.
Скопируйте F3 в F4:F11.
Подсказка. Указание используемых в формулах ячеек можно производить с помощью курсора. Для этого после знака равно щелкнуть левой клавишей мыши по первой ячейке, используемой в формуле, (в данном случае D3), поставить знак ( в данном случае плюс) и установите курсор на следующую ячейку, используемую в формуле.( в данном случае ячейку E3) Нажмите клавишу <Enter>... Этот способ удобно использовать в тех случаях, когда вся таблица видна на экране.
Примечание. Копировать формулу можно с помощью функции автозаполнения. Для этого курсор нужно поставить на копируемую ячейку (в данном случае на F3), щелкнуть по ней левой клавишей мыши, установите указатель мыши на правый нижний угол клетки, курсор приобретет вид черного крестика, затем, удерживая черный крестик левой клавишей мыши, тянуть вниз до конца диапазона копирования (в данном случае до ячейки Е11).
Задание №3. Вычислить налог на доходы физических лиц (НДФЛ). Результат разместить в столбец “НДФЛ”.
Для выполнения задания выполните следующие действия:
Курсор установите на ячейку G3.
Введите с помощью клавиатуры формулу = F3*0,13.
Нажмите клавишу <Enter>.
Скопируйте ячейку G3 в диапазон G4:G11.
Задание №4. Вычислить выплаченные каждому работнику деньги. Результат разместить в столбец “К выплате”.
Для выполнения задания необходимо из данных в столбце “Начислено” вычесть “НДФЛ”. Для этого выполните следующие действия:
Курсор установите на ячейку H3.
Введите с помощью клавиатуры формулу =F3-G3.
Нажмите клавишу <Enter>.
Скопируйте ячейку H3 в диапазон H4:H11.
Рассмотрим некоторые наиболее часто используемые стандартные функции.
Статистические функции.
|
Функция |
Действие |
|
СЧЁТЕСЛИ |
Подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию.
|
|
МАКС |
Определяет максимальное значение из указанных чисел |
|
МИН |
Определяет минимальное значение из указанных чисел |
|
СРЗНАЧ
|
Вычисляет среднее значение из указанных чисел |
|
СЧЕТ |
Подсчитывает количество ячеек, имеющих числовые значения |
|
РАНГ |
Определяет ранг числа в ряду других чисел
|
|
СЧЕТЗ |
Подсчитывает количество ячеек, имеющих непустые значения |
|
СЧИТАТЬПУСТОТЫ |
Вычисляет количество пустых ячеек
|
Функция СЧЁТЕСЛИ
Функция СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют указанному пользователем критерию.
Синтаксис: СЧЕТЕСЛИ (диапазон, критерий)
Диапазон - это область ячеек, в которых содержится критерий.
Критерий – это число, текст, число, выражение, ссылка на ячейку.
При задании критерия можно использовать операторы: = (равно), > (больше), < (меньше), =>(больше или равно), <= (меньше или равно), <> (не равно).
Задание №5. Подсчитать количество менеджеров. Результат разместить в ячекеE16.
Первый способ:
Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку E16.
В строке формул ввести =СЧЁТЕСЛИ(C3:C11;"менеджер")
Нажмите клавишу <Enter>
Второй способ:
Для выполнения задания выполните следующие действия:
Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку E16.
Нажмите пиктограмму
(Вставить
функцию)В поле «Категория» выберете группу«Статистические».
В поле «Выберите функцию» выберете «СЧЁТЕСЛИ»
Нажмите кнопку «ОК». Появится окно «Аргументы функции»
В поле «Диапазон» введите С3:С11.
В поле «Критерий» введите с клавиатуры менеджер.
Нажмите «ОК».
Результат выполнения: 5
Примечание: если вы работаете в ранних версиях MS Excel, то критерий менеджер необходимо заключить в кавычки.
Задание №6. Подсчитать количество сотрудников, не получивших премии. Результат разместить в ячеке G17. (Для самостоятельного выполнения).
Результат выполнения: 3
Функция МАКС.
Функция МАКС определяет максимальное значение из указанных чисел.
Рассмотрим её применение на следующем примере.
Задание №7. Определить размер максимального оклада. Результат разместить в ячейке F18.
Первый способ:
Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку F16.
В строке формул ввести =МАКС(D3:D11)
Нажмите клавишу <Enter>
Второй способ:
Произведите следующие действия:
Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку F18.
Нажмите пиктограмму
(Вставить функцию)В поле «Категория» выберете группу «Статистические».
В поле «Выберите функцию» выберете «МАКС»
Нажмите кнопку «ОК».
В поле «Число1» введите D3:D11
Нажмите кнопку «ОК».
Результат выполнения: 40000
Функция МИН.
Функция МИН определяет минимальное значение из указанных чисел. Её применение полностью аналогично функции МАКС и иллюстрируется следующим заданием для самостоятельного выполнения.
Задание №8. Определить размер минимального оклада. Результат разместить в ячейке F19.
Результат выполнения: 13000
Функция СРЗНАЧ.
Функция СРЗНАЧ вычисляет среднее значение из диапазона указанных чисел. Её использование похоже на применение функций МАКС и МИН и иллюстрируется следующим заданием для самостоятельного выполнения.
Задание №9. Определить размер среднего оклада. Результат разместить в ячейке F20.
Результат выполнения: 21555,56 руб.
Функция СЧЁТ.
Функцию СЧЁТ удобно использовать в тех случаях, когда необходимо подсчитать количество ячеек в диапазоне, который содержат числа.
Задание № 10. Подсчитать количество сотрудников работающих в отделах, обозначенных числами.Результат разместить в ячейке С21.
Первый способ:
Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку C21.
В строке формул ввести =СЧЁТ(A3:A11)
Нажмите клавишу <Enter>
Второй способ:
Произведите следующие действия:
Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку C21.
Нажмите пиктограмму
(Вставить функцию)В поле «Категория» выберете группу «Статистические».
В поле «Выберите функцию» выберете «СЧЁТ»
Нажмите кнопку «ОК».
В поле «Значение1» введите A3:A11
Нажмите кнопку «ОК».
Результат выполнения: 5
ФУНКЦИЯ СЧЁТЗ.
Функцию СЧЁТЗ удобно использовать в тех случаях, когда необходимо подсчитать количество заполненных ячеек в диапазоне.
Задание № 11 .Подсчитать количество сотрудников работающих в любых отделах. Результат разместить в ячейке С22.
Первый способ:
Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку C22.
В строке формул ввести =СЧЁТ(A3:A11)
Нажмите клавишу <Enter>
Второй способ:
Произведите следующие действия:
Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку C22.
Нажмите пиктограмму
(Вставить функцию)В поле «Категория» выберете группу «Статистические».
В поле «Выберите функцию» выберете «СЧЁТЗ»
Нажмите кнопку «ОК».
В поле «Значение1» введите A3:A11
Нажмите кнопку «ОК».
Результат выполнения: 6
Примечание. На первый взгляд, при использовании разных функций «СЧЁТ» и «СЧЁТЗ» получен один и тот же результат. На самом деле, назначение этих функций различно. Для того, чтобы убедиться в этом, введите в ячейку A6 слово Бухгалтерия. Результат выполнения: в ячейке C21 содержится число5, а в ячейке C22 содержится число 6. Таким образом, функция «СЧЁТ» подсчитывает только числовые значения, а «СЧЁТЗ» - любые значения (числовые, текстовые). Функция СЧЁТЗ может быть с успехом использована для обработки анкет. Например, её используют для подсчета лиц, ответивших на вопрос, независимо от содержания самого ответа, то есть в результате определяется количество ответивших на вопрос из общего числа опрашиваемых.
ФУНКЦИЯ СЧИТАТЬПУСТОТЫ.
Функцию СЧИТАТЬПУСТОТЫ используется в тех случаях, когда необходимо подсчитать количество не заполненных ячеек в диапазоне. Убедитесь в этом, выполнив самостоятельно следующее задание.
Задание № 12 . Определить количество незаполненных ячеек в диапазоне A3:A11. Результат разместить в ячейке С23.
Результат выполнения: 3
Функция СРЗНАЧЕСЛИ.
Функция СРЗНАЧЕСЛИ подсчитывает среднее значение в диапазоне, удовлетворяющих заданному условию.
Синтаксис: СРЗНАЧЕСЛИ(диапазон, условие, диапазон_усреднения)
Диапазон — несколько ячеек для вычисления среднего значения, включающих числа, массивы.
Условие — число, выражение, ссылка на ячейку, текст, на основании которого вычисляется среднее значение.
Диапазон_усреднения — фактическое множество ячеек для вычисления среднего.
Проиллюстрируем применение этой функции на следующем примере.
Задание № 13. Вычислить средний оклад менеджеров. Результат разместить в ячейке F24.
Первый способ:
Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку F24.
В строке формул ввести =СРЗНАЧЕСЛИ(C3:C11;"менеджер";D3:D11)
Нажмите клавишу <Enter>
Второй способ:
Необходимо выполнить действия:
Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку F24.
Нажмите пиктограмму
(Вставить функцию)В поле «Категория» выберете группу «Статистические».
В поле «Выберите функцию» выберите «СРЗНАЧЕСЛИ»
Нажмите кнопку «ОК».
Появится окно «Аргументы функции»
В поле «Диапазон» введите С3:С11
В поле «Условие» введите менеджер
В поле «Диапазон_усреднения» введите D3:D11
Нажмите кнопку «ОК».
Обратите внимание, что первым указывается диапазон, в котором содержится критерий, т.е. в данном случае это область ячеек с указанием должностей. А диапазон _усреднения состоит из ячеек, в которых содержатся числа, необходимые для вычисления среднего арифметического, данном случае область ячеек, содержащая оклады сотрудников.
Результат выполнения: 20200
Примечание. Если вы работаете в ранних версиях MS Excel, то критерий менеджер необходимо заключить в кавычки.
Выполните следующее задание самостоятельно и проверьте полученный результат.
Задание № 14 . Вычислить средний размер премий слесарей. Результат разместить в ячейке F25.
Результат выполнения: 14000