Цель работы:
- Научиться вводить данные в электронные таблицы.
- Научиться форматировать ячейки таблицы.
- Научиться работать со встроенными функциями Microsoft Excel 2010.
Теоретический материал
Интерфейс Excel 2010. Стартовое окно программы содержит три пустых листа рабочей книги. Вверху находится лента с инструментами вкладок: Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид.
Для вызова полного набора инструментов той или иной панели надо открыть окно данной панели, нажав на стрелочку в правом нижнем углу.
При этом откроется соответствующее окно с инструментами.
Пользователь, используя команды вкладки Главная, в документе Microsoft Excel 2010 может отформатировать ячейки и документ.
Формат ячейки включает в себя параметры, которые определяют:
- вид представления числового значения ячейки (Главная/ Число или в контекстном меню Формат ячейки/ Число);
- выравнивание и ориентацию содержимого ячейки (Главная/ Выравнивание или Формат ячейки/ Выравнивание);
- вид шрифта (Главная/ Шрифт или Формат ячейки/ Шрифт);
- обрамление ячейки (Главная/ Шрифт или Формат ячейки/ Граница);
- Заполнение (фон) ячейки (Главная/ Шрифт или Формат ячейки/ Заливка).
Кроме этого, можно установить параметры защиты данных, записанных в ячейку (Главная/ Ячейки/ Формат/ Защита или Формат ячейки/ Защита). Так же на вкладке Главная можно задать стиль ячеек или таблицы (Главная/ Стили).
Команды группы Ячейки позволяют Вставить или Удалить ячейки, строки и столбцы. С помощью команды Формат задать Размер, Видимость строки или столбца, Упорядочить листы и установить Защиту.
Обычно ячейки форматируются путем изменения отдельных параметров. Перед тем как изменить формат ячейки, необходимо установить на нее табличный курсор. Если же требуется установить формат для нескольких ячеек одновременно, следует предварительно выделить эти ячейки.
Ввод данных в ячейки Excel
В ячейки листа Excel можно вводить – числовые данные, текстовые данные и формулы. Кроме того, есть возможность ввести логические и ошибочные значения, значения даты и времени.
После ввода цифровых и текстовых данных нет необходимости нажимать клавишу Enter. Для перехода к следующей ячейке достаточно воспользоваться клавишами со стрелками или клавишей Tab.
Если необходимо начать ввод данных в текущей ячейке с новой строки, перейдите в режим редактирования ячейки и нажмите клавиши Alt+Enter.
По умолчанию текст в ячейках выравнивается по левому краю, а числа - по правому. При необходимости форматирование можно провести вручную.
Формулой в Excel называется последовательность символов, начинающаяся со знака равенства “=”. В эту последовательность символов могут входить постоянные значения, ссылки на ячейки, имена, функции или операторы. Результатом работы формулы является новое значение, которое выводится как результат вычисления формулы по уже имеющимся данным.
Если значения в ячейках, на которые есть ссылки в формулах, меняются, то результат изменится автоматически.
После того как формула введена в ячейку, вы можете ее перенести, скопировать или распространить на блок ячеек.
При перемещении формулы в новое место таблицы ссылки в формуле не изменяются, а ячейка, где раньше была формула, становится свободной. При копировании формула перемещается в другое место таблицы, ссылки изменяются, но ячейка, где раньше находилась формула, остается без изменения. Формулу можно распространить на блок ячеек.
При копировании формул возникает необходимость управлять изменением адресов ячеек или ссылок. Для этого перед символами адреса ячейки или ссылки устанавливаются символы “$”. Изменяться только те атрибуты адреса ячейки, перед которыми не стоит символ “$”. Если перед всеми атрибутами адреса ячейки поставить символ “$”, то при копировании формулы ссылка не изменится.
Если в ссылке используются символы $, то она называется абсолютной, если символов $ в ссылке нет — относительной. Адреса таких ссылок называются абсолютными и относительными, соответственно.
Функция Excel - это заранее определенная формула, которая работает с одним или несколькими значениями и возвращает результат.
Функции в Excel используются для выполнения стандартных вычислений в рабочих книгах. Значения, которые используются для вычисления функций, называются аргументами. Значения, возвращаемые функциями в качестве ответа, называются результатами. Помимо встроенных функций вы можете использовать в вычислениях пользовательские функции, которые создаются при помощи средств Excel.
Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой “;”.
Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы.
В качестве аргументов можно использовать числа, текст, логические значения, массивы, значения ошибок или ссылки. Аргументы могут быть как константами, так и формулами.
Для удобства работы функции в Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок. Кроме того, присутствуют следующие категории функций: статистические, текстовые и математические.
При помощи текстовых функций имеется возможность обрабатывать текст: извлекать символы, находить нужные, записывать символы в строго определенное место текста и многое другое.
С помощью функций даты и времени можно решить практически любые задачи, связанные с учетом даты или времени (например, определить возраст, вычислить стаж работы, определить число рабочих дней на любом промежутке времени).
Логические функции помогают создавать сложные формулы, которые в зависимости от выполнения тех или иных условий будут совершать различные виды обработки данных.
В Excel широко представлены математические функции. Например, можно выполнять различные операции с матрицами: умножать, находить обратную, транспонировать.
С помощью статистических функций возможно проводить статистическое моделирование. Кроме того, возможно использовать элементы факторного и регрессионного анализа.
Т.к. функция суммы используется наиболее часто, то на вкладке Главная вынесена кнопка Автосумма 
. Кнопка Автосумма снабжена выпадающим списком, из которого можно выбрать другую формулу для ячейки.
Условная функция. Общий вид условной функции следующий:
ЕСЛИ(<условие>; <выражение1>; <выражение2>)
Условие — это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ. <выражение 1> и <выражение 2> могут быть числами, формулами или текстами.
Условная функция, записанная в ячейку таблицы, выполняется так: если условие истинно, то значение данной ячейки определит <выражение 1>, в противном случае — <выражение 2>.
Логические выражения. Логические выражения строятся с помощью операций отношения (<, >, <= (меньше или равно), >= (больше или рано), =, <> (не равно)) и логических операций (логическое И, логическое ИЛИ, логическое отрицание НЕ). Результатом вычисления логического выражения являются логические величины ИСТИНА или ЛОЖЬ.
Существуют особенности записи логических операций в табличных процессорах: сначала записывается имя логической операции (И, ИЛИ, НЕ), а затем в круглых скобках перечисляются логические операнды.
И (логическое_значение1; |
Возвращает значение ИСТИНА, если все аргументы |
Логическое_значение2; …) |
имеют значение ИСТИНА; возвращает значение (AND) ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ; например, И(2+2=4;2+3=5) равняется ИСТИНА, если ячейка B4 содержит число между 1 и 100, то И(1<B4; B4<100) равняется ИСТИНА |
ИЛИ(логическое_значение1; |
Возвращает значение ИСТИНА, если хотя бы один |
Логическое_значение2; …) |
аргументов имеет значение ИСТИНА ивозвращает |
|
(OR) значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ; например, ИЛИ(2+2=4; 2+3=6) возвращает ИСТИНА; если ячейка B4 содержит число меньше 1 или больше 100, то ИЛИ(1<B4; B4>100) возвращает ИСТИНА. НЕ (логическое_зачение) (NOT) Меняет на противоположное логическоезначение своего аргумента; например НЕ(2+2=5) возвращает ИСТИНА; если ячейка B4 содержит число меньше 1 или больше 100, то НЕ(ИЛИ(1<B4; B4>100)) возвращает ЛОЖЬ |
Вложенные логические функции ЕСЛИ.
Формат записи:
= ЕСЛИ(усл.1; выражение В; ЕСЛИ(усл.2; выражение С; ЕСЛИ(…))).
Число вложенных функций в принципе может быть любое, но общая длина строки не должна быть слишком большой.
Контрольные вопросы:
1. Как выполняется объединение ячеек?
2. Какие встроенные функции Microsoft Excel 2010 вы знаете?
3. Как работает Мастер функций?
4. Для чего используются функции МИН, МАКС, СРЗНАЧ?
5. В каких случаях применется условная функция ЕСЛИ?
6. Какие наиболее часто используемые функции можно выбрать с помощью команды Автосумма?
Практическая работа
Задание 1. Создать таблицу «Зачетная ведомость» и произвести простейшие статистические вычисления.
Методические указания:
1. Переименуйте рабочий лист книги Excel из Лист 1 в Зачет, дважды щелкнув мышью по названию листа: название листа находится на вкладке в нижней части окна Excel.
2. Наберите название таблицы:
- выделите ячейки с A1 по K1 (A1:K1);
- выберите Контекстное меню/ Формат ячеек/ Выравнивание/ Объединить ячейки или выполните команду Главная/ Выравнивание/ Объединить и поместить в центре
; - наберите текст названия таблицы;
- установите размер шрифта 12, полужирное начертание.
3. Создайте таблицу с данными:
- наберите в ячейке B4 Тема, а в A5 – Фамилия;
- выделите ячейки A4:B5;
- с помощью кнопки установите границу вида (Внешние границы) для группы ячеек;
- удерживая клавишу Ctrl, последовательно выделите мышью ячейки A4 и B5;
- выберите Контекстное меню/ Формат ячеек/ Граница и добавьте вид наклонной границы;
- объедините ячейки C4:C5;
- на вкладке Выравнивание окна формата ячеек установите флажок Переносить по словам или выполните команду Главная/ Выравнивание/ Перенос текста;
- протяните формат этой ячейки до столбца K: возьмитесь мышкой за правый нижний угол объединенной ячейки (при этом курсор изображается в виде черного крестика.) / двигайте мышь вправо (удерживая нажатой левую кнопку), пока выделение не достигнет столбца K, отпустите кнопку мыши;
- введите заголовки столбцов полужирным шрифтом;
- для поворота текста на 90 градусов в столбцах I,J и K в окне формата ячеек воспользуйтесь опцией Ориентация;
- объедините ячейки A6:B6;
- протяните формат ячеек до строки 15;
- введите заголовки строк;
- введите в ячейки C6:H11 оценки учеников по темам. Ячейки с минимальными, максимальными и средними значениями будут вычисляться по формулам и вручную их заполнять не следует;
- для очерчивания границ ячеек выделите их и воспользуйтесь кнопкой
и с вариантом 
(все границы).
4. Вычислите лучшую оценку у ученика Абрамова К.:
- установите курсор в ячейку I6;
- нажмите на кнопку
рядом со строкой формулы, вызовите Мастер функций/ Функция/ Категория - Статистические/ МАКС; - в строку Число1 введите диапазон ячеек C6:H6 (эти ячейки можно выделить мышью) и нажмите Ок. В ячейке появится найденное значение;
- протяните полученное значение на все остальные ячейки. В каждой строке значение вычисляется исходя из тех данных, которые в ней находятся;
- аналогичным образом найдите лучшую оценку и средний балл с помощью функций МИН и СРЗНАЧ соответственно.
5. Вычислите лучшую, худшую оценки и средний балл по каждой теме в строках 13, 14 и 15, используя команду Главная/ Редактирование/ Автосумма.
6. Найдите лучшую, худшую оценки и средний балл по всем значениям таблицы в ячейках I13, J14, K15, пользуясь данными строк или столбцов лучших, худших и средних результатов.
Задание 2. Создать таблицу «Зачетная ведомость» с использованием условной функции ЕСЛИ.
Методические указания
- Лист 2 ЭТ переименуйте в Допуск.
- Введите данные как показано на слайде.
- В ячейку Е2 введите формулу вычисления среднего значения =СРЗНАЧ(B2:D2)
- Скопируйте эту формулу в ячейки Е3, Е4, Е5.
- К полученным результатам примените форматирование ячеек.
- В ячейку F2 необходимо ввести формулу =ЕСЛИ(E2>=3;"Зачет";ЕСЛИ(E2<3;"Незачет")) и скопировать ее в ячейки F3, F4, F5.
- Сохраните документ.