Команда Формат
Команда Формат предназначена для работы с ячейками, строками, столбцами, листами.
Отдельные возможности опции ЯЧЕЙКИ были рассмотрены ранее (окно Выравнивание), другие варианты будут описаны дальше.
Меню команд Строка и Столбец практически совпадают.
Опции ширина, подгон ширины, стандартная ширина (высота и подгон высоты) для активной клетки позволяют установить конкретный размер, размер, чтобы данные отражались на экране в установленном формате, возвратиться к стандартному размеру колонки.
Опция Скрыть удаляет с экрана активную строку или активный столбец. Команда Показать выводит скрытые данные внутри выделенных строк или столбцов.
Команды опции Лист - Скрыть и Показать работают аналогично. Команда Переименовать была рассмотрена ранее.
Удалите с экрана столбец с данными об окладе, восстановите.
Посмотрите стандартную высоту строки, содержащий заголовок, установите размер в два раза больше, установите минимальный размер для строки, чтобы текст помещался на экране.
Задание № 1
Выполните все упражнения, помеченные в тексте.
Задание № 2
Загрузите таблицу, созданную при выполнении лабораторной работы № 5 в соответствии со своим вариантом.
Установите порядковые номера для данных в строках таблицы.
Постройте диаграмму по данным последнего столбца
Для данных второго столбца установите имя.
Для самого дорогого продукта (билета, товара, оклада и т.д.) введите примечание.
Организуйте поиск данных в таблице по самостоятельно выбранному шаблону.
Отсортируйте таблицу по одному из параметров (самостоятельно выберите ключ сортировки).
Скопируйте информацию из таблицы на следующий лист.
На втором листе удалите любые две записи.
Уменьшите стоимость исходных данных в два раза.
Задайте для активных листов имена.
Пустые листы удалите.
Сохраните последний вариант на диске.
Покажите выполненное задание.
Лабораторная работа № 7 Мастер функций и логические выражения в Excel
Составим таблицу, содержащую сведения о результатах сдачи экзаменов по пяти предметам для студентов, на примере которой будем изучать работу с функциями и другими командами.
В первую строку занесите заголовок: РЕЗУЛЬТАТЫ СЕССИИ
В ячейки второй строки: Фамилия, Физика, Химия, История, Информатика, Экология.
Заполните семь строк фамилиями студентов и оценками.
По результатам сессии надо назначить стипендию студентам, исходя из следующих правил: если среди оценок есть двойка, стипендию не назначать; если студент получил только пятерки, то назначить стипендию повышенную на 50%; если оценки только пятерки и четверки, стипендия повышается на 25%; во всех остальных случаях - обычная стипендия.
Для определения отличника, двоечника или троечника достаточно вычислить минимальный балл по оценкам для каждого студента, по его значению определить статус студента. Для определения студента, который может рассчитать на стипендию, повышенную на 25%, необходимо, чтобы одновременно минимальная оценка была 4, а максимальная - 5.
Внесите в заголовок таблицы: "Минимум" и "Максимум".
Для определения минимальной оценки используем кнопку "Вставка функции" пиктографического меню:
Установите курсор в клетке G3.
Зафиксируйте мышь на кнопке Вставка функции. В окне диалога в поле "Категория" из списка выберите "Статистические", в поле "Функция" зафиксируйте "МИН":
Кнопка "ОК" выводит следующее окно мастера функций:
Мастер функций выделяет ближайшие клетки с числами: с B3 по F3. В окне "Число 1" указывает этот диапазон - B3:F3, в поле формулы таблицы формула приведена полностью:
= МИН (B3:F3)
Обратите внимание, что Мастер функции в своем окне сразу же показывает результаты вычислений.
Зафиксируйте мышь на кнопке "ОК". Если формула введена правильно, на экране в ячейке G3 должно быть выведено число - минимальное из оценок.
Следует отметить, что это не единственный вариант ввода формул.
Пользователь имеет возможность лично напечатать формулу в соответствующей клетке: = мин(b3:f3). Но использование Мастера функций более наглядно для выбора имени функции и аргументов.
При вводе подобным образом в ячейку H3 максимального балла для первого студента, Мастер функций указал неверный диапазон оценок:B3:G3. В таком случае следует мышью выделить нужные клетки:
Скопируйте аналогичные формулы для остальных студентов.
Для выполнения расчетов, связанных с проверкой выполнения каких-либо условий, предназначена логическая функция ЕСЛИ.
Общий вид функции:
=ЕСЛИ (условие; выражение 1; выражение 2)
Если условие, записанное в формуле, выполняется, то в ячейку, содержащую формулу, помещается выражение 1, в противном случае - выражение 2.
В условии используются операции:
> - больше, < - меньше, >= - больше или равно, <= - меньше, = - равно, <> - не равно.
Числовые значения, текстовые константы и даты могут быть связаны любыми отношениями. Но результат таких операций всегда ИСТИНА или ЛОЖЬ.
На месте выражений можно записать числа, формулы, текстовые константы и другие логические операторы ЕСЛИ.
Рассмотрим варианты использования функции ЕСЛИ.
1. Для выявления студентов - двоечников, в ячейку I3 следует записать формулу:
=ЕСЛИ (G3=2;"двоечник";"успевает")
Для ввода формулы с использованием Мастера функций установите курсор в клетке I3 (i3).
В окне диалога в поле "Категория" из списка выберите "Логические", в поле "Функции" зафиксируйте "ЕСЛИ". Кнопка "ОК" выводит следующее окно мастера функций. В поле "логическое выражение" введите: G3=2 (ячейку G3 можно указать мышкой, можно просто напечатать), в следующем поле "значение если истина" - слово "двоечник", в последнем поле "значение если ложь" - слово "успевает":
Обратите внимание на строку ввода, Мастер функций расставил в формуле все имена, знаки и скобки. Зафиксируйте мышь на кнопке "ОК".
В зависимости от поставленных оценок на экран в ячейке I3 должны получить текст: "двоечник" или "успевает". Распространите эту формулу на остальные клетки.
2. Для разделения студентов на двоечников, отличников и остальных в ячейку J3 запишем новую формулу:
=ЕСЛИ (G3=2;"двоечник";ЕСЛИ(G3=5;"отличник";"успевает"))
Также используем кнопку Вставка функции.
В поле "Категория" можно отметить пункт "10 недавно использовавшихся", в окне "Функции" выбираем "ЕСЛИ".
Кнопка "ОК" выводит следующее окно мастера функций. В поле "логическое выражение" введите: G3=2, в поле "значение если истина" - слово "двоечник", в поле "значение если ложь" - щелкните по кнопке
в строке ввода, должно открыться меню:
В меню выбираем "ЕСЛИ", последовательно в полях записываем: G3=5, "отличник", "успевает". Вводим формулу кнопкой "ОК".
Распространите эту формулу на остальные клетки.
3. Разделим студентов на хорошистов и всех остальных (отличников, двоечников, просто успевающих).
Если необходимо выполнение двух или нескольких условий одновременно, то используют логическую операцию "И", если достаточно выполнения одного из любых условий, то указывают операцию "ИЛИ".
=ЕСЛИ(И(условие1; условие2; ...); выражение 1; выражение 2)
=ЕСЛИ(ИЛИ(условие1; условие2;...); выражение 1; выражение 2)
Введем новую формулу в ячейке K3.
=ЕСЛИ(И(G3=4;H3=5);"хорошист","......")
Также используем кнопку Вставка функции.
Выбираем "ЕСЛИ", фиксируем "ОК", фиксируем поле "логическое выражение". Щелкните по кнопке
в строке ввода таблицы, в меню выделяем
"Другие функции…", категория
"Логические", выбираем "И". В
поле "Логическое 1" заносим условие:
G3=4, в поле "Логическое 2" - H3=5, для
возвращения в предыдущее окно "Если"
щелкаем мышью в строке ввода за скобками:
В строку "Истина" заносим "хорошист", в строку "Ложь" заносим несколько точек (".....").
Щелкаем по кнопке "ОК" и распространяем эту формулу на остальные клетки.
Объединим все предыдущие условия, чтобы начислить стипендию.
Очистите колонки I, J, K.
Внесите в клетку I2 -"Стипендия", в пустую ячейку, например, А10, значение обычной стипендии - 166,98 р. (на сегодняшний день - 27 ноября 2000).
При копировании формулы адрес A10 должен быть постоянным для каждой клетки, поэтому задаем адрес A$10.
В ячейку I3 введите окончательную формулу:
=ЕСЛИ(G3=2;0;ЕСЛИ(G3=5;A$10+50%*A$10;ЕСЛИ(И(G3=4;H3=5); A$10+25%*A$10;A$10)))
Введите формулу с помощью Вставки функции или напечатайте сами, скопируйте ее для подсчета стипендии остальным студентам.
Вычислите средний балл по каждому предмету, среднюю минимальную и максимальную оценку, среднюю стипендию (функция СРЗНАЧ).
Установим график дежурства для студентов. В клетку J2 введите заголовок "Дежурство". В ячейку J3 с помощью Мастера функций введите функцию "Сегодня" из категории "Дата и время". Установите с помощью команд
Правка, Заполнить, Прогрессия
интервал дежурств в одну неделю.
Лично для себя вычислите количество прожитых дней на текущую дату.
В соответствии с личным вкусом оформите таблицу, используя все возможности программы (из пункта оформление ячеек таблицы).
Сохраните таблицу в своей папке.