Сохранитена диске С: документ Занятие 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. С чего начинается написание формулы?

2. Что такое абсолютные ссылки и для чего они нужны?

3. Что такое мастер функций?

4. Как можно быстро вставить функцию?

5. Что такое Математические функции?

6. Что такое Статистические функции?

7. Для чего используется функция Дата и время?