Пример
1. Создать таблицу Excel:
2. Заполнить столбец Характер долга с учётом указанных условий: если Долг=0, то выдать сообщение «Долга нет». В противном случае, если 0< Долг<= 2000, то выдать «Долг небольшой». В противном случае, если 2000<Долг, то выдать «Долг большой». В остальных случаях выдать «Долг очень большой».
Установить курсор в ячейку С2 и ввести формулу:
· Выбрать функцию ЕСЛИ из списка встроенных функций.
· Установить курсор в поле Логическое выражение и указатьВ2=0
· Установить курсор в поле Значение_если _истина и ввести Долга нет.
· Установить курсор в поле Значение_если_ложь и раскрыть список с функцией Если
· Выбрать функцию снова Если и заполнить параметры нового второго окна функции Если. Установим курсор в поле Логическое выражение, раскроем список функций, показанный выше. Выберемиз него функцию И. Создадим выражение И(В2>0;В2<=2000):
После нажатия на кнопку ОК появится следующее окно, т.к. во второй функции Если нарушен синтаксис (нет истинного и ложного значения):
После нажатия на кнопку ОК следует доввести вторую функцию Если, используя кнопку Вставить функцию, предварительно установив курсор на вторую функцию Если в строке формул:
· Установим курсор в поле Значение_если _истина и введем Долг небольшой:
· Установим курсор в поле Значение_если_ложь и введем Долг большой.
Получим:
Использование математических, статистических функций
Пример1
1) Создать файл Excelс именем Функции.
2) На 1 листе создать справочную таблицу (Рисунок 1 Образец справочной таблицы)[1].Ярлык листа назвать «Справка ».
Рисунок 1 Образец справочной таблицы
Пример 1
1) В файле Функции создать ведомость расчета заработной платы сотрудников предприятия[2], отформатировать таблицу по образцу (см Рисунок 2 Образец таблицы примера 1).Ярлык листа с ведомостью расчета заработной платы назвать «Р_вед».
Рисунок 2 Образец таблицы примера 1
2) Для вывода на листе «Р_вед» соответствующей табельному номеру фамилии использовать функцию ПРОСМОТР:
- Отсортировать справочную таблицу на листе «Справка » (А1:В11) по возрастанию значений табельного номера.
- Присвоить А1:А11 на листе «Справка » имя Табельный_номер, В1:В11 – имя ФИО.
- В ячейку С4 столбца «Фамилия, инициалы » листа «Р_вед» ввести формулу расчета ПРОСМОТР
Рисунок 3 Шаг 1 функции Просмотр
Рисунок 4 Шаг 2 функции Просмотр
=ПРОСМОТР(В4;Табельный_номер;ФИО).
- Скопировать формулу во все ячейки столбца «Фамилия, инициалы».
3) Дополнительную справочную информацию (см. Рисунок 5 Справочная таблица примера 1), необходимую для расчета заработной платы разместить на отдельном листе с именем «Справка_ЗП».
Рисунок 5 Справочная таблица примера 1
4) В ячейку F4 столбца «Начислено » листа «Р_вед» ввести формулу расчета: =ПРОИЗВЕД(D4;E4).
5) Скопировать формулу во все ячейки столбца «Начислено».
6) В ячейку G4 столбца «Удержано (НДФЛ)» ввести формулу =ПРОИЗВЕД(F4;Справка_ЗП!$В$2), где ссылка Справка! $В$2 является абсолютной ссылкой на ячейку со ставкой НДФЛ (13%).
7) Скопировать формулу во все ячейки столбца «Удержано (НДФЛ)».
8) В ячейку H4 столбца «К выплате» ввести формулу =СУММ(F4;-G4).
9) Скопировать формулу во все ячейки столбца «К выплате».
10) В ячейку H14 с помощью кнопки «Автосуммирования» ввести формулу расчета итога. =СУММ(H4:H13).
Пример 2
1) В файле Функции создать таблицу «Анализ начислений заработной платы».
2) Отформатировать таблицу по образцу (см. Рисунок 6 Образец таблицы примера 2).Ярлык текущего листа назвать «Анализ».
Рисунок 6 Образец таблицы примера 2
3) Выполнить расчет с помощью следующих функций:
- Итоговая сумма = СУММ(Р_вед!H4:H13);
Примечание:Р_вед!H4:H13 – ссылка на диапазон ячеек листа Р_вед
- Максимальная выплата =МАКС (Р_вед!H4:H13);
- Минимальная выплата =МИН (Р_вед!H4:H13);
- Средняя выплата = СРЗНАЧ (Р_вед!H4:H13);
- Общее кол-во сотрудников =СЧЕТ (Р_вед!H4:H13);
Пример 3
1) В файле Функции создать таблицу «Ведомость поступления товаров» (см. Рисунок 7 Образец таблицы примера 3)[3], ярлык текущего листа назвать «Поступление».
Рисунок 7 Образец таблицы примера 3
2) Создать на листе «Отч_пост» отчетную таблицу по образцу (см. Рисунок 8 Образец отчетной таблицы примера 3).
Рисунок 8 Образец отчетной таблицы примера 3
3) Выполнить расчет с помощью следующих функций:
- СУММЕСЛИ, которая суммирует ячейки диапазона соответствующие указанному критерию.
Критерием суммирования в данной задаче является наименование товара.
- СЧЁТЕСЛИ,которая определяет количество ячеек диапазона соответствующих указанному критерию.
| СУММЕСЛИ Категория «Математические» | Суммирует ячейки, удовлетворяющие заданному критерию. | СУММЕСЛИ(диапазон; критерий; диапазон суммирования) Диапазон — диапазон вычисляемых ячеек. Критерий — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, "32", ">32", "яблоки". Диапазон суммирования— фактические ячейки для суммирования. |
| СЧЕТЕСЛИ Категория «Статистические» | Подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию. | СЧЁТЕСЛИ(диапазон; критерий) Диапазон — диапазон, в котором нужно подсчитать ячейки. Критерий— критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки". |
Общее кол-во товара =СУММЕСЛИ(Поступление!$C$3:$C$17;A3;Поступление! $E$3:$E$17)
Общая стоимость =СУММЕСЛИ(Поступление!$C$3:$C$17;A3;Поступление! $F$3:$F$17)
Кол-во партий поступления
=СЧЁТЕСЛИ(Поступление! $C$3: $C$17;A3)
Итоговая задача
1) Создать новый файл Excel с именем Лаб_работы.
2) На листе с именем «Спр_Лаб_1 » ввести справочную информацию(см. Рисунок 9 Справочная таблица лабораторной работы)[4].
Рисунок 9 Справочная таблица лабораторной работы
3) На листе с именем «Вед_оплаты» создать расчетную таблицу (смРисунок 10 Образец таблицы «Ведомость оплаты электроэнергии»)[5].
Рисунок 10 Образец таблицы «Ведомость оплаты электроэнергии»
4) В столбце «Оплачиваемый месяц » получить необходимую информацию с помощью функции МЕСЯЦ и данных соответствующей ячейки столбца «Дата оплаты », далее скопировать формулу на все ячейки данного столбца.
5) В столбце «Квартира » применить нумерацию квартир с листа «Спр_Лаб_1 » (проверка данных).
6) В столбце «Отв. кв.съемщик» получить необходимую информацию с помощью функции ПРОСМОТР или ВПР, скопировав формулу на все ячейки данного столбца
7) В столбце «Кол-во комнат» получить необходимую информацию с помощью функции ПРОСМОТР или ВПР,скопировав формулу на все ячейки данного столбца.
8) В столбце «Стоимость (руб)» выполнить расчет с помощью функции ПРОИЗВЕД (расход эл. энергии * стоимость Квт энергии),скопировав формулу на все ячейки данного столбца. (стоимость Квт энергии размещается на листе «Спр_Лаб_1 »).
9) На листе с именем «Анализ_оплаты» создать расчетную таблицу «Анализ оплаты электроэнергии» (см. Рисунок 11 Образец таблиц «Анализ оплат электроэнергии»).
Рисунок 11 Образец таблиц «Анализ оплат электроэнергии»
10) В столбец «Месяц » ввести 1-12 с помощью маркера заполнения, «Квартира » ввести 100-120.
11) На листе с именем «Статистика_расхода» создать расчетную таблицу «Статистика расхода электроэнергии» (см.Рисунок 12 Образец таблицы «Статистика расхода электроэнергии»). В А2:А13 ввести 1-12, в столбце Вв соответствующих ячейках сделать соответствующие расчеты.
Рисунок 12 Образец таблицы «Статистика расхода электроэнергии»