- •Введение
- •Обработка данных с использованием арифметических формул Учебные цели изучения темы
- •Краткие теоретические сведения
- •Этапы решения задач обработки данных в среде табличного процессора
- •Задачи Задача 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 вводится с использованием любого из поддерживаемых для нее форматов, после чего она автоматически преобразуется в соответствующий ей порядковый номер, который в дальнейшем используется для вычислений.
Дату можно создать, воспользовавшись функцией ДАТА. Ее синтаксис:
Дата (год; месяц; день).
Например, значение ДАТА (1980; 6; 14) соответствует 14 июня 1980 года.
Аргумент дата в функциях управления датами должен вводиться с использованием функции ДАТА или как результат других формул. Если дата вводится в качестве аргумента функции как текст, могут возникнуть проблемы.
Функция СЕГОДНЯ возвращает текущую дату. Ее синтаксис:
Сегодня (). Создание ряда последовательных дат
В некоторых задачах у пользователя может возникнуть необходимость вставить в рабочий лист целый ряд последовательных дат.
Эффективный способ исполнения этой операции – использование средства Автозаполнение. Для этого в ячейку необходимо ввести первую дату, а затем, удерживая нажатой правую кнопку мыши, перетащить в нужном направлении маркер заполнения. Отпустить кнопку мыши и выбрать из контекстного меню требуемую команду заполнить: по дням, рабочим дням, месяцам, годам.
Протаскивание маркера заполнения левой кнопкой обеспечивает заполнение дат по дням.
Извлечение из даты дня, месяца или года
Эти операции выполняются с помощью функций ДЕНЬ, МЕСЯЦ, ГОД. Их синтаксис имеет вид:
ДЕНЬ (дата в числовом формате);
МЕСЯЦ (дата в числовом формате);
ГОД (дата в числовом формате).
Пусть в ячейке А1 записана дата 2.03.1936. Тогда формула = ДЕНЬ (А1) возвращает 2, формула = МЕСЯЦ (А1) – 3, формула = ГОД (А1) – 1936.
Возвращение дня недели, соответствующего любой дате
Эта операция выполняется с помощью функции ДЕНЬНЕД. День недели определяется как целое в интервале от 1 до 7. Ее синтаксис:
ДЕНЬНЕД (дата_в_числовом_формате; тип)
Значение аргумента тип определяет систему нумерации дней недели для выведения результата:
если аргумент тип отсутствует или равен 1, то отсчет дней начинается с воскресенья (1), как это принято в англоязычных программных продуктах;
если в значение аргумента тип равно 2, функция возвращает 1 для понедельника, 2 – для вторника и т.д.
Выбор значений из списка по индексу
В формулах можно использовать до семи уровней вложения функций. Поэтому выбор значений с помощью функции ЕСЛИ допускает не более семи их вариантов. Существуют задачные ситуации, когда целесообразно использовать функцию ВЫБОР (категория «Функции ссылки и автоподстановки»), позволяющую выбрать одно значение из списка, в котором может быть до 29 аргументов. Ее синтаксис:
Выбор (номер_индекса; значение1; значение2; ...).
номер_индекса – номер выбираемого аргумента значения. Он должен быть числом от 1 до 29, формулой или ссылкой на ячейку, содержащую число от 1 до 29. Если номер_индекса равен 1, то функция ВЫБОР возвращает значение1; если он равен 2, то функция ВЫБОР возвращает значение2 и так далее;
значение1; значение2, … – это от 1 до 29 аргументов значений, из которых ВЫБОР, используя номер_индекса, выбирает значение или выполняемое действие. Эти аргументы могут быть числами, текстовыми строками, ссылками на ячейки, именами, формулами.
Пример 5.1
Требуется определить, какие дни недели соответствуют заданному набору дат. Рис. 30 представляет макет электронной таблицы для рассматриваемого примера.
|
A |
B |
C |
1 |
Определение дня недели по дате |
||
2 |
Понедельник |
|
|
3 |
Вторник |
|
|
4 |
Среда |
|
|
5 |
Четверг |
|
|
6 |
Пятница |
|
|
7 |
Суббота |
|
|
8 |
Воскресенье |
|
|
9 |
Дата |
Номер дня недели |
Название дня недели |
10 |
02.01.03 |
? |
?? |
Рис. 30. Макет таблицы для примера 5.1
Формула для определения номера дня недели (В10):
= ДЕНЬНЕД (А10;2)
Формула для определения названия дня недели (С10):
= ВЫБОР (B10,$A$2,$A$3,$A$4,$A$5,$A$6,$A$7,$A$8)
В справочной системе MS Excel предлагается следующая формула для преобразования даты в название дня недели:
= ТЕКСТ (ДЕНЬНЕД (дата; 2); “дддд”).
К сожалению, ее использование в таком виде некорректно, т.к. она преобразует 1 в воскресенье, 2 – в понедельник и т.д. Верный результат дает формула вида:
= ТЕКСТ (ДЕНЬНЕД (дата; 2) + 1; “дддд”).