 |
Математические и статистические функции
|
|
|
|
Лабораторная работа №2_E
Редактирование данных в Excel. Использование встроенных функций.
Цель работы:
· Изучить порядок открытия и закрытия файлов книг.
· Изучить способы редактирования данных.
· Изучить функции и порядок работы с ними.
Последовательность выполнения работы
1. Загрузите пакет Excel.
2. Откройте созданный вами в лабораторной работе № 1 документ (ЭТ).
3. Сделайте активным Лист2, переименуйте его на Редактирование и измените цвет ярлычка.
4. Создайте колонтитулы. Верхний колонтитул заполните по своему усмотрению. В нижнем колонтитуле в центре введите свою фамилию, инициалы и номер группы.
5. Создайте ЭТ из 10 строк с заголовком таблицы из двух строк (вместо Д1 написать название любой дисциплины) и заполните её.
6. Отформатируйте шапку таблицы (см. пункт 5): измените ширину столбцов, выполните Выравнивание по центру по горизонтали и по вертикали, объедините ячейки, измените шрифт, размер шрифта, начертание.
7. Удалите две любые строки, расположенные в разных местах ЭТ, выполнив команду Удалить только один раз.
8. Вставьте новый столбец № п/п перед столбцом ФИО.
8.1 Отформатируйте его заголовок: уменьшите ширину столбца, выполните перенос по словам, объединение ячеек с выравниванием содержимого по центру.
8.2 Заполните его с помощью автозаполнения:
- в ячейку под заголовком столбца ввести 1,
- в смежную нижнюю ячейку ввести 2,
- выделить обе заполненные ячейки,
- потянуть за маркер заполнения пока не появится цифра 8.
9. Вставьте шесть строк в середину ЭТ, выполнив команду Вставить только один раз.
10. Выполните перемещение 3 строк ЭТ.
11. Заполните пустые строки ЭТ, используя копирование и редактирование данных.
|
|
|
12. Отсортируйте по возрастанию данные столбца № п/п.
Для этого выделите данные ЭТ без заголовков столбцов. На вкладке Главная в группе инструментов Редактирование выберите Сортировка и фильтр, 
Сортировка от м инимального к максимальному.
13. Вставьте новый столбец Группа после столбца ФИО. Заполните его (группы должны повторяться).
14. Выполните поиск и замену одной группы на другую (например: заменить группу 111 на 112). Для этого на вкладке Главная в группе инструментов Редактирование выберите Найти и выделить, Заменить.
15. Выполните вычисления с помощью абсолютного адреса и встроенных функций: СУММ, МИН, МАКС, СРЗНАЧ, СУММЕСЛИ, СЧЕТЕСЛИ, ЕСЛИ (желательно при записи функции ЕСЛИ в логическом выражении использовать сложное условие с функциями И, ИЛИ).
Примечания: 1. Для функции ЕСЛИ создайте новый столбец.
2. Для всех остальных функций сначала формулируйте постановку, затем введите функцию.
3. После ввода всех функций копируйте формулы.
4. Смотрите Образец выполнения, но выполняйте свои задания по вариантам.
16. Просмотрите формулы на всем рабочем листе все сразу. Для этого на вкладке Формулы в группе инструментов Зависимости формул нужно щелкнуть кнопку 
.
17. Вернитесь к отображению в ячейках таблицы результатов вычислений.
18. Оформите лабораторную работу и подготовьтесь к её защите.
Варианты заданий
Для функции ЕСЛИ:
1. Фамилии студентов сдавших МК1.
2. Фамилии студентов сдавших МК2.
3. Фамилии студентов не сдавших МК2.
4. Фамилии студентов, начинающиеся на букву Т.
5. Фамилии студентов группы 111.
6. Фамилии студентов, у которых ПК1 не более 15.
7. Фамилии студентов, у которых ПК1 более 18.
8. Фамилии студентов, у которых ПК1 не менее 16.
9. Фамилии студентов, у которых ПК1 менее 17.
10. Фамилии студентов, у которых сумма баллов не более 80.
11. Фамилии студентов, у которых сумма баллов более 80.
|
|
|
12. Фамилии студентов, у которых сумма баллов не менее 70.
13. Фамилии студентов, у которых сумма баллов менее 70.
14. Номера групп студентов сдавших МК1.
15. Номера групп студентов не сдавших МК1.
16. Номера групп студентов не сдавших МК2.
17. Номера группы студентов, у которых сумма баллов более 90.
18. Номера группы студентов, у которых сумма баллов не более 90.
19. Номера группы студентов, у которых сумма баллов менее 75.
20. Номера группы студентов, у которых сумма баллов не менее 75.
21. Номера группы студентов, у которых сумма баллов более 75.
22. Номера группы студентов, у которых сумма баллов не более 75.
23. Номера группы студентов, у которых сумма баллов менее 66.
24. Номера группы студентов, у которых сумма баллов не менее 66.
25. Номера группы студентов, фамилии которых начинаются на букву А.
Для функции СУММЕСЛИ:
1. Общая сумма баллов по МК2 студентов 512 группы.
2. Общая сумма баллов по МК2 студентов сдавших МК1.
3. Общая сумма баллов по МК2 студентов не сдавших МК1.
4. Общая Сумма баллов студентов сдавших МК1.
5. Общая Сумма баллов студентов сдавших МК2.
6. Общая Сумма баллов студентов не сдавших МК2.
7. Общая Сумма баллов студентов, начинающиеся на букву Т.
8. Общая Сумма баллов студентов группы 111.
9. Общая Сумма баллов студентов, у которых ПК2 не более 17.
10. Общая Сумма баллов студентов, у которых ПК2 более 15.
11. Общая Сумма баллов студентов, у которых ПК2 не менее 19.
12. Общая Сумма баллов студентов, у которых ПК2 менее 18.
13. Общая Сумма баллов студентов, у которых Сумма баллов не более 75.
14. Общая Сумма баллов студентов, у которых Сумма баллов более 75.
15. Общая Сумма баллов студентов, у которых Сумма баллов не менее 90.
16. Общая Сумма баллов студентов, у которых Сумма баллов менее 90.
17. Общая сумма баллов по МК2 студентов, у которых сумма баллов по ПК2 более 15.
18. Общая сумма баллов по МК2 студентов, у которых сумма баллов по ПК2 не более 20.
19. Общая сумма баллов по МК1 студентов, у которых сумма баллов по ПК1 менее 17.
20. Общая сумма баллов по МК1, у которых сумма баллов по ПК1 не менее 16.
21. Общая сумма баллов по МК1, фамилии которых начинаются на букву А.
Для функции СЧЕТЕСЛИ:
1. Количество студентов группы 214.
2. Количество студентов сдавших МК1.
3. Количество студентов, фамилии которых начинаются на букву У.
|
|
|
4. Количество студентов, у которых сумма баллов не менее 80.
Образец выполнения
Содержание отчета
1. Номер лабораторной работы, её тема и цель.
2. Опишите операции, которые вы делали со своей электронной таблицей, и коротко опишите последовательность их выполнения.
5. Листинг ЭТ с заголовками строк и столбцов.
Контрольные вопросы
1. Как создать новую книгу?
2. Как открыть ранее созданный файл книги?
3. Как закрыть файл книги?
4. Как отменять выполненные действия?
5. Вставка и удаление данных.
6. Способы и последовательность редактирования данных.
7. Выделение смежных и несмежных ячеек, строк, столбцов, диапазонов на рабочем листе.
8. Способы копирования и перемещения данных рабочего листа.
9. Относительные и абсолютные ссылки.
10. Копирование и перемещение формулы.
11. Изменение масштаба отображения рабочего листа.
12. Назначение диалогового окна «Формат ячеек».
13. Форматирование содержимого ячеек.
14. Обрамление и затенение ячеек.
15. Вычисления с помощью встроенных функций: МАКС, МИН, СУММ, СРЗНАЧ, ЕСЛИ, СЧЁТЕСЛИ, СУММЕСЛИ.
16. Отображение формул на рабочем листе.
17. Способ заполнения ячеек – автозаполнение.
18. Сортировка данных ЭТ.
19. Поиск и замена данных в ЭТ.
ПРИЛОЖЕНИЕ
Математические и статистические функции
МИН, МАКС - возвращают минимальный/максимальный из списка аргументов.
Синтаксис: =МИН(число1; число2;...; число255)
=МАКС(число1; число2;...; число255)
СУММ - возвращает сумму аргументов.
Синтаксис: =СУММ(число1; число2;...; число255)
СРЗНАЧ - возвращает среднее арифметическое заданных чисел (сумма чисел, делённая на количество чисел).
Синтаксис: =СРЗНАЧ(число1; число2;...; число255)
СУММЕСЛИ - суммирует только те значения, которые соответствуют определённому критерию.
Синтаксис: =СУММЕСЛИ(диапазон; критерий [;диапазон_суммирования])
· диапазон: проверяемый диапазон, каждая ячейка из которого проверяется на соответствие условию, указанному во втором аргументе.
· критерий: условие для суммирования, на соответствие которому проверяется каждая ячейка из проверяемого диапазона. Если необходимо использовать операцию сравнения, то "логическое выражение" указывается без левого операнда и заключается в двойные кавычки (например, " >=35 " — суммировать все числа, большие 35). Также можно использовать текстовые значения (например, "ДИИТ" — суммировать все значения, находящиеся напротив текста "ДИИТ") и числовые (например, 800 — суммировать значения в ячейках, значения в которых 800).
|
|
|
· диапазон_суммирования: необязательный аргумент, используется тогда, когда проверяемый диапазон и диапазон суммирования находятся в разных диапазонах. Если он не указан, то в качестве диапазона суммирования используется проверяемый диапазон (первый аргумент). Если он указан, то суммируются значения из ячеек этого диапазона, находящиеся "напротив" соответствующих ячеек проверяемого диапазона.
СЧЁТЕСЛИ - подсчитывает количество ячеек, удовлетворяющих определённому условию.
Синтаксис: =СЧЁТЕСЛИ(диапазон; критерий)
· диапазон: проверяемый диапазон, каждая ячейка из которого проверяется на соответствие условию, указанному во втором аргументе. Из этого же диапазона происходит подсчёт количества ячеек.
· критерий: условие, на соответствие которому проверяется каждая ячейка из первого аргумента. Условие записывается аналогично функции СУММЕСЛИ.
Логические функции
И – проверяет, все ли аргументы имеют значение ИСТИНА и возвращает значение ИСТИНА, если истинны все аргументы.
Синтаксис: =И(логич_знач1; логич_знач2;...; логич_знач255)
ИЛИ – проверяет, имеет ли хотя бы один из аргументов значение ИСТИНА и возвращает значение ИСТИНА или ЛОЖЬ. Значение ЛОЖЬ возвращается только в том случае, если все аргументы имеют значение ЛОЖЬ.
Синтаксис: =ИЛИ(логич_знач1; логич_знач2;...; логич_знач255)
Примечание: Функции И, ИЛИ обычно используются в качестве аргумента других функций, например, ЕСЛИ.
ЕСЛИ – проверяет условие и возвращает одно значение, если условие выполняется (истинно) или другое значение, если условие не выполняется (ложно).
Синтаксис:
=ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь).
Текстовая функция
ЛЕВСИМВ(текст [,число_знаков]) – возвращает первый символ или несколько первых символов текстовой строки на основе заданного числа символов.
Синтаксис: =ЛЕВСИМВ(текст [,число_знаков])
· текст: текстовая строка, содержащая символы, которые требуется извлечь.
· число_знаков: необязательный аргумент - количество символов, извлекаемых функцией ЛЕВСИМВ.
· если значение "число_знаков" опущено, оно считается равным 1.
|
|
|
|
|
|
