- •Введение
- •Обработка данных с использованием арифметических формул Учебные цели изучения темы
- •Краткие теоретические сведения
- •Этапы решения задач обработки данных в среде табличного процессора
- •Задачи Задача 1.1. Расчет учебной нагрузки
- •Задача 1.2. Расчет платежей за воду
- •Задача 1.3. Платежный календарь
- •Подсчет и суммирование данных Учебные цели изучения темы
- •Краткие теоретические сведения
- •Функция(аргумент1; аргумент2; ... )
- •Суммирование всех значений диапазона
- •Сумм (данные).
- •Условное суммирование по одному критерию
- •Суммесли (диапазон; критерий; диапазон_суммирования)
- •Задача 2.2. Учет товарного запаса
- •Связывание данных посредством формул с двумерными и трехмерными ссылками Учебные цели изучения темы
- •Краткие теоретические сведения
- •Имя_Листа ! Адрес_Ячейки.
- •[Имя_Рабочей_книги] Имя_Листа ! Адрес_Ячейки
- •Задачи Задача 3.1. Потребительский кредит
- •Задача 3.2. Расчет зарплаты бригады
- •Задача 3.3. Расчет платежей за электроэнергию
- •Задача 3.4. Долгосрочное кредитование
- •Обработка данных с использованием условных формул Учебные цели изучения темы
- •Краткие теоретические сведения Логические выражения
- •И (логическое_значение1; …; логическое_значение30).
- •Или (логическое_значение1; …; логическое_значение30).
- •Не (логическое_значение).
- •Логическая функция если
- •Задачи Задача 4.1. Расчет комиссионных от продаж
- •Задача 4.2. Оплата телефонных переговоров
- •Задача 4.3. Счет за проживание в гостинице
- •Задача 4.4. Расчет стипендии
- •Задача 4.5. Расчет квартальной премии бригады
- •Задача 4.6. Поток товарно-материальных запасов
- •Задача 4.7. Учет расходования гсм
- •Обработка данных типа дата/вреМя с использованием функции выбора Учебные цели изучения темы
- •Краткие теоретические сведения
- •Дата (год; месяц; день).
- •Сегодня (). Создание ряда последовательных дат
- •Извлечение из даты дня, месяца или года
- •Возвращение дня недели, соответствующего любой дате
- •Выбор значений из списка по индексу
- •Выбор (номер_индекса; значение1; значение2; ...).
- •Возвращение даты, отстоящей на заданное количество рабочих дней от заданной даты
- •Определение количества рабочих дней между двумя датами
- •Определение разницы между двумя датами в годах
- •Задачи Задача 5.1. Учет издания методической литературы
- •Задача 5.2. Заработанный доход врачей‑стоматологов
- •Задача 5.3. Анализ опозданий сотрудников фирмы
- •Задача 5.4. Начисление заработной платы при трехсменной работе
- •Обработка текстовых данных с использованием функции вертикального просмотра таблицы Учебные цели изучения темы
- •Краткие теоретические сведения Извлечение символов из текстовой строки
- •Пстр (текст; начальная_позиция; количество_символов)
- •Преобразование текста, отображающего число, в число
- •Объединение несколько текстовых элементов в один
- •Поиск вхождения одной текстовой строки в другую
- •Преобразование знаков в текстовой строке из одного регистра в другой
- •Строчн (текст); прописн (текст). Присвоение имен ячейкам и диапазонам
- •Вертикальный просмотр таблицы
- •Задачи Задача 6.1. Расчет транспортного налога
- •Задача 6.2. Расшифровка кода группы и номера зачетной книжки студента
- •Задача 6.3. Анализ кодов isbn
- •Задача 6.4. Определение знака зодиака
- •Критериальные задачи Задача «Гороскоп»
- •Задача «Расчет стоимости заказа текстильных этикеток»
- •Задача «Расчет зарплаты ппс»
- •Задача «Учет движения товаров на мебельном складе»
- •Вопросы для критериальных тестов
- •Перечислить
- •Литература
Логическая функция если
MS Excel располагает набором функций, которые можно применять для анализа данных с использованием условий. Логическая функция ЕСЛИ предоставляет формулам способность к принятию решений: после выполнения логической проверки она возвращает одну из двух альтернатив. Ее синтаксис:
= ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь )
лог_выражение – условие, которое требуется проверить (принимает значения ИСТИНА или ЛОЖЬ);
значение_если_истина – значение, которое возвращается, если условие истинно;
значение_если_ложь –значение, которое возвращается, если условие ложно.
Замечания:
тип значения, возвращаемого функцией ЕСЛИ, соответствует типу выбранного аргумента значение_если_истина или значение_если_ложь. Если эти аргументы не заданы, функция ЕСЛИ в зависимости от результата логической проверки возвращает значение ИСТИНА или ЛОЖЬ;
аргументы значение_если_истина, значение_если_ложь могут иметь вид формул;
чтобы конструировать более сложные проверки, функции ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь (максимальный уровень вложений равен 7).
Пример 4.3
Пусть ячейка А2 содержит нормированное количество рабочих дней в текущем месяце, а ячейка В10 – количество дней, фактически отработанных рабочим. Записать формулу для определения сверхурочных дней, отработанных этим рабочим.
=ЕСЛИ (B10>$A$2; B10 – $A$2; 0)
Пример 4.4
Известны показатели планируемого (B7) и фактически произведенного (C7) объемов продукции. Записать формулу, с помощью которой делается вывод о выполнении плана:
C7=B7 – «План выполнен»;
C7>B7 – «План перевыполнен»;
C7<B7 – «План невыполнен».
Формула имеет вид:
= ”План ” & ЕСЛИ (C7=B7; “выполнен”;
ЕСЛИ (C7>B7;”перевыполнен”;”невыполнен”))
В данной формуле вторая функция ЕСЛИ вложена в первую в качестве аргумента значение_если_ложь. Если условие C7=B7 истинно, то выводится значение “выполнен”, и работа формулы заканчивается. В противном случае анализируется условие C7>B7 вложенной функции ЕСЛИ, и в зависимости от его значения выводится соответствующий текст.
Задачи Задача 4.1. Расчет комиссионных от продаж
Предметная область: расчет заработанного дохода менеджеров. Элементы предметной области: фамилии менеджеров (15 фамилий), объемы продаж (в грн) каждого менеджера.
Отношения: заработанный доход менеджера составляют комиссионные от объема произведенных им продаж. Если объем продаж менеджера не превышает средний объем по фирме, то ему назначается обычный комиссионный (5,5%), в противном случае – премиальный (6,25%). С заработанных комиссионных удерживаются подоходный налог (до 170 грн – 15%, свыше – 20%) и взнос в пенсионный фонд (до 150 грн – 1%, свыше – 2%).
Требования: разработать и реализовать табличную модель учета доходов менеджеров и размеров выплат; выполнить числовое и стилевое форматирование данных.
Методические указания к решению
Ввести макет таблицы, согласно рис. 23
|
A |
B |
C |
D |
E |
F |
1 |
Расчет заработанного дохода менеджеров |
|||||
2 |
Средний объем продаж |
? |
|
|
||
3 |
Ставка комиссионных |
5,5% |
|
|
||
4 |
Ставка премиальных комиссионных |
6,25% |
|
|
||
5 |
Ставка взноса в пенсионный фонд |
1% |
до 150 грн |
|||
6 |
|
|
|
2% |
от 150 грн |
|
7 |
Ставка подоходного налога |
15% |
до 170 грн |
|||
8 |
|
|
|
20% |
от 170 грн |
|
9 |
Менеджеры |
Продажи |
Комиссионные |
Налог |
П/фонд |
К выдаче |
10 |
Александров |
1200 |
? |
? |
? |
? |
Рис. 23. Макет таблицы к задаче «Расчет комиссионных от продаж»
Ввести формулу в ячейку D2.
Используя Мастер функций, ввести формулу для расчета комиссионных, которая имеет вид:
= B10*ЕСЛИ (В10<$D$2; $D$3; $D$4).
Рис. 24. Диалоговое окно для ввода аргументов функции ЕСЛИ
Ввести аналогичные формулы для расчета подоходного налога и взноса в пенсионный фонд.
Выполнить числовое и стилевое форматирование данных.