Использование встроенных функций EXCEL

1. СУММ(число1, число2,…) – суммирует аргументы. В качестве аргументов может быть задан диапазон.

Например, для подсчета суммарного объема производства изделий за первые шесть месяцев года используем функцию суммирования (рис.2.5).

Рис.2.5.Вычисление суммы

Для подсчета суммы в примере можно использовать также функцию Автосуммирование. Для этого необходимо выделить диапазон ВЗ:В8 и на Панели инструментов нажать кнопку автосуммирования S (рис.2.5). В ячейке В9 появится формула СУММ(ВЗ:В8).

Для того чтобы посмотреть какие формулы записаны во всей таблице в ячейках расчета, можно настроить отображение в таблице нечисловых результатов (рис. 2.6). Для этого войдите в меню СЕРВИС, выберите пункт Параметры, в диалоговом окне войдите на вкладку Вид, в группе Параметры окна поставьте флажок Формулы и щелкните по кнопке ОК.

Рис.2.6. Вывод формул

2. СЧЁТ(значение1;значение2;...) – функция подсчитывает количество чисел в списке аргументов. Например: СЧЁТ(А1:А9).

3. СРЗНАЧ(число1; число2;…). Возвращает среднее (арифметическое) своих аргументов, которые могут быть числами или именами, массивами или ссылками на ячейки с числами. Например: СРЗНАЧ(A3:А10).

4. КОРЕНЬ(число)

Функция вычисляет квадратный корень из положительного числа. Если число отрицательное (ожидается, что может быть отрицательным в процессе расчетов), то следует использовать функцию модуля числа.

5. АВS(число) – функция возвращает модуль числа (рис.2.7)

 

Рис.2.7. Пример использования функции КОРЕНЬ()

6. ЕХР(число) – возвращает экспоненту числа. Функция вычисляет результат возведения в степень основания натурального логарифма (е=2,71878…) Степень указывается в скобках как аргумент функции. Например: EXP(3)=e³.

7. LN(число) – в ычисляет натуральный логарифм числа. Данная функция является обратной по отношению к EXP().

8. LOG(число; основание) – в ычисляет логарифм числа по заданному основанию. Например: LOG(23,3).

9. МАКС(диапазон) – функция служит для поиска максимального элемента в диапазоне, указанном в качестве аргумента.

10. МИН(диапазон) – функция служит для поиска минимального элемента в диапазоне, указанном в качестве аргумента.

11. ЕСЛИ(условие; выражение 1; выражение 2 ) – логическая функция ЕСЛИ() проверяет условие, записанное первым в списке аргументов, на истинность. Если условие оказывается истинным, то управление передается к выражению 1, а выражение 2 игнорируется. Если условие оказывается ложным, то управление передается к выражению 2, а выражение 1 игнорируется. Таким образом, реализуется алгоритм разветвляющегося вычислительного процесса, имитирующего ход принятия решения. На языке записи алгоритма в виде блок-схем логические действия изображаются в виде ромба с двумя исходящими от него линиями, символизирующими два возможных взаимоисключающих варианта дальнейшего хода вычислительного процесса (рис.2.8).

 

 

Рис.2.8. Логическое действие в виде блок-схемы

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

Рассмотрим примеры применения функции ЕСЛИ(). [4]

Пример 2.1. В зависимости от значений переменных F и R переменная У должна принимать следующие символьные значения:

(2.1)

Решение.

Пусть переменной F соответствует ячейка А1, переменной R – ячейка А2, а переменной У – ячейка A3. В этом случае задачу можно сформулировать так: сравнить числовые значения, записанные в ячейках А1 и А2, и вывести полученный результат сравнения в ячейку A3 в виде символьных констант «F>R» и «F<=R».

1. Введите в ячейки А1 и А2 любые числовые константы, например 7 и 4 (рис. 2.9).

2. В ячейку A3 запишите формулу, основанную на логической функции ЕСЛИ().

3. Подтвердите ввод и убедитесь в правильности полученного результата (рис.2.9).

Рис.2.9. Пример решения с использование функции ЕСЛИ()

12. Логическая функция И() – ф ункция также называется функцией логического умножения и имеет следующий синтаксис:

И(условие 1;условие 2;...)

В качестве аргументов функция использует условия (для примера показано два условия, но может быть больше). Функция возвращает значение ИСТИНА, если все входящие условия истины. Если хотя бы одно условие окажется ложным, то функция возвращает значение ЛОЖЬ.

13. Логическая функция ИЛИ() – ф ункция так же называется функцией логического сложения и имеет следующий синтаксис:

ИЛИ(условие 1; условие 2;...)

Функция возвращает значение ИСТИНА, если хотя бы одно из условий истинно. Функции И() и ИЛИ() часто применяются совместно с функцией ЕСЛИ() в качестве вложенных функций.

Пример 2.2. Определить наибольшее из трех чисел.

Решение

1. Запишите числа, подлежащие сравнению, в ячейки A1, A2, A3.

2. Запишите в ячейку А4 формулу (рис. 2.10), содержащую вложение функций ЕСЛИ() и И().

Рис.2.10. Пример записи функций ЕСЛИ() и И()

Вложение функции И() позволяет сформировать проверку одновременного выполнения двух условий для определения, что А1 больше А2 и A3, Если оба условия истинны, то в ячейку А4 выводится текст А1 наибольшее. Если нет, то управление передается второй функции ЕСЛИ() с вложенной функцией И() и проверяется одновременное выполнение двух условий для определения, что A2 больше А1 и A3. Если оба условия истинны, то в ячейку А4 выводится текст А2 наибольшее. Если нет, то в ячейку выводится текст A3 наибольшее.

Пример 2.3. Выяснить, есть ли среди трех чисел отрицательные.

Решение

1. Введите исходные числа в ячейки A1, A2, A3.

2. В ячейку А4 запишите формулу с вложенной логической функцией ИЛИ() (рис. 2.11).

Рис.2.11. Пример записи формулы с вложенной функцией ИЛИ()

Если хотя бы одно из условий окажется истинным, то функция ИЛИ возвратит значение ИСТИНА, управление передастся второму аргументу и в ячейку А4 будет выведен текст Есть отрицательные числа. Если ни одно из условий не окажется истинным, то функция ИЛИ возвратит значение ЛОЖЬ и в ячейку А4 будет выведен текст Отрицательных чисел нет.

Пример 2.4. Составить формулу, которая выдавала бы текст приветствия на вводимое время следующим образом:

от 0 до 7 часов – «Доброй ночи»

от 7 до 11 часов – «Доброе утро»

от 11 до 18 часов – «Добрый день»

от 18 до 24 часов – «Добрый вечер»

Предусмотреть защиту от неправильного ввода: время суток не может выражаться отрицательными числами и числами больше 24.

Решение

1. Формализуйте задачу. Представьте условия задачи как диапазон числовых значений, каждому из которых надо поставить в соответствие символьную константу: «Доброй ночи», «Доброе утро» и т. д.

2. Составьте исходную таблицу (рис.2.12). Текущее время будет вводиться в ячейку В2, рабочая формула будет введена в ячейку ВЗ и там же будет выводиться текст приветствия.

3. Используя логические функции ЕСЛИ(), И(), ИЛИ(), составьте и запишите в ячейку ВЗ рабочую формулу (рис. 2.12).

Рис.2.12. Пример записи и решения задачи 5

Формула составлена по принципу вложения: четыре вложения функции ЕСЛИ() в саму себя, четыре вложения функции И() в функцию ЕСЛИ(), одно вложение функции ИЛИ() в ЕСЛИ(). Формула имеет следующую структуру:

=ЕСЛИ(И(условия);«сообщение»;ЕСЛИ(И(условия);«сообщение»;

ЕСЛИ(И(условия);«сообщение»;ЕСЛИ(И(условия);«сообщение»;

ЕСЛИ(ИЛИ(условия);«сообщение»))))).

Результат решения задачи представлен на рисунке 2.13.

Рис.2.13. Полученный результат решения задачи 4

14. РАНГ(Число; Ссылка; Порядок) – функция в озвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке. Первые два аргумента являются обязательными. Третий аргумент определяет способ упорядочения. Если он равен нулю или опущен, то наилучшим считается наибольшее число и ему присваивается ранг, равный 1. Если третий аргумент равен любому ненулевому числу, то наилучшим считается наименьшее число и ему присваивается ранг, равный 1. Например:=РАНГ(A2;$A$2:$A$6).Наибольшему числу будет присвоен ранг 1.

Рассмотрим пример использования функции РАНГ.

Пример 2.5. Ячейки A2:A6 содержат числовые данные (рис.2.14). Присвоить числам ранг при условии, что наилучшим считается наибольшее число, и поместить результат в ячейках B2:B6.

Решение

Для решения такой задачи используется статистическая функция =РАНГ(Число; Ссылка; Порядок). Первые два аргумента являются обязательными. Третий аргумент определяет способ упорядочения. Если он равен нулю или опущен, то наилучшим считается наибольшее число, и ему присваивается ранг, равный 1. Если третий аргумент равен любому ненулевому числу, то наилучшим считается наименьшее число, и ему присваивается ранг, равный 1.

Таким образом, в ячейку B2 нужно ввести формулу:

=РАНГ(A2;$A$2:$A$6) и скопировать ее в ячейки B3:B6 (рис.2.14).

 

 

 

Рис.2.14. Определение ранга

16. СУММЕСЛИ(диапазон; критерий; диапазон суммирования) – функция суммирует ячейки, заданные указанным условием. Например: =СУММЕСЛИ (A1:A6;">160000";B1:B6).

17. ВПР(искомое значение; таблица массив; номер столбца; параметр) – функция называется «вертикальный поисковый ряд». Ищет значение в первом столбце массива и возвращает значение из ячейки в найденной строке и указанном столбце. По умолчанию таблица должна быть отсортирована по возрастанию. Например: =ВПР(C3;$G$3:$H$8;2). Функция ВПР находит точно такое же значение ячейки C3 в первом столбце диапазона $G$3:$H$8 и возвращает соответствующее значение второго столбца этого диапазона, так как третий аргумент равен двум.

На примере покажем, как автоматизировать процесс ввода однотипных, но различных формул, используя функцию вертикального просмотра ВПР.

Пример 2.6. Постановка задачи. Два цеха производят продукцию трех видов – прибор «Альфа» артикулов А1 и А2, прибор «Бета» артикулов Б1 и Б2, прибор «Гамма» артикулов В1 и В2. Цех 1 выпускает приборы «Альфа» и «Бета». Цех 2 выпускает приборы «Бета» и «Гамма». Имеются данные о количестве приборов, произведенных каждым цехом за январь месяц. Известны также издержки, приходящиеся на единицу продукции каждого артикула.

Определить общие издержки каждого цеха по приборам каждого артикула в январе. Определить количество приборов «Бета», изготовленных первым цехом и вторым цехом в январе.

Рис.2.15. Результат решения задачи

Решение

В ячейках A1:E10 и G1:H8 (рис.2.15) содержатся исходные данные задачи. Результат решения приведен в ячейках E3:E10. С математической точки зрения задача решается элементарно. Для расчета общих издержек в ячейку E3 вводится формула: =ВПР(C3;$G$3:$H$8;2)*D3 и размножается на ячейки диапазона E3:E10. Функция ВПР находит точно такое же значение ячейки C3 в первом столбце диапазона $G$3:$H$8 и возвращает соответствующее значение второго столбца этого диапазона, так как третий аргумент равен двум.

Для определения количества приборов «Бета», изготовленных первым цехом и вторым цехом в январе, введенные текстовые данные для задач требуют объединения ячеек: A12:C12, A13:C13, A14:C14 и A15:C15. Для расчета количества приборов «Бета», изготовленных первым цехом и вторым цехом в январе, нужно ввести в ячейки D12 и D13 следующие формулы:

=СУММЕСЛИ(B3:B6;B5;D3:D6)

и =СУММЕСЛИ (B7:B10;B7;D7:D10).

Для расчета количества приборов «Бета», изготовленных обоими цехами в январе, нужно ввести в ячейку D14 следующую формулу: =СУММЕСЛИ(B3:B10;B5;D3:D10).

Для расчета количества приборов «Бета» артикула Б1, изготовленных обоими цехами в январе, нужно ввести в ячейку D15 следующую формулу: =СУММЕСЛИ(C3:C10;C5;D3:D10).

18. ГПР(искомое значение; таблица; номер строки; параметр) – функция называется «горизонтальный поисковый ряд», так как производит поиск параметра искомое значение в самой верхней строке диапазона поиска. Счет номеров строк для параметра номер строки начинается с единицы. Таким образом, функция ищет значение в верхней строке таблицы и возвращает значение ячейки, находящейся в указанной строке того же столбца. Параметр определяет тип поиска: 0 (или ЛОЖЬ) – точный поиск, 1 (или ИСТИНА) – приблизительный.

Рассмотрим пример применения функции ГПР.

Пример 2.7. Имеется обширная электронная таблица, в которой записаны индексы товаров и их цены (рис. 2.18). Приведен небольшой фрагмент этой большой таблицы. Менеджеру по продажам нужно срочно узнать цены ряда товаров по их индексам. Поскольку таблица большая, поиск «вручную» неэффективен. Что следует сделать менеджеру?

Решение

1. Менеджеру следует составить таблицу Запрос (рис. 2.18) с двумя рабочими ячейками Индекс товара и Цена товара.

2. В ячейку Индекс товара менеджер должен ввести индекс интересующего его товара, а в ячейку Цена товара записать формулу для поиска и чтения содержимого ячейки. В формуле следует использовать функцию ГПР(), так как поиск входа в диапазон возможен только по значению индекса, который записан в верхней строке диапазона (рис. 2.17).

Рис.2.17. Запись функции ГПР

3. Введите в ячейку А9 индекс СК. Подтвердите ввод и убедитесь, что в ячейке А9 получено значение цены товара с индексом СК.

В качестве искомого значения возможно использовать символы, заключенные в кавычки. Можно вместо символа указать адрес ячейки А9.

Рис. 2.18. Результат решения примера 7

19 СЧЁТЕСЛИ(диапазон; критерий) – функция подсчитывает количество непустых ячеек в удовлетворяющих заданному условию.Например: =СЧЁТЕСЛИ(A1:A6;">10000") – подсчитывает в диапазоне A1:A6 количество значений, которые больше 10000.

Рассмотрим пример применения функции СЧЁТЕСЛИ.

Пример 2.8. Для таблицы рисунка 2.19 определить число поездок, сделанных Петровым.

Решение

Создайте вспомогательную мини-таблицу Число ездок и запишите в ячейку В13 функцию СЧЕТЕСЛИ() (рис.2.19) В этом случае вы указываете адрес ячейки, в которой записана фамилия интересующего вас водителя (А4 – это Петров) Можно поступить иначе. Создайте вспомогательную таблицу с двумя полями Фамилия и Число поездок. В ячейку А14 будете вводить фамилию водителя. В функции СЧЕТЕСЛИ() на месте параметра критерий стоит адрес этой ячейки. Или же можно указывать фамилию, заключенную в кавычки, прямо в функции в позиции критерий.

=СЧЁТЕСЛИ(A3:A11;A4)

Рис.2.19. Результат решения примера 8

КОНТРОЛЬНЫЕ ВОПРОСЫ К РАЗДЕЛУ 2

1. Перечислите типы данных Excel.

2. Как выравниваются числовые данные в ячейке таблицы по умолчанию?

3. Как выравниваются текстовые данные в ячейке таблицы по умолчанию?

4. Какие существуют способы записи формул в ячейки?

5. Для чего предназначены абсолютные и относительные ссылки?

6. Какими способами можно вычислить сумму?

7. Какова ширина ячейки по умолчанию?

8. Как работает функция ЕСЛИ?

9. Каково назначение функции РАНГ?

10. Каково назначение функции ВПР и ГПР?

РЕДАКТИРОВАНИЕ ДАННЫХ

Выделение ячеек

Выделение ячеек является важнейшим техническим приемом при работе с Excel. Преобразование данных производится только над выделенными объектами, т.е. вначале надо выделить ячейки, а затем указать, что с ними делать. [3], [6]

Выделение одиночной ячейки производится простым переходом в нее щелчком мыши или с помощью клавиатуры. Выделенная таким образом ячейка будет обрамлена жирной линией, а в правом нижнем углу появится очень важный элемент – маленький черный квадрат, называемый маркером заполнения (рис.3.1). Адрес и содержимое выделенной ячейки отображается в строке формул.

 

 

Рис. 3.1. Выделение одиночной ячейки

Выделение строки или столбца можно производить мышью или с помощью клавиатуры.

С помощью мыши: щелкнуть мышью по цифре-заголовку строки или по букве-заголовку столбца (на бордюре). После выделения вся строка или столбец закрашивается в темный цвет (кроме самой первой ячейки), а в правом нижнем углу выделенной цифры или буквы бордюра отображается маркер заполнения (рис. 3.2).

С помощью клавиатуры для выделения столбца нажать две клавиши Ctrl и Пробел, а для строки – Shift и Пробел.

Маркер заполнения

Рис. 3.2. Выделение строки

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

С помощью мыши

Способ 1: щелкнуть мышью на любой из угловых ячеек прямоугольной области и, не отпуская кнопку мыши, перетащить курсор в противоположный угол области. Курсор во время этой операции сохраняет вид белого креста. Перетаскивать курсор можно по всем четырем диагональным направлениям. Выделенная область отмечается цветом, кроме исходной ячейки, от которой велось построение и которая является текущей.

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

С помощью клавиатуры

Способ 1: установить курсор на любой из угловых ячеек, удерживая нажатой клавишу Shift, с помощью клавиш со стрелками выделить остальную часть области.

Способ 2: установить курсор на исходной ячейке, нажать и отпустить клавишу F8, клавишами со стрелками выделить прямоугольную область, нажать и отпустить клавишу F8.

Выделение несмежных ячееки прямоугольных областей – щелкнуть мышью на первой ячейке (или выделить первую прямоугольную область); удерживая нажатой клавишу Ctrl, выделить вторую ячейку (или прямоугольную область) и т.д.

Выделение всего листа

Способ 1: щелкнуть мышью на кнопке ВЫДЕЛИТЬ ВСЕ.

Способ 2: нажать три кнопки Ctrl+Shift+Пробел.

Способ 3: нажать Ctrl+A (А латинская).