Сохранитена диске С: документ Занятие 2с результатами выполнения заданий в своей папкеРабота в Excel.

ЗАНЯТИЕ 2

Табличные функции. Мастер функций

В электронной таблице Excel встроенные функции можно разбить на классы:

- математические и тригонометрические;

- информационные;

- статистические;

- базы данных;

- финансовые и т.д.

В общем случае синтаксис функций следующий:

F(аргумент 1, аргумент 2, …, аргумент N).

Для вставки функций либо в процессе ввода, либо в режиме редактирования нужно выбрать вкладку Формулы → панель Библиотека функций, на ней будут перечислены все классы функций процессора Excel и их конкретный вид (рис.1.3.1). Выбор требуемой функции приводит к ее вставке в формулу в той позиции, которую занимал курсор ввода.

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

Так же на строке формул имеется кнопка для быстрой вставки функции (рис.1.3.1).

В Excel есть специальное средство для эффективной работы с функциями – Мастер функций.

Рис. 1.3.1. Панель Библиотека функций. Кнопка Вставить функцию

Вычислите «Среднее арифметическое» последовательности чисел, для этого:

1. Откройте на диске С: вашу папку с именем Работа вExcel.

2. В папке создайте фал Excel с именем Занятие 2, откройте его.

3. На Листе 1 введите в ячейки с A1 по D1 произвольные числа. Например: 2, 3, 5, 8.

4. Выделите для результата ячейку Е1 и нажмите кнопку Вставка функции на строке формул (рис.1.3.2). Появится диалоговое окно Мастера функций, показанное на рис.1.3.2

Рис. 1.3.2. Окно Мастер функций

5. В окне Категория выберите категорию Статистические, а в окне Функция — функцию СРЗНАЧ, нажмите кнопку ОК. Появится диалоговое окно выбранной функции (рис. 1.3.3). Переместите это окно так, чтобы были видны ячейки с данными, которые будут выступать в качестве аргументов функции.

6. Мышкой выделите диапазон ячеек с A1по D1, при этом окно функции сворачивается в одну строку. После того как вы отпустите кнопку мыши, окно снова развернется, и в нем в поле ввода первого параметра появится строка A1:D1, а соответствующий диапазон в таблице будет обведен мерцающей рамкой. В нижней части окна функции выводится вычисленное значение функции. Нажмите кнопку ОК.

Рис. 1.3.3. Окно Аргументы функции

7. В ячейке Е1 будет виден результат вычисления 4,25, а в строке формул отобразится формула, созданная Мастером функцийСРЗНАЧ (А1:D1).

Математические функции

Математические функции выполняют вычисления арифметические и тригонометрические.

Статистические функции.В статистических функциях в качестве аргументов может быть использован список значений. В списке могут присутствовать числа, адреса ячеек, их имена, адреса и имена блоков ячеек. В списке они разделяются запятой или точкой с запятой «; ». Пустые ячейки, попавшие в список аргументов, при расчетах игнорируются.

Найдите среди заданных чисел «наибольшее», «наименьшее», вычислить «сумму чисел» и «среднее значение», посчитать общее количество чисел, количество нулевых, количество положительных и сумму отрицательных с помощью математических и статистических функций, для этого:

1. Перейдите на Лист 2 и введите в ячейки с В3 по J3 последовательность чисел:

5 –41 0 71 13 –11 20 0 15

2. Введите в ячейки с А5 по А12 соответственно следующие названия:

A5 – СУММА;

A6 – КОЛИЧЕСТВО;

A7 – КОЛИЧЕСТВО НУЛЕЙ;

A8 – КОЛИЧЕСТВО ПОЛОЖИТ;

A9 – МАКСИМУМ;

A10 – СУММА ОТРИЦ;

A11 – МИНИМУМ;

Ф12 – СРЕДНЕЕ ЗНАЧЕНИЕ.

3. Введите в ячейки с В5 по В12 соответственно следующие формулы:

!При вводе формул смотри пример «Ввод среднего арифметического» и рис. 1.3.2, 1.3.3.

В5 – =CУMM(В3:J3);

В6 – =CЧET(В3:J3);

В7 – =СЧЕТЕСЛИ(В3:J3;"=0"), при вводе формулы в строке Критерий введите «=0», так как нам нужно посчитать количество нулей (рис. 1.3.4);

Рис. 1.3.4. Ввод функцииСЧЕТЕСЛИ

В8 – =СЧЕТЕСЛИ(В3:J3;">0"), при вводе формулы в строке Критерий введите «>0», так как нам нужно посчитать количество чисел, которые больше нуля (рис. 1.3.4);

В9 – =MAKC(В3:J3) ;

В10 – =СУММЕСЛИ(В3:J3;"<0")"), при вводе формулы в строке Критерий введите «<0», так как нам нужно посчитать сумму отрицательных чисел (рис. 1.3.5);

Рис. 1.3.5. Ввод функцииСУММЕСЛИ

В11 – =MИН(В3:J3) ;

В12 – =CP3HAЧ(В3:J3).

Результат вычислений представлен на рис. 1.3.6.

Рис. 1.3.6. Результат вычислений математических и статистических функций

Функция «Дата и время»

При работе с датами электронные таблицы хранят их в виде целого числа, соответствующего количеству дней, прошедших с 01.01.1900 до указанной даты. Но на экране даты представляются в привычной нам форме, если для соответствующей ячейки задан формат «Дата».

Для использования функций «Дата и время» выполните следующие действия:

1. Перейдите на лист 3 и в ячейку А1 введите надпись “Текущая дата”.

2. В ячейку А2 введите надпись “Размер премии”.

3. В ячейку В1 введите текущую дату с помощью функции Сегодня, для этого выберите вкладку Формулы→ пункт Дата и время → функцию Сегодня и нажмите кнопку ОК.

4. В ячейку B2 введите размер премии 1500, при этом задайте формат ячейки Денежный. Для этого нажмите правой кнопкой мыши на ячейке В2 (появится контекстное меню) → пункт Формат ячеек → вкладка Число → формат Денежный (рис.1.3.7).

Рис. 1.3.7. Окно Формат ячеек

5. В ячейки с А5 по F5 введите следующие данные:

А5 - № п/п

В5 - Фамилия

С5 - Дата рождения

D5 - Возраст

E5 - Юбилей

F5 - Премия

6. Столбец № п/п с ячейки A6 по A20 заполните числами с 1 до 15, используя функцию Автозаполнение.

7. Столбец Фамилия, Дата рождения (предварительно установив формат ячеек Дата) заполните данными из табл. на рис.1.3.8.

8. В ячейках D6:D20 создайте формулу для вычисления возраста, для этого введите в ячейку D6 следующую формулу (формат ячейки выставить Числовой):

=ГОД($B$1) – ГОД(C6)

9.Скопируйте формулу из ячейки D6 в ячейки D6:D20.

10. В ячейках столбца Юбилей должен содержаться текст “юбилей” либо символ “-“ в зависимости от того, какое число содержится в соседней ячейке слева.

11. Только если в ячейках D6:D20 содержится число, кратное 5, будем считать возраст юбилейным.

12. Чтобы создать формулу, определяющую юбилейный возраст, нужно воспользоваться логической функцией ЕСЛИ (в логическом выражении приведенной формулы проверяется условие кратности 5 числа в ячейке D6, для этого используется математическая функция ОСТАТ):

=ЕСЛИ(ОСТАТ(D6;5)=0;"юбилей";"-")

13. Скопируйте формулу из ячейки Е6 в ячейки Е6:Е20.

14. Оформить ячейки с текстом юбилей шрифтом «полужирный курсив» красного цвета с помощью команды Формат ячеек. Для этого нажмите правой кнопкой мыши на ячейке (появится контекстное меню) → пункт Формат ячеек → вкладка Шрифт.

15. Заполните ячейки F6:F20 в соответствии со следующим: премия начисляется только тем лицам, у которых юбилей. Для решения этой задачи необходимо использовать функцию ЕСЛИ, в ячейку F6 введите формулу:

=ЕСЛИ(E6="юбилей";$B$2;"-")

Результат вычисления представлен на рис 1.3.8.

Рис. 1.3.8. Результаты вычислений

Рассчитайте самостоятельно:

- Используя функцию СЧЕТЕСЛИ, вычислите, сколько в таблице:

- людей – юбиляров;

- старше 31 лет;

- моложе 30 лет.

Результат вычисления представлен на рис 1.3.8.

Сохранитена диске С: документ Занятие 2с результатами выполнения заданий в своей папкеРабота в Excel.

Контрольные вопросы:

1. С чего начинается написание формулы?