рефераты конспекты курсовые дипломные лекции шпоры
- Раздел Информатика
- /
- Методические указания по выполнению лабораторных и самостоятельных работ – «Информатика и вычислительная техника»
Реферат Курсовая Конспект
Методические указания по выполнению лабораторных и самостоятельных работ – «Информатика и вычислительная техника»
Методические указания по выполнению лабораторных и самостоятельных работ – «Информатика и вычислительная техника» - раздел Информатика, Министерство Образования И Науки Российской Федерации Федеральное Го...
Министерство образования и науки Российской Федерации
Федеральное государственное бюджетное образовательное учреждение
высшего профессионального образования
«Пермский национальный исследовательский политехнический университет»
Березниковский филиал
С. А. Варламова
Microsoft Excel 2007
Методические указания по выполнению
лабораторных и самостоятельных работ
студентами всех форм обучения направления
230100.62 – «Информатика и вычислительная техника»
Издательство
Пермский национальный исследовательский политехнический университет
2012
Составитель – кандидат технических наук, доц. С.А. Варламова
УДК 683.3
В 18
Рецензент:
кандидат технических наук, доцент Ерыпалова М.Н.
(Березниковский филиал
Пермского национального исследовательского
политехнического университета)
Microsoft Excel 2007: метод. указания по выполнению лабораторных и самостоятельных работ. / сост. С.А. Варламова. Березниковский филиал ПНИПУ – Пермь: Изд-во Перм. нац. исслед. политехн. ун-та. – 2012.
Рассмотрены основные принципы работы в табличном процессоре Microsoft Excel, весь теоретический материал разбит на 8 лабораторных работ, к каждой из которых предложено 10 вариантов индивидуальных заданий. Каждая лабораторная работа сопровождается необходимыми теоретическими сведениями и контрольными вопросами для закрепления материала.
Предназначено для студентов всех форм обучения направления 230100 – «Информатика и вычислительная техника»
© ПНИПУ, 2012
Оглавление
Введение. 6
Лабораторная работа 1. Введение в табличный процессор Excel 7
1.1. Теоретические сведения. 7
1.1.1. Общий вид экрана. 7
1.1.2. Создание, открытие и сохранение рабочих книг. 9
1.1.3. Листы рабочей книги, ячейка и адрес ячейки. 10
1.1.4. Перемещения по экрану. 11
1.1.5. Выделение фрагментов таблицы.. 11
1.1.6. Контроль вводимых данных. 12
1.1.7. Работа с формулами. 15
1.1.8. Адреса ячеек. 16
1.1.9. Распространение формул. 17
1.1.10. Визуализация зависимостей и примечания. 18
1.1.11. Форматирование таблиц. 18
1.2. Задание к работе. 19
1.2.1. Варианты индивидуальных заданий. 20
1.3. Контрольные вопросы.. 23
Лабораторная работа 2. Графики и диаграммы.. 24
2.1. Теоретические сведения. 24
2.1.1. Общие сведения о диаграммах. 24
2.1.2. Создание диаграммы.. 26
2.1.3. Изменение стиля или макета диаграммы.. 27
2.1.4. Добавление и удаление названий или меток данных. 29
2.1.5. Обновление и изменение данных на диаграмме. 31
2.1.6. Создание смешанной диаграммы.. 34
2.2. Задание к работе. 34
2.3. Контрольные вопросы. 35
Лабораторная работа 3. Функции Excel 36
3.1. Теоретические сведения. 36
3.1.1. Средства автоматизации ввода данных в Excel 2007. 36
3.1.2. Встроенные функции Excel 38
3.1.3. Статистический анализ. 40
3.2. Задание к работе. 42
3.2.1. Варианты индивидуальных заданий. 43
3.3. Контрольные вопросы. 43
Лабораторная работа 4. Средства работы с книгами. 45
4.1. Теоретические сведения. 45
4.1.1. Работа с листами книги. 45
4.1.2. Средства защиты книг. 46
4.2. Задание к работе. 50
4.3. Контрольные вопросы. 50
Лабораторная работа 5. Фильтры.. 51
5.1. Теоретические сведения. 51
5.1.1. Сортировка списков. 51
5.1.2. Фильтрация списков. 53
5.2. Задание к работе. 57
5.2.1. Варианты индивидуальных заданий. 58
5.3. Контрольные вопросы. 67
Лабораторная работа 6. Формы и автоматическое подведение итогов. 69
6.1. Теоретические сведения. 69
6.1.1. Форма. 69
6.1.2. Промежуточные итоги. 70
6.2. Задание к работе. 73
6.2.1. Варианты индивидуальных заданий. 73
6.3. Контрольные вопросы. 75
Лабораторная работа 7. Консолидация данных. 76
7.1. Теоретические сведения. 76
7.1.1. Консолидация данных на основе трехмерных ссылок. 76
7.1.2. Консолидация данных по расположению.. 77
7.1.3. Консолидация данных по категориям. 77
7.1.4. Изменение итоговой таблицы консолидации данных. 78
7.2. Задание к работе. 79
7.2.1. Варианты индивидуальных заданий. 80
7.3. Контрольные вопросы. 84
Лабораторная работа 8. Сводные таблицы.. 85
8.1. Теоретические сведения. 85
8.1.1. Создание сводной таблицы.. 85
8.1.2. Модификация сводной таблицы.. 89
8.2. Задание к работе. 90
8.2.1. Варианты индивидуальных заданий. 90
8.3. Контрольные вопросы. 93
Лабораторная работа 9. Составление условий в электронных таблицах, условное форматирование 94
9.1. Теоретические сведения. 94
9.1.1. Условное форматирование. 94
9.1.2. Функция ЕСЛИ.. 96
9.2. Задание к работе. 98
9.2.1. Варианты индивидуальных заданий. 99
Лабораторная работа 10. Текстовые функции, функции даты и времени. . 101
10.1................................................................................... Теоретические сведения. 101
Введение
Microsoft Excel – ведущая программа обработки электронных таблиц. Электронные таблицы позволяют производить обработку чисел и текста, задавать формулы и функции для автоматического выполнения, представлять данные в виде диаграмм, публиковать рабочие листы и диаграммы в Интернете.
Популярность табличного процессора MS Excel позволяет предположить, что интерес к нему будет расти и дальше. Поэтому рассмотрение тех или иных задач, которые можно решить с использованием его возможностей, будет расширять области применения MS Excel как в учебном процессе, так и для автоматизации многих расчетов в производственной сфере.
В методических рекомендациях на различных примерах продемонстрированы возможности MS Excel для решения математических задач. MS Excel используется здесь как средство для проведения расчетов, визуализации их в виде таблиц, графиков, сводных таблиц и др. Представленные в указаниях индивидуальные задания предназначены для углубленного освоения возможностей этого табличного процессора.
Лабораторная работа 1. Введение в табличный процессор Excel
Цель работы: ознакомление с основными элементами табличного процессора Excel и подготовка простейших таблиц.
Теоретические сведения
Общий вид экрана
Рис. 1. Окно табличного процессора MS Excel 2007Создание, открытие и сохранение рабочих книг
Для создания новой книги, когда Excel уже запущен, нужно щелкнуть по кнопке <Office> и выбрать пункт меню «Создать». В появившемся диалоговом… Чтобы открыть существующий файл, воспользуйтесь командой меню «Открыть»кнопки… Чтобы сохранить новый документ или внесенные изменения, воспользуйтесь, соответственно, командами меню «Сохранить…Листы рабочей книги, ячейка и адрес ячейки
Чтобы отобразить большее или меньшее число ярлычков листов, подведите указатель мыши к области разделения ярлычков листов с горизонтальной полосой…Перемещения по экрану
Стрелки вверх, вниз, вправо, влево дают перемещение на одну ячейку в соответствующем направлении. Клавиша [Home] – в первый столбец строки. Сочетание клавиш [Ctrl]+[Home] и [Ctrl]+[End] – соответственно, в начало таблицы и в последнюю использованную…Выделение фрагментов таблицы
Выделение строк(и) – щелчком мыши по номеру строки, либо нажать [Shift]+[пробел], затем [F8] и стрелку, показывающую направление выделения, выделив… Выделение прямоугольного фрагмента – установить курсор мыши в один из углов… Выделение нескольких прямоугольных фрагментов – выделить одну область, как сказано выше, нажать [Ctrl] и, держа ее…Контроль вводимых данных
Размер шрифта вводимых данных устанавливается через опции вкладки Данныегруппа Шрифт. Если же необходимо задать новый шрифт, который будет… Вводимые в таблицу данные можно проверять на корректность значений. Для этого… Окно «Проверка вводимых значений» содержит 3 вкладки: параметры, сообщение для ввода, сообщение об ошибке.Работа с формулами
До фиксации ввода формула отображается в ячейке и в строке формул. После нажатия [Enter] в ячейке появится вычисленное по формуле значение, а строка… При вычислении значения по формуле в первую очередь вычисляются выражения… 1. вычисляются значения функций;Адреса ячеек
Относительная ссылка воспринимается программой как указание маршрута к адресуемой ячейке от ячейки, содержащей формулу. При копировании формулы… Абсолютная ссылка задает абсолютные координаты ячейки. При копировании формулы… Смешанная ссылка представляет собой комбинацию абсолютной и относительной ссылок, когда для строки и столбца…Распространение формул
- Установите курсор в ячейку с формулой; - Подведите указатель мыши к маркеру заполнения. Изображение указателя… - Нажмите левую кнопку мыши и, удерживая ее нажатой, перемещайте курсор до нужного места. Для завершения…Визуализация зависимостей и примечания
Можно просмотреть на экране установленные аналитические зависимости между ячейками. Для этого необходимо задать опции на вкладке Формулы в группеЗависимости формул:
- Влияющие ячейки: при выборе этого пункта меню на выбранную ячейку указывают стрелки от ячеек, из которых берет данные указанная ячейка.
- Зависимые ячейки: при выборе этого пункта меню стрелка указывает на ячейки, которые берут из выбранной ячейки данные.
- Проверка наличия ошибок: при выборе этого пункта меню можно узнать источник ошибки в формуле. Например, в ячейке вычисляется сумма двух ячеек. Но при вычислении значения одной из ячеек-источников произошла ошибка, например, деление на ноль. Ошибку такого типа поможет выявить этот пункт меня, указав красной стрелкой источник ошибки.
- Убрать все стрелки: убирает все стрелки с листа.
- Окно контроля значений: выводит окошко, опции которого достаточно понятны и не требуют комментариев.
Форматирование таблиц
Высота строк и ширина столбцов таблицы регулируется с помощью одноименных команд меню «Формат»группыЯчейкивкладкиГлавная. Для изменения ширины…Задание к работе
1. Изучить самостоятельно группу Параметры страницы на вкладкеРазметка страницы. Установить размер бумаги – А4, горизонтальное и вертикальное центрирование, в верхнем колонтитуле – свою фамилию (по центру), в нижнем – номер группы (по центру) и номер страницы (слева).
2. Установить размер шрифта 14 единиц.
3. Разработать таблицу согласно варианту задания. Задайте условия для проверки вводимых данных. Предусмотреть контекстную диагностику при вводе некорректных данных.
4. Заполните таблицы условными данными.
5. Изучите различные способы ввода формул.
6. Изучите способы выделения, копирования и переноса ячеек, строк и столбцов. Получите копию созданной таблицы, скопировав ее по частям.
7. Поясните, как и почему изменились формулы в скопированной таблице.
8. Отформатируйте исходную таблицу «вручную», используя:
- форматы данных (числовой, денежный, процент, дата и т.д.);
- обрамление и заполнение ячеек;
- различные форматы шрифтов;
- выравнивание абзацев;
- изменение высоты строк и ширины столбцов.
9. Отформатируйте вторую таблицу, используя команду «Автоформат».
10. Подготовьте к печати одностраничный отчет.
11. Сохранить лабораторную работу (этот файл будет использоваться в дальнейшем).
Варианты индивидуальных заданий
Вариант 1.
Сравнительная таблица розничных цен на продовольственные товары по городам Пермского края на “__” ____201__г. (руб. за 1 кг).
| Наименование товара | Пермь | Березники | Соликамск | Чусовой | Краснокамск | Средняя цена |
| Указать не менее 7 наименований | ||||||
| ВСЕГО |
Вариант 2.
Данные о продаже автомобилей в 201__г.
| Марка | 1 квартал | % | 2 квартал | % | 3 квартал | % | 4 квартал | % |
| Указать не менее 7 наименований | ||||||||
| ВСЕГО |
Вариант 3.
Температура воздуха в городах мира с 1 по 7 января 201__г.
| Дата | Москва | Рим | Париж | Мадрид |
| Ср. темп. | ||||
| Средняя температура по всем городам: |
Вариант 4.
Итоговые экспертные оценки кандидатов на должность.
| № кандидата | Эксперты | ||||||
| Сумма | % | ||||||
| Указать не менее 7 кандидатов | |||||||
| Всего: |
Вариант 5.
Доходы/расходы за полугодие 201__г.
| Месяцы | ||||||
| Доходы | январь | февраль | март | май | июнь | июль |
| Оклад | ||||||
| Премия(20%) | ||||||
| Совместит. | ||||||
| Предприним. | ||||||
| ВСЕГО: | ||||||
| Расходы | ||||||
| Жилье | ||||||
| Кредит | ||||||
| Питание | ||||||
| Др. расходы | ||||||
| ВСЕГО: | ||||||
| ОСТАТОК: | ||||||
| Итого остаток за полугодие: |
Вариант 6.
Выручка от продажи книжной продукции в 201__г.
| Название книги | 1 квартал | 2 квартал | 3 квартал | 4 квартал | % от объема за год |
| Указать не менее 7 наименований | |||||
| Всего за год: |
Вариант 7.
Прайс-лист фирмы ___________ на” __” ___201__г.
| Наименование товара | Розничная цена | От 10 шт. | От 1000 шт. | Свыше 1000 шт. | Дилеры |
| Указать не менее 7 наименований |
Примечание: цена в колонках 3-6 уменьшается на 2% по сравнению с предыдущей колонкой.
Вариант 8.
Результат зимней сессии студентов группы ______________
| Ф.И.О | Название предмета | ||||
| Мат. анализ | Информатика | Алгебра и геометрия | Программирование | Сумма | |
| Указать не менее 7 студентов | |||||
| Сред. оценка |
Вариант 9.
Зарплатная ведомость за ноябрь
| ФИО | Оклад | Премия 30% | Подоходный налог, 13% | Налог в пенсионный фонд, 1% | К выдаче |
| Указать не менее 7 работников |
Вариант 10.
Бланк накладной
" " ____________ 200__ г.
Накладная N_____
Кому: ______________________________
От кого: ______________________________
| N п/п | Наименование | Кол-во | Цена | Сумма |
| Указать не менее 7 наименований |
Итого: __________
Контрольные вопросы
1. Каким образом можно задать количество листов во вновь создаваемой книге?
2. Как отобразить/убрать координатную сетку рабочего листа Excel?
3. Какие вы знаете сочетания клавиш для перемещения по ячейкам электронной таблицы?
4. Как выделить ячейку, блок ячеек, строку, столбец таблицы?
5. Каким образом можно проверять данные на корректность ввода?
6. Какие вкладки содержит окно проверки?
7. Каков порядок действий при вычислении формулы в Excel?
8. Каким знаком аргументы отделяются друг от друга при использовании функций?
9. Как отобразить существующие зависимости и примечания?
10. Как скопировать данные таблицы с одного рабочего листа на другой?
11. Как присвоить формат ячейке? Приведите примеры разных форматов?
12. Каковы правила ввода и редактирования формул в Excel?
13. Что такое относительная и абсолютная адресация?
14. Как отформатировать таблицу в Exсel?
Лабораторная работа 2. Графики и диаграммы
Цель работы: изучение средств графического представления данных.
Теоретические сведения
Общие сведения о диаграммах
Для большей наглядности числовые данные иногда лучше представлять в графическом виде. Это позволяет решать самые разнообразные задачи. На диаграммах легко просматривается тенденция к изменению, при этом можно определять скорость изменения этой тенденции. Различные соотношения, прирост, взаимосвязь процессов – все это легко можно увидеть на диаграммах.
Диаграмма – это графическое представление числовых данных. В Excel используется ряд основных понятий и определений, которые необходимо знать для правильного и эффективного построения диаграмм.
Ряды данных – это наборы значений, которые требуется изобразить на диаграмме. Например, при построении диаграммы дохода компании за последнее десятилетие рядом данных является набор значений дохода за каждый год. Математический аналог рядов данных – это значения функции (Y).
Категории служат для упорядочения знаний в рядах данных (в нашем примере – это годы). Математический аналог категорий – это аргумент функции (X).
Легенда – это условные обозначения значений различных рядов данных на диаграмме.
В Excel имеется 9 типов двумерных и 6 типов 3-х мерных диаграмм, каждая из которых имеет несколько разновидностей. Рассмотрим некоторые из них.
1. В диаграмме с областями отдельные ряды данных представлены в виде закрашенных разными цветами областей.
2. При использовании линейчатой диаграммы отдельные значения будут представлены полосами различной длинны, расположенными горизонтально вдоль оси X. Длина полосы соответствует величине числового значения.
3. Линейчатая диаграмма, в которой отдельные значения представлены вертикальными столбиками различной длинны, называется гистограммой. Для всех трех указанных выше типов диаграмм при выборе соответствующего формата каждый ряд значений можно представить как долю от общей суммы.
4. На графике все отдельные значения будут соединены между собой линиями, полученными путем интерполяции. График не стоит использовать, если отдельные абсолютные значения не взаимосвязаны.
5. При использовании круговой диаграммы сумма всех значений принимается за 100%, а процентное соотношение величин изображается в виде круга, разбитого на несколько секторов разного цвета. В круговой диаграмме допускается только один ряд данных.
6. Кольцевая диаграмма – это особый вид круговой диаграммы. В этом случае сумма всех значений также принимается за 100%, а ряды данных представляют собой вложенные кольца, разделенные на сегменты в процентном отношении. Преимущество кольцевой диаграммы перед круговой состоит в возможности одновременного изображения нескольких рядов данных.
7. Радар – это диаграмма, в которой категории представляются лучами, расходящимися из одной точки. Каждое значение ряда данных будет выделено на оси и соединено с другими замкнутой линией.
8. На точечной диаграмме отдельные значения таблицы представляются точками в декартовой системе координат, которые не соединены линиями. Этот вид диаграммы больше всего подходит для представления независимых отдельных значений.
9. При создании объемных диаграмм с областями используется система координат с тремя осями. Объемная гистограмма, объемная линейчатая и объемная круговая диаграммы позволяют достичь эффекта пространственного представления данных. Объемный (трехмерный) график приобретает форму лент. Принцип построения объемных поверхностей диаграмм напоминает применяемый в картографии способ представления на физических картах высоты участка местности над уровнем моря.
Любая диаграмма состоит из множества элементов. Некоторые из них отображаются по умолчанию, а другие можно добавлять при необходимости. Отображение элементов диаграммы можно изменить путем их перемещения в другое место диаграммы, изменения их размера или формата. Кроме того, ненужные элементы можно удалить (рис. 5).
| |
| |
| |
| |
| |
| |
| |
Рис. 5. Элементы диаграммы. (1 – область диаграммы,
2 – область построения, 3 – элементы данных, 4 – оси,
5 – легенда, 6 – название диаграммы, 7 – название осей)
Создание диаграммы
1. Упорядочьте данные на листе, которые будут использоваться для построения диаграммы. 2. Выделите ячейки, содержащие данные для построения диаграммы. 3. В группе Диаграммы на вкладке Вставка выполните одно из следующих действий:Изменение стиля или макета диаграммы
После создания диаграммы ее внешний вид можно быстро изменить. Вместо добавления либо изменения элементов диаграммы или ее форматирования вручную можно применить готовые макет и стиль диаграммы. В приложении Excel содержится множество полезных готовых макетов и стилей, которые можно использовать. Кроме того, можно настроить макет или стиль в соответствии со своими требованиями, вручную изменив макет и форматирование отдельных элементов диаграммы.
Применение готового макета диаграммы
Выберите диаграмму, к которой необходимо применить готовый макет. На вкладке Конструктор в группе Макеты диаграммы выберите нужный макет диаграммы (см. рис. 6, рамка 3).
Применение готового стиля диаграммы
Выберите диаграмму, к которой необходимо применить готовый стиль. На вкладке Конструктор в группе Стили диаграмм выберите стиль, который необходимо применить (см. рис. 6, рамка 4).
Изменение макетов элементов диаграммы вручную
- щелкните в любом месте диаграммы, чтобы отобразить средства для работы с диаграммами; - на вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем…Изменение формата элементов диаграммы вручную
- чтобы изменить формат выбранного элемента диаграммы, в группе Текущий фрагмент выберите элемент «Формат выделенного фрагмента», а затем выберите… - чтобы изменить формат фигуры выбранного элемента диаграммы, в группе Стили… - чтобы изменить формат текста выбранного элемента диаграммы с помощью WordArt, в группе Стили WordArt выберите…Добавление и удаление названий или меток данных
Чтобы быстро различать ряды данных на диаграмме, можно добавить метки данных к точкам данных на диаграмме. По умолчанию метки данных связаны со…Добавление названия диаграммы
Выберите диаграмму, которой необходимо присвоить название. Откроется панель «Работа с диаграммами». На вкладке Макет в группе Подписи выберите элемент «Название диаграммы». Выберите место расположения названия диаграммы либо «Название по центру с перекрытием», либо «Над диаграммой». В появившемся текстовом поле «Название диаграммы» введите необходимый текст.
Добавление названий осей
- чтобы добавить название для основной горизонтальной оси (оси категорий), выберите элемент «Название основной горизонтальной оси», а затем –… - чтобы добавить название для основной вертикальной оси (оси значений),… - чтобы добавить название для оси глубины (оси рядов), выберите элемент «Название оси глубины», а затем – необходимый…Связывание названия с ячейкой листа
Выберите название диаграммы или оси, которое необходимо связать с ячейкой листа. В строке формул на листе введите знак равенства «=». Выберите на листе ячейку, содержащую данные или текст, которые необходимо показать на диаграмме.
Кроме того, в строке формул можно ввести ссылку на ячейку листа. Введите знак равенства, затем – название листа и восклицательный знак, например: =Лист1!F2. Нажмите клавишу [Enter].
Добавление меток данных
- чтобы добавить метки данных ко всем точкам данных из всех рядов данных, щелкните область диаграммы; - чтобы добавить метки данных ко всем точкам данных определенного ряда… - чтобы добавить метки данных к определенной точке данных из ряда данных, щелкните ряд данных, в котором находится…Удаление названий или меток данных с диаграммы
- чтобы удалить название диаграммы, выберите элемент «Название диаграммы», а затем – параметр «Нет»; - чтобы удалить названия осей, выберите элемент «Название оси», щелкните тип… - чтобы удалить метки данных, выберите элемент «Метки данных», а затем – параметр «Нет».Обновление и изменение данных на диаграмме.
Обновление диаграммы с использованием измененных значений
Откройте лист, содержащий данные, отображенные на диаграмме. Введите новые данные в ячейке, значение которой необходимо изменить. Нажмите клавишу [Enter].
Добавление данных в существующую диаграмму
Чтобы добавить дополнительный источник данных в существующую диаграмму, можно воспользоваться одним из нескольких способов. Можно быстро добавить другой ряд данных, перетащить маркеры размеров, чтобы включить данные в диаграмму, внедренную в этот же лист, скопировать дополнительные данные листа на диаграмму или на отдельный лист диаграммы.
Добавление ряда данных в диаграмму
В области «Элементы легенды (ряды)» нажмите кнопку <Добавить>. В поле «Имя ряда» введите имя, которое будет использоваться для ряда, или…Перетаскивание маркеров размера диапазонов для добавления данных во внедренную диаграмму
На листе в ячейках, соседних с существующими данными листа, введите данные и надписи, которые нужно добавить на диаграмму. Щелкните диаграмму, чтобы… - чтобы добавить на диаграмму новые категории и ряды данных, на листе… - чтобы добавить только новые ряды данных, перетащите зеленый маркер размера, включив новые данные и их подписи в…Копирование данных листа на диаграмму
На листе выберите ячейки, содержащие данные, которые нужно добавить на диаграмму. Нажмите сочетание клавиш [Ctrl+C]. Щелкните лист диаграммы или… - чтобы вставить данные в диаграмму нажмите сочетание клавиш [Ctrl+V]; - чтобы определить, как скопированные данные должны быть вставлены в диаграмму, на вкладке Главная в группе Буфер…Изменение данных на существующей диаграмме
Изменить существующую диаграмму можно, изменяя диапазон ячеек, на котором основана диаграмма, или редактируя отдельный ряд данных, показываемый на диаграмме.
Изменение диапазона ячеек, на котором основана диаграмма
Щелкните диаграмму, для которой нужно изменить диапазон ячеек данных источника. При этом появится панель «Работа с диаграммами». На вкладке Конструктор в группе Данные щелкните <Выбрать данные>.
В диалоговом окне «Выбор источника данных» убедитесь, что в поле «Диапазон данных диаграммы» ссылка выбрана полностью. На листе выберите ячейки, содержащие данные, которые должны появиться на диаграмме.
Изменение ряда данных, отображаемого на диаграмме
Изменение подписей вдоль горизонтальной оси (оси категорий)
Щелкните диаграмму, содержащую подписи горизонтальной оси, которые нужно изменить. При этом появится панель «Работа с диаграммами». На вкладке…Удаление данных с диаграммы
Удаление данных источника из листа
На листе выделите ячейку или диапазон ячеек, которые содержат данные, удаляемые с листа, а затем нажмите клавишу [Del].
Удаление ряда данных из диаграммы
Если выбрана диаграмма, выполните следующие действия. На вкладке Конструктор в группе Данные щелкните <Выбрать данные>. В группе Элементы легенды (ряды) выберите удаляемый ряд данных, а затем… Если выбран ряд данных на диаграмме, нажмите клавишу [Del].Создание смешанной диаграммы
Выделите данные, на основе которых будет создана смешанная диаграмма. На вкладке Вставкав группе Диаграммы нажмите кнопку <Гистограмма>. В… Появится элемент диаграммы, при этом станут доступны вкладки Конструктор,… В группе График выберите значение «График с маркерами», а затем нажмите кнопку <ОК>.Задание к работе
1. Исходные данные для лабораторной работы № 2 те же, что и для предыдущей.
2. На рабочих листах создайте не менее 3 различных видов диаграмм.
3. Научитесь редактировать отдельные элементы диаграмм, изменять их размер и местоположение.
4. Создайте комбинированную диаграмму со вспомогательной осью.
5. Сохраните лабораторную работу в новой книге.
Контрольные вопросы.
1. Что означает понятие:
a) ряд данных;
b) категория;
c) легенда?
2. Сколько рядов данных может быть изображено:
a) на круговой диаграмме;
b) на кольцевой диаграмме?
3. Как отредактировать на диаграмме:
a) название диаграммы;
b) название осей X и Y;
c) легенду;
d) изменить тип диаграммы;
e) добавить новые данные?
4. Как указать на гистограмме ключевые значения ряда данных?
5. Каким образом можно комбинировать разные типы диаграмм?
Лабораторная работа 3. Функции Excel
Цель работы: изучение принципов работы со встроенными функциями Excel, получение навыков выполнения статистического анализа.
Теоретические сведения
Средства автоматизации ввода данных в Excel 2007
Так как таблицы часто содержат повторяющиеся или однотипные данные, программа Excel предоставляет средства автоматизации ввода. К числу таких средств относятся автозавершение и автозаполнение
Автозавершение ввода
Если текст должен быть другим, ввод следует продолжать, не обращая внимания на предлагаемый вариант. Автоматический ввод производится только для записей, которые содержат текст…Автозаполнение смежных ячеек
Рис. 8. Ячейка и маркер заполненияАвтозаполнение смежных ячеек числами
Рис. 9. Заполнение ячеек числами При протягивании вправо или вниз числовое значение увеличивается, при протягивании влево или вверх – уменьшается. По…Заполнение числами с шагом отличным от 1
- заполнить две соседние ячейки нужными значениями; - выделить эти ячейки; - протянуть маркер заполнения (рис. 10)Встроенные функции Excel
1. Числовые константы. = ПРОИЗВЕД (2,3) 2*3. 2. Ссылки на ячейки и блоки ячеек.Статистический анализ
1. Вычисление среднего арифметического последовательности чисел: =СРЗНАЧ(числа). Например: =СРЗНАЧ(5;7;9);Загрузка пакета статистического анализа и доступ к нему
1. Нажмите кнопку Microsoft Office , а затем щелкните <Параметры Excel>. 2. Выберите команду «Надстройки» и в окне «Управление» выберите пункт… 3. Нажмите кнопку <Перейти>.Работа с надстройкой Анализ данных
В большинстве случаев в открывшемся диалоговом окне нужно просто указать интервал исходных данных, интервал для вывода результатов и задать… Инструмент «Описательная статистика» формирует таблицу статистических данных,… Инструмент «Генерация случайных чисел» дает возможность получать равномерное и неравномерное распределение.Задание к работе
Каждый вариант состоит из двух заданий. Для выполнения первого задания необходимо:
1. На рабочем листе построить таблицу значений функции согласно варианта задания и ее график.
2. Определите среднее, минимальное и максимальное значение функции и вывести эти данные на графике. Создав тем самым смешанную диаграмму.
3. Используя логическую формулу, вычислить сумму значений функций, если среднее, минимальное и максимальное значения имеют одинаковые знаки и произведение в противном случае.
4. Произвольной ячейке присвоить имя и сгенерировать в ней случайное число. В таблице значений функции добавить еще один столбец, полученный умножением у на случайное число. Добавить на графике функции второй график, соответствующий полученному столбцу данных.
5. Напишите логическую формулу, которая выводит текстовое сообщение «Вычислена сумма» или «Вычислено произведение» в зависимости от того, что было вычислено на рабочем листе в п. 3 задания 1.
6. Сохраните работу в новой книге
Исходными данными для второго задания являются варианты заданий к лабораторной работе №1. Необходимо:
- провести статистический анализ с использованием функций 1-6 методических указаний к работе;
- построить гистограмму распределения данных;
- произвести описательную статистику.
Варианты индивидуальных заданий
Таблица 2
| Вариант | Функция | Диапазон | Шаг |
| 
| dx=0,2 | |
| 
| dx=1 | |
| 
| dx=0,75 | |
| 
| dx=0,4 | |
| 
| dx=0,7 | |
| 
| dx=5 | |
| 
| dx=2,5 | |
| 
| dx=0,4 | |
| 
| dx=0,3 | |
| 
| dx=1 |
Контрольные вопросы.
1. Какие категории функций Excel Вы знаете (назовите не менее пяти)?
2. Что может выступать в качестве аргументов функции?
3. Приведите примеры стандартных функций MS Excel, применяемых для статистического анализа.
4. Для чего предназначен Пакет анализа и каков порядок доступа к его инструментам?
5. В чем заключаются особенности построения гистограммы распределения данных?
Лабораторная работа 4. Средства работы с книгами
Цель работы: изучение средств работы с рабочими книгами MS Exsel.
Теоретические сведения
Работа с листами книги
Менять расположение листов друг относительно друга проще всего мышью. Нужно захватить ярлычок левой кнопкой мыши и перетащить его по горизонтали на… Для переименования, вставки, удаления, перемещения и копирования листов можно… Отменить удаление листа невозможно, поэтому хорошенько подумайте перед удалением.Средства защиты книг
Чтобы предотвратить умышленное либо случайное изменение, перемещение или удаление важных данных, можно установить защиту определенных элементов листа или книги с использованием пароля или без него.
Защита элементов листа
Ограничение доступа к отдельным листам
Для того чтобы защитить лист выполните следующие действия:
- на вкладке Рецензирование в группе Изменения выберите команду «Защитить лист»;
- в списке «Разрешить всем пользователям этого листа» отметьте флажками элементы, изменение которых должно быть доступно пользователям.
- в диалоговом окне «Пароль для отключения защиты листа» введите пароль для защиты листа, нажмите кнопку <Ok>, а затем еще раз введите пароль для подтверждения.
Сокрытие формул на листе
Выберите лист, который нужно защитить. Чтобы скрыть все формулы, которые не должны отображаться, выполните следующие действия:
- выберите на листе ячейки, содержащие формулы, которые необходимо скрыть;
- на вкладке Главная в группе Ячейки щелкните «Формат», а затем выберите команду «Формат ячеек»;
- На вкладке «Защита» установите флажок «Скрыть формулы» и затем нажмите кнопку <Ok>.
Разблокировка некоторых ячеек или их диапазона
Чтобы разблокировать все ячейки или диапазоны, которые должны быть доступны другим пользователям для изменения, выполните следующие действия:
- выберите последовательно все ячейки или диапазоны, которые нужно разблокировать;
- На вкладке Главная в группе Ячейки щелкните <Формат>, а затем выберите команду «Формат ячеек».
- На вкладке «Защита» снимите флажок «Не обновлять связь» и нажмите кнопку <Ok>.
Разблокировка графических объектов
- удерживая нажатой клавишу [Ctrl], последовательно щелкните все графические объекты, которые требуется разблокировать (графические объекты должны… - yа вкладке Формат в группе Размер нажмите кнопку вызова диалогового окна … - на вкладке «Свойства» снимите флажок «Защищаемый объект», а также флажок «Скрыть текст» (если он есть).Защита элементов книги
Ограничение возможностей изменений в книге
- чтобы защитить структуру книги, установите флажок «Структура»; - чтобы при каждом открытии книги ее окна сохраняли свой размер и положение,… - чтобы другой пользователь не смог снять защиту с листа, введите пароль в поле «Пароль (не обязателен)», нажмите…Установление доступа к книге по паролю
Совместная работа с книгой
Для того чтобы предоставить книгу в совместный доступ выполните следующее: 1. На вкладке Рецензирование в группе Изменения выберите команду «Доступ к… 2. Установите флажок «Разрешить изменять файл нескольким пользователям одновременно».Просмотр журнала изменений
В группе «Отслеживать исправления, внесенные» установите флажок «по времени» и в списке «время» выберите вариант «Все». Снимите флажки «пользователем» и «в диапазоне». Установите флажок «Вносить изменения на отдельный лист».Задание к работе
1. Объединить ранее созданные книги в одну.
2. Переименовать листы новой книги по названиям предыдущих работ.
3. Создать копию одного из листов и назвать его Копия.
4. Установить пароль по чтению для новой книги.
5. Установить защиту для одного из листов.
6. Установить защиту книги.
7. Отработать операции ограничения совместного доступа к книге:
- организовать совместный доступ к своей книге, для этого привлечь своего товарища или соседа;
- разрешить сохранение журнала изменений;
- выполнить несколько изменений, включающих и противоречивые изменения одних и тех же ячеек в книге общего доступа;
- продемонстрировать, чем отличаются параметры группы «Обновлять изменения»;
- продемонстрировать, чем отличаются параметры группы «Для противоречивых изменений».
8. Показать результаты преподавателю.
Контрольные вопросы.
1. Как произвести слияние книг?
2. Как обеспечить защиту листа рабочей книги?
3. Как обеспечить доступ к книге по паролю?
4. Как обеспечить доступ к книге для совместного использования?
5. Как обеспечить регистрацию изменений в книге?
6. Какие существуют параметры для обновления изменений при совместной работе?
7. Чем отличаются параметры для группы противоречивые изменения?
Лабораторная работа 5. Фильтры
Цель работы: ознакомление с возможностями по выборке данных в списках с помощью фильтров.
Теоретические сведения
Как правило, список состоит из записей (строк) и полей (столбцов). Столбцы должны содержать однотипные данные. Если в списке присутствуют заголовки,…Сортировка списков
- выделите список; - нажмите кнопку <Сортировка и фильтр> в группе Редактированиена… - выберите «Сортировка от А до Я».Фильтрация списков
Основное отличие фильтра от упорядочивания – это то, что во время фильтрации записи, не удовлетворяющие условиям отбора, временно скрываются (но не удаляются), в то время, как при сортировке показываются все записи списка, меняется лишь их порядок.
Фильтры бывают двух типов: обычный фильтр (его еще называют автофильтр) и расширенный фильтр.
Автофильтр
В столбцах списка появятся кнопки со стрелочками, нажав на которые можно настроить параметры фильтра (рис. 13).Расширенный фильтр
- скопируйте и вставьте на свободное место шапку списка; - в соответствующем поле (полях) задайте критерии фильтрации; - выделите основной список;Примеры условий отбора расширенного фильтра
В условия отбора расширенного фильтра может входить несколько условий:
а) накладываемых на один столбец. Чтобы задать для отдельного столбца три или более условий отбора, они вводятся в ячейки, расположенные в смежных строках. Например, для следующего диапазона условий будут отобраны строки, содержащие либо «Белов», «Батурин» или «Сушкин» в столбце «Студент».
| Студент |
| Белов |
| Батурин |
| Сушкин |
б) накладываемых одновременно на несколько столбцов. Для этого условия вводятся в ячейки, расположенные в одной строке диапазона условий. Например, для следующего диапазона условий будут отобраны строки, содержащие данные о студентах группы ИВТ-09, сдавших экзамен по физике на 4 или 5 и по культурологии – на 5.
| Группа | Физика | Культурология |
| ИВТ-09 | >3 |
в) накладываемых на один из столбцов. Для этого условия вводятся в ячейки, расположенные в разных строках диапазона условий. Например, для следующего диапазона условий будут отобраны строки с информацией о студентах, сдавших либо физику, либо культурологию на 3.
| Физика | Культурология |
г) сложное условие отбора, накладываемое на несколько столбцов. Для этого вводят его составные части в отдельные строки диапазона условий. Например, для следующего диапазона условий будут отобраны строки, содержащие данные о студентах групп ИВТ-09 и АСУ-09, сдавших экзамен по физике на 4 и 5.
| Группа | Физика |
| ИВТ-09 | >3 |
| АСУ-09 | >3 |
д) диапазонное условие, накладываемое на один столбец. Для этого вводят его составные части в отдельные столбцы диапазона условий, но эти столбцы должны иметь одинаковые имена. Например, для следующего диапазона условий будут отобраны строки, содержащие данные о студентах, сдавших экзамен по физике более чем на 2, но менее 5.
| Физика | Физика |
| >2 | <5 |
Виды условий отбора
1. Последовательности символов. При использовании текстовой константы в качестве условия отбора будут отобраны все строки с ячейками, содержащими… 2. Знаки подстановки. Используются для того, чтобы отобрать строки с… 3. Значения сравнения. Чтобы отобрать строки с ячейками, имеющими значения в заданных границах, следует использовать…Задание к работе
1. Для своего варианта создать таблицу в соответствии со структурой строки заголовка и внести в нее не менее 10 записей.
2. Выполнить задания на использования автофильтрации для своего варианта. Результаты каждого задания копировать на отдельный лист.
3. Выполнить задания на использования расширенного фильтра для своего варианта. Результаты каждого задания копировать на отдельный лист.
4. Все листы поименовать по смыслу.
Варианты индивидуальных заданий
Вариант 1.
| Марка машины | Номер | Год выпуска | Год приобретения | Цвет машины | Пробег | Цена (у. е.) | Владелец |
Задание на использование автофильтрации:
1. Определить белые Ауди, год выпуска которых больше 2000, но меньше либо равен 2006. Отсортировать полученные данные по возрастанию года выпуска автомобилей.
2. Определить красные Форды, пробег которых больше либо равен 150 000 км, а цена меньше либо равна 10 000 у.е. Отсортировать полученные данные по убыванию цены.
3. Определить автомобили, год приобретения которых больше либо равен 2007, и цена находится в следующих пределах: от 5 000 до 14 000 у.е. Отсортировать полученные данные сначала по возрастанию года выпуска, а затем по убыванию цены.
4. Определить желтые автомобили, пробег которых меньше либо равен 50 000 км, и год выпуска 2008. Отсортировать полученные данные сначала по марке автомобиля (по алфавиту), а затем – по возрастанию пробега автомобилей.
Задание на использование расширенного фильтра:
1. Определить белые Мерседесы или черные Ауди, год выпуска которых больше 2000 года.
2. Определить машины, год выпуска которых больше 2000 года и вторая буква номера которых «С».
3. Определить автомобили, фамилии владельцев которых начинаются на букву «Д» или «П», и пробег которых больше среднего для всех автомобилей.
4. Определить владельцев автомобилей, фамилии которых заканчиваются на «*ов», или владельцев автомобилей, год выпуска которых больше 2000 года, но меньше 2005 года.
5. Определить белые или черные автомобили, год выпуска которых больше 2006 года, пробег которых меньше либо равен максимальному значению пробегов для всех автомобилей.
Вариант 2.
| Дата | Город | Вид осадков | Количество осадков | Температура | Давление | Направление ветра | Сила ветра |
Задание на использование автофильтрации:
1. Определить города, температура в которых за конкретную дату превышала 9 °С. Отсортировать полученные данные сначала по городу, а затем — по возрастанию температуры.
2. Определить данные по погоде для конкретного города за конкретный промежуток времени. Отсортировать полученные данные сначала по виду, а затем — по возрастанию количества осадков.
3. Определить города, в которых наблюдалось северо-восточное направление ветра за конкретный период времени. Отсортировать эти данные сначала по городам, а затем — по возрастанию даты.
4. Определить города, температура в которых наблюдалась в пределах от 5 до 14 °С за конкретную дату. Отсортировать полученные данные сначала по городам, а затем — по возрастанию температуры.
Задание на использование расширенного фильтра:
1. Определить города, для которых направление ветра — северное или северо-западное, температура воздуха в которых больше 8 °С.
2. Определить данные о погоде для Санкт-Петербурга или Минска за некоторый конкретный промежуток времени.
3. Определить города, в которых за конкретный промежуток времени выпал снег или снег с дождем, а также — температура в которых находилась в пределах от -5 °С до +3 °С.
4. Определить города, сила ветра в которых не превышает средней силы ветра и количество осадков в которых больше либо равно среднему значению для всех городов, или города, вид осадков в которых — град с дождем.
5. Определить города с западным или юго-западным направлением ветра, сила которого больше минимальной или города, температура в которых за некоторый промежуток времени равны средней температуре для всех городов.
Вариант 3.
| ФИО | Год рождения | Год поступления | Специальность | Курс | Спортивный норматив | Результат | Оценка |
Задание на использование автофильтрации:
1. Определить студентов конкретного года рождения, оценки которых за спортивные нормативы больше 3. Отсортировать эти данные сначала по специальности, затем — по фамилии студента.
2. Определить студентов конкретного года рождения, которые сдали определенный норматив. Отсортировать эти данные сначала по году поступления студентов, а затем — по результатам сдачи.
3. Определить студентов конкретной специальности, которые сдали определенный норматив. Отсортировать эти данные сначала по курсу, а затем — по результатам сдачи.
4. Определить студентов конкретного курса, оценка которых за сдачу спортивного норматива больше 2, но меньше либо равна 4. Отсортировать полученные данные сначала по специальности, а затем — по оценке.
Задание на использование расширенного фильтра:
1. Определить студентов специальности ИВТ, МАХП, сдавших с оценкой "4" бег на 100 м.
2. Определить студентов первого или второго курса, оценки которых больше либо равны средней оценке для всех студентов.
3. Определить студентов с максимальными показателями (оценками) по всем спортивным нормативам для специальностей ИВТ и АТП.
4. Определить студентов с неудовлетворительными оценками для всех курсов и факультетов.
5. Определить студентов данного года рождения и данного года поступления, сдавших бег на 100 м или прыжки в длину с оценкой "4" и выше.
Вариант 4.
| Продавец (фирма) | Страна-продавец | Товар | Количество | Цена | Дата | Страна-покупатель | Покупатель (фирма) |
Задание на использование автофильтрации:
1. Определить товары, цена которых находится в некоторых пределах и которые проданы за конкретную дату. Отсортировать полученные данные сначала по стране-импортеру, а затем — по фирме-продавцу.
2. Определить фирмы-покупатели, количество купленных товаров которых за конкретную дату превысило 100 единиц. Отсортировать полученные данные сначала по товару, затем —по возрастанию цены товара.
3. Определить страны-покупатели, продающие конкретные виды товаров (например, дискеты и бумагу для офиса), цена которых больше некоторого значения. Отсортировать эти данные сначала по стране, а затем — по виду товаров.
4. Определить товары, проданные за конкретный промежуток времени некоторой страной-импортером. Отсортировать полученные данные сначала по наименованию товара, а затем — по дате продажи.
Задание на использование расширенного фильтра:
1. Определить товары, проданные фирмами-продавцами из Франции, Германии или Бельгии, количество которых больше 100 единиц.
2. Определить фирмы-покупатели из стран России или Беларуси, купившие товар за конкретный период времени.
3. Определить фирмы-продавцов с максимальным и минимальным объемами продаж.
4. Определить товара в названии которых присутствует буква «о», и цена которых более 200 у.е., но менее 500 у.е.
5. Определить фирм продавцов, которые сотрудничают с покупателями из стран, название которых начинается на «А», или те фирмы, которые реализуют товар в количестве более 10000 ед.
Вариант 5.
| ФИО | Курс | Группа | Тема расчетного задания | Научный рук-тель | Кафедра | Дата выдачи задания | Дата защиты | Оценка |
Задание на использование автофильтрации:
1. Определить студентов данного научного руководителя, выполнивших расчетное задание на 4 и 5. Отсортировать эти данные сначала по дате выдачи, затем — по фамилии студентов.
2. Определить работы, выданные не позднее конкретного числа и защищенные до конкретной даты включительно. Отсортировать полученные данные сначала по кафедре, затем — по научному руководителю.
3. Определить студентов данного курса, имеющих расчетные задания по заданной кафедре. Отсортировать полученные данные сначала по группе, а затем — по научному руководителю.
4. Определить студентов данной группы и данного курса, выполнивших расчетные задания не позднее конкретного числа. Отсортировать полученные данные сначала по кафедре, затем — по оценке.
Задание на использование расширенного фильтра:
1. Определить студентов данного научного руководителя, получивших "4" или "5" по курсовой работе и сдавших работу до определенного числа.
2. Определить студентов первого курса, специальности ИВТ или на АТП, оценка которых за расчетное задание больше либо равна средней оценке для студентов.
3. Определить студентов, фамилия которых заканчивается на «*ов», тема расчетного задания «Системы счисления» или «Алгебра логики».
4. Определить студентов, получивших задание в конкретный промежуток времени и получивших за него оценку «5».
5. Определить студентов кафедры АТП, которые выполнили расчетные задания не позднее конкретного числа на оценку «5» или студентов, фамилии которых начинаются на «П», и имеющих за расчетные задания оценку «4».
Вариант 6.
| Название книги | Автор/ы | Жанр книги | Год издания | Место издания (город) | Издательство | Количество страниц | Тираж | Цена |
Задание на использование автофильтрации:
1. Определить книги данного года издания, тираж которых находился в некоторых пределах. Отсортировать полученные данные сначала по городу издания, а затем — по издательству.
2. Определить книги заданного жанра, цена которых находится в некоторых пределах. Отсортировать эти данные сначала по году издания, а затем — по цене.
3. Определить авторов книг, публикующихся в конкретном жанре, место издания книг которых— два конкретных города. Отсортировать полученные данные сначала по тиражу, а затем — по цене книг.
4. Определить книги, количество страниц которых больше заданного числа, а тираж находится в некоторых пределах. Отсортировать эти данные сначала по теме, затем — по цене книг.
Задание на использование расширенного фильтра:
1. Определить книги, цена которых больше средней цены для всех книг.
2. Определить книги, изданные в Москве или Минске, тираж которых составляет 500 штук.
3. Определить книги, изданные в Санкт-Петербурге или Минске, цена которых меньше средней цены всех книг или количество страниц которых находится в пределах от 200 до 350.
4. Определить книги определенного жанра, тираж которых больше среднего тиража книг всех книг или книги, в фамилии авторов которых есть буквы "ск".
5. Определить книги, у которых количество страниц больше 100 и в названии присутствует буква «к».
Вариант 7.
| ФИО сотрудника | Дата рождения | Пол | Должность | Отдел | Год поступления на работу | Стаж | Количество детей |
Задание на использование автофильтрации:
1. Определить сотрудников отдела маркетинга, поступивших на работу раньше конкретного года. Отсортировать эти данные сначала по фамилии сотрудников, затем — по должностям.
2. Определить сотрудников, которые имеют троих и более детей. Отсортировать полученные данные сначала по отделам, затем — по фамилии сотрудников.
3. Определить сотрудников женского пола, занимающих должность начальника отдела. Отсортировать полученные данные сначала по отделам, а затем — по дате рождения.
4. Определить сотрудников данного отдела и пола, имеющих стаж не менее 5 лет. Отсортировать полученные данные сначала по должности, затем — по дате рождения.
Задание на использование расширенного фильтра:
1. Определить сотрудников данного отдела, имеющих 3-х или 4-х детей и стаж работы более 5 лет.
2. Определить сотрудников, отдела рекламы и маркетинга, стаж которых меньше или равен среднему стажу всех сотрудников.
3. Определить сотрудников, фамилия которых заканчивается на «*ов», работающих в должности специалиста или помощника.
4. Определить сотрудников 1985 года рождения, имеющих более 1 ребенка, или сотрудников 1986 года рождения, имеющих более 2-х детей.
5. Определить сотрудников отдела рекламы, которые устроились на работу в 2000 году или сотрудников, фамилии которых начинаются на «П», и имеющих 2-х детей.
Вариант 8.
| ФИО читателя | Дата рождения | Адрес (улица) | Название книги | Автор | Стоимость | Дата выдачи | Дата возврата |
Задание на использование автофильтрации:
1. Определить книги данного автора, выданные конкретного числа. Отсортировать полученные данные сначала названию книги, а затем — по дате возврата.
2. Определить читателей, живущих на улице Ленина и читающих Булгакова. Отсортировать эти данные сначала по дате рождения, а затем — по названию книги.
3. Определить читателей, живущих на улице Труда и 9 мая, читающих Пушкина и Лермонтова. Отсортировать полученные данные сначала по названию книги, а затем — по дате рождения.
4. Определить книги, взятые в период с 3 сентября 2010 года по 5 октября 2010 года, читателями с улицы Пятилетки. Отсортировать эти данные сначала по автору, затем — дате возврата.
Задание на использование расширенного фильтра:
1. Определить книги, стоимость которых больше средней стоимости для всех книг.
2. Определить книги, Пушкина и Лермонтова, которые были взяты читателями в сентябре.
3. Определить читателей, живущих на улице Ленина или Пятилетки, и читающих книги, стоимость которых меньше средней стоимости всех книг или дата выдачи сентябрь.
4. Определить читателей или авторов, в фамилии которых есть буквы «у» или «ы».
5. Определить книги, стоимость которых больше 500 у.е. и в названии присутствует буква «к».
Вариант 9.
| Наим. товара | Фирма производитель | Модель | Сборка (страна) | Дата поступления | Цена поступления | Кол-во |
Задание на использование автофильтрации:
1. Определить стиральные машины, цена поступления которых менее 30 000 руб. Отсортировать эти данные сначала по фирме производителю, затем — по моделям.
2. Определить пылесосы, фирмы Thomas. Отсортировать полученные данные сначала по количеству, затем — по цене поступления.
3. Определить технику, количество которой более 20 единиц. Отсортировать полученные данные сначала по наименованию товара, а затем — по фирме производителю.
4. Определить газовые плиты, количество которых более 8 единиц и цена поступления от 8 000 руб. Отсортировать полученные данные сначала по модели, затем — по количеству.
Задание на использование расширенного фильтра:
1. Определить газовые плиты, количество которых равно 5 или 7, а цена поступления менее 9 000 руб.
2. Определить газовые и электроплиты, цена поступления которых меньше средней цены поступления всех товаров.
3. Определить технику, фирмы «Bosh», цена поступления которой 10 000 или 15 000 руб.
4. Определить стиральные машины фирмы «Ariston», в модели которых есть буква «Q», или стиральные машины фирмы «Indesit», собранные в России.
5. Определить телевизоры «Samsung», в модели которых указаны буквы «LED» или телевизоры «LG», цена поступления которых от 20 000 руб.
Вариант 10.
| ФИО сотрудника | Год рождения | Пол | Отдел | Должность | Оклад | Премия | Итого |
Задание на использование автофильтрации:
1. Определить сотрудников конкретного года рождения, оклад которых больше 9 000 у.е. Отсортировать эти данные сначала по должности, затем — по фамилии сотрудника.
2. Определить сотрудников конкретного года рождения, которые работают в определенном отделе. Отсортировать эти данные сначала по окладу сотрудниов, а затем — размерам премии.
3. Определить сотрудников конкретной должности, которые получили премию больше 5 000 у.е. Отсортировать эти данные сначала по году рождения, а затем — по фамилии.
4. Определить сотрудников, занимающих конкретную должность, оклад которых больше 7 000 у.е., но меньше либо равен 15 000 у.е. Отсортировать полученные данные сначала по фамилии, а затем — по премии.
Задание на использование расширенного фильтра:
1. Определить сотрудников отдела маркетинга и информационных технологий, имеющих оклад 15 000 у.е. и премию 6 000 у.е.
2. Определить сотрудников отдела рекламы и бухгалтерии, премия которых больше либо равна средней премии всех остальных сотрудников.
3. Определить сотрудников с максимальными окладами.
4. Определить сотрудников, в фамилии которых есть буква «о» или мужчин, работающих в отделе маркетинга.
5. Определить сотрудников отдела рекламы, которые родились в 1985 году или женщин, фамилии которых начинаются на «П».
Контрольные вопросы.
1. Как произвести автофильтрацию?
2. Для каких целей может быть использована фильтрация данных?
3. В каких случаях требуется фильтрация с использованием сложных критериев (расширенный фильтр)?
4. В каких случаях используется автофильтр
5. Как отобразить все данные после фильтрации?
6. Является ли обязательным наличие области критериев в расширенном фильтре?
7. Какое минимальное количество строк и столбцов может быть в области критериев?
8. Всегда ли результат расширенного фильтра будет отображаться на месте исходного диапазона?
Лабораторная работа 6. Формы и автоматическое подведение итогов
Цель работы: научиться создавать формы, использовать их для ввода и модификации данных, а также изучить средства автоматического подведения итогов.
Теоретические сведения
Форма
Форма — это наиболее удобный способ для просмотра, изменения, добавления и удаления записей списка, а также для поиска записей, удовлетворяющих заданным условиям.
Для работы с формой данных ее необходимо добавить на панель быстрого доступа. Для этого:
- Щелкните стрелку на панели быстрого доступа и выберите элемент «Другие команды».
- В открывшемся окне «Параметры Excel» в поле «Выбрать команды из» выберете из раскрывающегося списка «Все команды».
- В предлагаемом списке выберите кнопку «Форма» и щелкните элемент «Добавить». Теперь данные можно вводить, используя форму ввода 
.
Перед работой с формой необходимо задать заголовки столбцов списка (эти заголовки используются для создания полей формы). Предварительно курсор размещается в списке.
Вставка записей выполняется с помощью кнопки <Добавить>. Вводятся поля новой записи, используя клавишу [TAB] для перемещения к следующему полю (для перемещения к предыдущему полю используйте сочетание клавиш [SHIFT]+[TAB]). Чтобы добавить запись в список, нажмите клавишу [ENTER]. Чтобы отменить добавление записи, нажмите кнопку <Вернуть> перед нажатием клавиши [ENTER] или кнопки <Закрыть>.
Изменение записей требует предварительного размещения курсора в нужной ячейке. После изменения данных нажмите клавишу [ENTER] для обновления текущей записи и перехода к следующей записи. Чтобы отменить изменения записи, нажмите кнопку <Вернуть> перед нажатием клавиши [ENTER] или кнопки <Закрыть>, пока эта запись активна в форме.
Удаление записи выполняется с помощью кнопки <Удалить>. Причем предварительно нужная запись должна быть найдена.
Поиск записей.Для перемещения на одну запись нажмите на стрелки полосы прокрутки в диалоговом окне. Чтобы переместиться на 10 записей, нажмите полосу прокрутки между стрелками. Нажмите кнопку <Далее> для перехода к следующей записи списка и <Назад> — для перехода к предыдущей.
Чтобы задать условия поиска или условия сравнения, нажмите кнопку <Критерии>. Введите критерии в форме (при этом используются правила записи условий, рассмотренные в предыдущей работе). Чтобы найти совпадающие с критериями записи, нажмите кнопки <Далее> или <Назад>. Чтобы вернуться к правке формы, нажмите кнопку <Правка>.
Промежуточные итоги
С помощью операции Итоги можно: - указать способ группировки данных; - вывести промежуточные и общие итоги для одной группы в списке;Пример
Дан список со следующими полями: № пп, Продавец, Товар, Номер партии, Цена, Количество, Итого (рис. 17). Определить количество товаров, проданных конкретным продавцом.
Рис. 17. Список продаж
Решение
2. Примените команду Данные → Промежуточные итоги. В окне Промежуточные итоги установите параметры в соответствии с рис. 18: для получения…Задание к работе
1. Исходные данные для лабораторной работы № 6 те же, что и для предыдущей.
2. Для своего варианта создать форму в соответствии со структурой строки заголовка.
3. С помощью Формы:
- добавить в список несколько записей в разные его места.
- модифицировать часть записей, найдя их через критерий.
- удалить несколько записей, найдя их через критерий.
4. Для своего варианта вывести указанные итоги. Каждое задание выполнять на отдельном листе.
Варианты индивидуальных заданий
Вариант 1.
1. Средняя, максимальная и минимальная цены определенной марки машины с учетом конкретного года приобретения.
2. Суммарный и средний пробег определенной марки машины с учетом конкретного года выпуска.
3. Количество и средняя цена машин определенного цвета с учетом конкретного года приобретения.
Вариант 2.
1. Среднее количество осадков данного вида с учетом данного города и конкретной даты.
2. Суммарное и среднее количество осадков данного вида для конкретного города.
3. Количество случаев с определенным направлением ветра с учетом конкретной даты.
Вариант 3.
1. Суммарный балл для каждой специальности с учетом вида спортивного норматива.
2. Средний результат для данного спортивного норматива с учетом курса и специальности.
3. Средние балл и результат по данному спортивному нормативу, учитывающие суммарные балл и результат по каждой специальности и группе.
Вариант 4.
1. Средние цену и количество проданных товаров конкретной фирмой-продавцом за конкретную дату.
2. Общее количество товаров данного вида с учетом среднего количества товаров, проданных конкретной страной.
3. Общее количество товаров, купленное фирмой-покупателем с учетом вида товара и его средней цены.
Вариант 5.
1. Количество расчетных заданий, выданных с конкретной датой на данной кафедре конкретным научным руководителем.
2. Средняя оценка по расчетным заданиям за конкретную дату защиты по конкретным курсам и группам.
3. Количество расчетных заданий, защищенных с конкретной датой, со средней оценкой по данному курсу.
Вариант 6.
1. Суммарный тираж книг данного жанра, учитывающий средний тираж каждого года издания.
2. Средняя цена книг данного автора с учетом суммарного тиража за конкретный год издания.
3. Среднее количество страниц для данного издательства за конкретный год издания.
Вариант 7.
1. Определить средний стаж сотрудников каждого отдела, учитывающий средний стаж для каждой должности.
2. Суммарное количество детей и суммарный стаж для каждого отдела
3. Для каждого отдела определить количество сотрудников, имеющих детей.
Вариант 8.
1. Средняя, максимальная и минимальная стоимость книг определенного автора.
2. Определить суммарную и среднюю стоимость книг, прочитанных каждым читателем.
3. Определить минимальную стоимость книг, прочитанных каждым читателем с учетом фамилии автора.
Вариант 9.
1. Средние цену и количество поступивших товаров конкретной фирмой производителем за конкретную дату.
2. Общее количество товаров данного вида с учетом среднего количества товаров, поступивших со сборкой в конкретной стране.
3. Общее количество товаров, поступивших от конкретной фирмой производителя с учетом вида товара и его средней цены.
Вариант 10.
1. Определить средний оклад сотрудников каждого отдела, учитывающий средний оклад для каждой должности.
2. Суммарное премию и суммарный оклад для каждого отдела
3. Для каждого отдела определить количество сотрудников, имеющих премию.
Контрольные вопросы.
1. Для чего предназначен инструмент Промежуточные итоги?
2. Можно ли подводить промежуточные итоги сразу по нескольким функциям?
3. Какие действия нужно выполнить, чтобы после подведения итогов можно было бы увидеть только промежуточные результаты?
4. Как удалить результаты промежуточных итогов?
5. Что такое форма данных?
6. В каких случаях предпочтительнее пользоваться формой данных?
7. Можно ли с помощью формы данных редактировать данные списка? Если – да, то что для этого нужно сделать?
Лабораторная работа 7. Консолидация данных
Цель работы: изучение средств консолидации данных
Теоретические сведения
Назначение консолидации данных состоит в совместном использовании данных, расположенных в разных местах, для выполнения некоторых расчетов. Существует несколько способов консолидации:
- с помощью трехмерных ссылок;
- по расположению;
- по категориям.
Консолидация данных на основе трехмерных ссылок
Для решения задачи консолидации этим способом надо выполнить следующие действия: - На листе консолидации скопировать или задать надписи для данных… - Указать ячейку, в которую следует поместить данные консолидации, и ввести формулу для расчета. Она должна включать…Консолидация данных по расположению
- Указать верхнюю левую ячейку области назначения – выбранного для хранения консолидируемых данных диапазона ячеек, который может находиться на том… - Выбрать команду Консолидация на вкладке Данные и из раскрывающегося списка… - Ввести в поле «Ссылка» исходную область консолидируемых данных. Нажать кнопку <Добавить>.Консолидация данных по категориям
Осуществляется также, как и консолидация данных по расположению за добавлением следующего: в наборе флажков «Использовать в качестве имен»… Однако следует убедиться в следующем: - Заголовки строк или столбцов были включены в исходные области.Изменение итоговой таблицы консолидации данных
После создания итоговой таблицы с помощью команды Консолидация можно добавлять, удалять или изменять исходные области данных. Кроме этого, можно создать связи итоговой таблицы с исходными данными, с тем, чтобы данные области назначения автоматически обновлялись при изменении данных в исходных областях.
Добавление области данных в итоговую таблицу
- укажите верхнюю левую ячейку существующей итоговой таблицы; - выберите команду Консолидация на вкладке Данные; - укажите поле «Ссылка»;Изменение области данных в итоговой таблице
- укажите левую верхнюю ячейку существующей итоговой таблицы; - выберите команду Консолидация на вкладке Данные; - в списке Список диапазонов укажите исходную область, которую следует изменить;Удаление области данных из итоговой таблицы
- укажите верхнюю левую ячейку существующей итоговой таблицы;
- выберите команду Консолидация на вкладке Данные;
- в списке Список диапазонов укажите исходную область, которую следует удалить;
- нажмите кнопку <Удалить>;
- чтобы пересчитать итоговую таблицу, нажмите кнопку <OK>.
Чтобы сохранить новый набор исходных областей без пересчета итоговой таблицы, нажмите кнопку <Закрыть>.
Задание к работе
1. На двух листах для своего варианта создать таблицы аналогичные по структуре, но отличающиеся по содержанию.
2. Выполнить консолидацию введенных данных тремя описанными способами, помещая каждый раз результат на отдельный лист и именуя его соответствующим образом. При консолидации решить задачу, поставленную для каждого варианта. При этом создавать связи с исходными таблицами.
3. Выполнить изменения некоторых данных в исходных таблицам и отследить изменение результирующих данных в итоговых таблицах.
4. Показать результат преподавателю.
Варианты индивидуальных заданий
Вариант 1.
| Марка автомобиля | Стоимость автомобиля каждого года выпуска | |||||
| Audi | ||||||
| BMW | ||||||
| Chevrolet | ||||||
| Ford | ||||||
| Honda |
Определить среднюю цену автомобилей данных марок, которые реализуются автосалонами города (на листе 1 данные первого автосалона, на листе 2 – второго).
Вариант 2.
| Город | Июнь | Июль | Август | |||
| Осадки | Температура | Осадки | Температура | Осадки | Температура | |
| Пермь | ||||||
| Березники | ||||||
| Соликамск | ||||||
| Краснокамск | ||||||
| Чусовой |
Определить среднее количество осадков и среднюю температуру для некоторых городов области в летний период за 2009-2010 года (на листе 1 данные 2009 года, на листе 2 – 2010).
Вариант 3.
| Фамилия студента | Результаты выполнения норматива | |||||
| Прыжок в длину (см) | Прыжок в высоту (см) | Бросание ядра (м) | Тройной прыжок (м) | Количество отжиманий | Количество подтягиваний | |
| Загорулько | ||||||
| Иванов | ||||||
| Петров | ||||||
| Сидоров | ||||||
| Чугайнов |
Определить лучший результат выполнения нормативов после двух попыток (на листе 1 данные первой попытки, на листе 2 – второй).
Вариант 4.
| Наименование товара | Количество товара, проданного в каждом месяце | ||||
| Август | Сентябрь | Октябрь | Ноябрь | Декабрь | |
| Холодильники | |||||
| Газовые плиты | |||||
| Посудомоечные машины | |||||
| Стиральные машины | |||||
| Пылесосы |
Определить общее количество некоторых видов товаров, проданных через магазины некоторой фирмы (на листе 1 данные одного магазина, на листе 2 – второго).
Вариант 5.
| Фамилия студента | Балл за выполнение задания | ||||
| Количество информации | Системы счисления | Алгебра логики | Алгоритмизация | Расчет сети | |
| Загорулько | |||||
| Иванов | |||||
| Петров | |||||
| Сидоров | |||||
| Пупкин | |||||
| Чугайнов |
Определить среднюю оценку за выполнение расчетных и домашних заданий для некоторых студентов группы (на листе 1 данные расчетных заданий, на листе 2 – домашних).
Вариант 6.
| Город издательства | Количество книг каждого жанра | ||||
| Наука | Техника | Роман | Детектив | Детская | |
| Москва | |||||
| Санкт-Петербург | |||||
| Тверь | |||||
| Челябинск | |||||
| Уфа | |||||
| Екатеринбург |
Определить общий тираж книг данной тематики, издающихся в различных городах в 2009/2010 годах (на листе 1 данные 2009 года, на листе 2 – 2010 года).
Вариант 7.
| Фамилия сотрудника | Количество выходных дней и отгулов за первое полугодие | |||||
| Январь | Февраль | Март | Апрель | Май | Июнь | |
| Загорулько | ||||||
| Иванов | ||||||
| Петров | ||||||
| Сидоров | ||||||
| Пупкин | ||||||
| Чугайнов |
Определить среднее количество выходных дней и отгулов за первое полугодие в 2009/2010 годах (на листе 1 данные 2009 года, на листе 2 – 2010 года).
Вариант 8.
| Фамилия сотрудника | Количество книг каждого автора | |||||
| Пушкин | Лермонтов | Гоголь | Толстой | Некрасов | Тургенев | |
| Сынов | ||||||
| Пупкин | ||||||
| Бобылев | ||||||
| Анисина | ||||||
| Дуров | ||||||
| Сидоров |
Определить среднее количество книг, каждого автора прочитанных читателями в 2009/2010 годах (на листе 1 данные 2009 года, на листе 2 – 2010 года).
Вариант 9.
| Наименование товара | Количество товара, проданного в каждом месяце | ||||
| Январь | Февраль | Март | Апрель | Май | |
| Холодильники | |||||
| Газовые плиты | |||||
| Посудомоечные машины | |||||
| Стиральные машины | |||||
| Пылесосы |
Определить общее количество некоторых видов товаров, поступивших в магазины некоторой фирмы (на листе 1 данные одного магазина, на листе 2 – второго).
Вариант 10.
| Фам. сотр-ка | Выплаты | |||||
| Оклад | Премия | Компенсация питания | Компенсация проезда | Подоходный налог | Итого | |
| Сынов | ||||||
| Пупкин | ||||||
| Бобылев | ||||||
| Анисина | ||||||
| Дуров | ||||||
| Сидоров |
Определить суммарные выплаты для некоторых сотрудников фирмы за два месяца (на листе 1 данные по выплатам одного месяца, на листе 2 – второго).
Контрольные вопросы.
1. Для чего предназначен инструмент Консолидация?
2. Какие методы консолидации существуют? В каких случаях может быть применен каждый из них?
3. Можно ли консолидировать данные не по всем категориям? Если – да, то что для этого нужно сделать?
4. Какие действия нужно выполнить, чтобы после консолидации данных при каждом изменении в исходных диапазонах автоматически изменялся и результат?
5. Как удалить результаты ошибочной консолидации?
Лабораторная работа 8. Сводные таблицы
Цель работы: изучение средств создания и модификации сводных таблиц.
Теоретические сведения
Сводные таблицы используются для группировки данных по одному или нескольким критериям, при этом производятся различные расчеты по этим данным: суммирование, нахождение среднего и т.д. Можно рассматривать сводную таблицу как средство группирования данных по различным вторичным ключам с последующими расчетами по требуемым параметрам.
Создание сводной таблицы
Рис. 22. Вставка сводных таблицМодификация сводной таблицы
Для модификации сводной таблицы нужно просто установить курсор мыши в одну из ее ячеек и сразу будет открыто окно Конструктора сводной таблицы.
Задание к работе
1. Исходные данные для лабораторной работы № 8 те же, что и для лабораторной работы № 5.
2. Построить сводные таблицы в соответствии с заданиями своего варианта и по возможности разместить их на одном листе с исходной таблицей.
3. Показать результат преподавателю.
Варианты индивидуальных заданий
Вариант 1.
1. Определить среднюю стоимость для автомобилей каждой марки.
2. Определить минимальную стоимость для автомобилей каждого цвета с дополнительной возможностью фильтрации результатов в соответствии с маркой автомобиля.
3. Сформировать сводную таблицу, группирующую автомобили по цветам и по каждому автомобилю выводить его стоимость. Определить максимальную стоимость автомобилей каждого цвета.
4. Сформировать сводную таблицу, содержащую в строках информацию о марках автомобилей, в столбцах – о цвете автомобилей, а на пересечении строк и столбцов – год выпуска. Определить самые «молодые автомобили».
Вариант 2.
1. Определить среднее количество осадков каждого вида.
2. Определить минимальную силу ветра каждого направления с дополнительной возможностью фильтрации результатов в соответствии с видом осадков.
3. Сформировать сводную таблицу, группирующую направления ветра по виду осадков и по каждому направлению ветра выводить его силу. Определить максимальную силу ветра при каждом виде осадков.
4. Сформировать сводную таблицу, содержащую в строках информацию о направлении ветра, в столбцах – вид осадков, а на пересечении строк и столбцов – температуру. Определить среднюю температуру при данных условиях.
Вариант 3.
1. Определить средний результат по каждому виду спортивных нормативов.
2. Определить минимальную оценку каждого студента с дополнительной возможностью фильтрации результатов в соответствии с годом рождения.
3. Сформировать сводную таблицу, группирующую студентов по виду спортивных нормативов и по каждому студенту выводить его балл. Определить максимальный балл по каждому виду спортивного норматива.
4. Сформировать сводную таблицу, содержащую в строках информацию о спортивном нормативе, в столбцах – фамилии студентов, а на пересечении строк и столбцов – результаты. Определить средний результат для каждого студента и норматива.
Вариант 4.
1. Определить количество фирм-продавцов из каждой страны.
2. Определить минимальную цену каждого товара с дополнительной возможностью фильтрации результатов в соответствии со списком стран-продавцов.
3. Сформировать сводную таблицу, группирующую товары по странам-продавцам и по каждому товару выводить его количество. Определить сумму товаров по каждой стране-продавцу.
4. Сформировать сводную таблицу, содержащую в строках информацию о товаре, в столбцах – фирме-покупателе, а на пересечении строк и столбцов – цену. Определить среднюю стоимость каждого товара.
Вариант 5.
1. Определить среднюю оценку за выполнение расчетных заданий по каждой кафедре.
2. Определить минимальную оценку каждого студента с дополнительной возможностью фильтрации результатов в соответствии с темой расчетного задания.
3. Сформировать сводную таблицу, группирующую студентов по теме расчетного задания и по каждому студенту выводить его балл. Определить максимальный балл по каждому виду расчетного задания.
4. Сформировать сводную таблицу, содержащую в строках информацию о научном руководителе, в столбцах – фамилии студентов, а на пересечении строк и столбцов – оценки. Определить средний балл для каждого студента и научного руководителя.
Вариант 6.
1. Определить средний тираж книг для каждого издательства.
2. Определить минимальную цену книг каждого жанра с дополнительной возможностью фильтрации результатов в соответствии с местом издания.
3. Сформировать сводную таблицу, группирующую жанры книг по месту издания и по каждому жанру выводить его цену. Определить максимальный цену по каждому месту издания.
4. Сформировать сводную таблицу, содержащую в строках информацию о жанре книги, в столбцах – место издания, а на пересечении строк и столбцов – количество страниц. Определить среднее количество страниц по каждому виду информации.
Вариант 7.
1. Определить средний стаж для сотрудников каждого отдела.
2. Определить минимальный стаж для каждой должности с дополнительной возможностью фильтрации результатов в соответствии с полом сотрудника.
3. Сформировать сводную таблицу, группирующую должности по полу сотрудников и по каждой должности выводить количество детей сотрудников. Определить максимальное количество детей для сотрудников каждой должности.
4. Сформировать сводную таблицу, содержащую в строках информацию об отделах, в столбцах – о должностях, а на пересечении строк и столбцов – стаж. Определить средний стаж сотрудников.
Вариант 8.
1. Определить общее количество книг, прочитанных каждым читателем.
2. Определить минимальный стоимость книги для каждого читателя с дополнительной возможностью фильтрации результатов в соответствии с полом фамилией автора книги.
3. Сформировать сводную таблицу, группирующую читателей по адресу (улице) проживания и для каждого читателя вывести общее количество, прочитанных книг. Определить самую читающую улицу.
4. Сформировать сводную таблицу, содержащую в строках фамилии авторов книг, в столбцах – фамилии читателей, а на пересечении строк и столбцов – стоимость прочитанных книг. Определить среднюю стоимость книг.
Вариант 9.
1. Определить количество товаров, собранных в каждой стране.
2. Определить минимальную цену каждого вида товара с дополнительной возможностью фильтрации результатов в соответствии со списком производителей.
3. Сформировать сводную таблицу, группирующую товары по фирмам производителям и по каждому товару выводить его количество. Определить сумму товаров по каждой фирме производителю.
4. Сформировать сводную таблицу, содержащую в строках информацию о товаре, в столбцах – стране, осуществляющей сборку, а на пересечении строк и столбцов – цену. Определить среднюю стоимость каждого товара.
Вариант 10.
1. Определить средний оклад для сотрудников каждого отдела.
2. Определить минимальную премию для каждой должности с дополнительной возможностью фильтрации результатов в соответствии с полом сотрудника.
3. Сформировать сводную таблицу, группирующую должности по полу сотрудников и по каждой должности выводить оклад сотрудников. Определить максимальное оклад для сотрудников каждой должности.
4. Сформировать сводную таблицу, содержащую в строках информацию об отделах, в столбцах – о должностях, а на пересечении строк и столбцов – оклад. Определить средний оклад сотрудников.
Контрольные вопросы.
1. Для чего предназначен инструмент сводные таблицы?
2. Какие поля можно задать при формировании сводной таблицы?
3. Можно ли производить изменение данных в сводной таблице? Если – да, то что для этого нужно сделать?
4. Можно ли в сводной таблице фильтровать данные? Если – да, то что для этого нужно сделать?
Лабораторная работа 9. Составление условий в электронных таблицах, условное форматирование
Цель работы: закрепить навыки работы с логическими функциями, научиться выполнять условное форматирование.
Теоретические сведения
Условное форматирование
Самая простая логика. Если содержимое ячейки больше (меньше, равно, не равно и т.д.) определенного значения, то – срабатывает определенное… - Выделите ячейки, которые должны автоматически менять свой цвет - Выберите на вкладке Главная в группе Стили → Условное форматирование → Правила для выделенных ячеек и…Форматирование с использованием гистограммы
- Выделите диапазон ячеек. - Щелкните по кнопке [Условное форматирование], в галерее выберите команду…Форматирование с использованием набора значков
- Выделите диапазон ячеек. - Щелкните по кнопке [Условное форматирование], в галерее выберите команду…Управление правилами условного форматирования
1. Щелкните по кнопке [Условное форматирование]/ 2. В окне Диспетчер правил условного форматирования в поле списка «Показать… 3. Выделите правило, которое надо изменить и нажмите кнопку [Изменить правило].Функция ЕСЛИ
- логическое выражение, задающее условие (logical_test); - значение, выводимое в случае выполнения условия (value_if_true); - значение, выводимое в случае невыполнения условия (value_if_false).Задание к работе
1. Составьте электронную таблицу следующего вида, для решения любого квадратного уравнения с помощью дискриминанта:
| Решение квадратного уравнения | ||
| Коэффициент a | Значение дискриминанта | |
| Коэффициент b | ||
| Коэффициент c | ||
| Корень x1 | ||
| Корень x2 |
Запишите в соответствующие ячейки формулы и установите условия для вычисления дискриминанта и корней уравнения со ссылкой на ячейки с исходными данными. Проверьте работу таблицы на следующих примерах:
| Два корня (1; 0,2) |
| Один корень (0,25) |
| Корней нет |
2. В таблицах, заполненных в лабораторной работе №5, применив условное форматирование, выделите:
- Зеленым цветом – максимальное значение,
- Красным цветом – минимальное значение,
- Желтым цветом – значения, которые больше среднего.
3. Решить задачи согласно варианту индивидуального задания путем построения электронной таблицы. Исходные данные для заполнения таблицы подобрать самостоятельно (не менее 10 строк).
Варианты индивидуальных заданий
Вариант 1
Торговый агент получает процент от суммы совершенной сделки. Если объем сделки до 3000, то 5%; если объем до 10000, то 2%; если выше 10000, то 1,5%. Таблица должна хранить даты совершения сделки, фамилии клиентов, объем сделки и процент, получаемый агентом.
Вариант 2
В доме проживает 10 жильцов. Подсчитать, сколько каждый из них должен платить за электроэнергию и определить суммарную плату для всех жильцов. Известно, что 1 кВт/ч электроэнергии стоит m рублей, у некоторых жильцов установлены электроплиты, при этом они имеют скидку за электроэнергию в размере 30%. Таблица должна хранить фамилии жильцов, отметку об установке электроплиты, количество потребленных кВТ, сумму к оплате.
Вариант 3.
Торговый склад производит уценку хранящейся на складе продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг 10 месяцев, то – 1,5 раза. Получить ведомость уценки товара, которая должна включать следующую информацию: наименование товара, срок хранения, цена товара до уценки, цена товара после уценки.
Вариант 4
В сельскохозяйственном кооперативе на сборе урожая помидоров работают 10 сезонных рабочих. Оплата труда производится по количеству собранных овощей. Дневная норма сбора составляет k килограммов. Сбор 1 кг помидоров стоит m рублей. Сбор каждого килограмма сверх нормы оплачивается в 2 раза дороже. Сколько денег в день получит каждый рабочий за собранный урожай?
Вариант 5
Автоматизировать начисление стипендии студентам (группа – 10 человек) по итогам летней сессии. Количество экзаменов – 5, баллы – от 2 до 5. Стипендия начисляется в размере 1000 руб., если все экзамены сданы с оценкой не ниже 3. При сдаче всех экзаменов с оценками не ниже 4 баллов выплачивается надбавка 25%, не ниже 5 баллов – надбавка 50%.
Вариант 6
В доме проживает 10 жильцов. Подсчитать, сколько каждый из них должен платить за водоснабжение и определить суммарную плату для всех жильцов. Известно, что 1 м3 горячей воды стоит 40 руб., 1 м3 холодной воды стоит 12 руб., водоотведение обходится по 9 руб. за 1 м3. В некоторых квартирах живут один или несколько льготников. При этом, если общее количество проживающих в квартире больше количества льготников, то такие жильцы имеют скидку 10%, если льготников больше – 30%. Таблица должна хранить фамилии жильцов, общее количество жильцов в квартире, количество жильцов, имеющих льготу, расход воды и сумму к оплате.
Вариант 7
В сельскохозяйственном кооперативе на сборе урожая картофеля работают 10 сезонных рабочих. Оплата труда производится по количеству собранных овощей. Дневная норма сбора составляет k килограммов. Сбор 1 кг картофеля стоит m рублей. Сбор каждого килограмма сверх нормы оплачивается в 2 раза дороже. Сколько денег в день получит каждый рабочий за собранный урожай?
Вариант 8
Торговый склад производит уценку хранящейся на складе продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается на 40%, а если срок хранения превысил 6 месяцев, но не достиг 10 месяцев, то – 25%. Получить ведомость уценки товара, которая должна включать следующую информацию: наименование товара, срок хранения, цена товара до уценки, цена товара после уценки.
Вариант 9
Торговый агент получает процент от суммы совершенной сделки. Если объем сделки до 5000, то 7%; если объем до 10000, то 4%; если выше 10000, то 2%. Таблица должна хранить даты совершения сделки, фамилии клиентов, объем сделки и процент, получаемый агентом.
Вариант 10
Автоматизировать расчет стоимости общежития студентами (группа – 10 человек) по итогам сессии. Количество экзаменов – 5, баллы от 2 до 5. Стоимость общежития 300 руб./семестр, если все экзамены сданы с оценкой не ниже 3. При сдаче всех экзаменов с оценками не ниже 4, студент получает скидку 5%, не ниже 5 – скидку 10%. Если есть задолженности, то стоимость оплаты за общежитие возрастает на 7%.
Лабораторная работа 10. Текстовые функции, функции даты и времени.
Цель работы: получить представление о текстовых функциях, освоить особенности измерения времени расчетах, выработать навыки решения задач, связанных с учетом даты или времени.
Теоретические сведения
Функция ТЕКСТ преобразует число в текстовую строку с заданным форматом. Синтаксис: =ТЕКСТ(значение;формат). Аргумент значение может быть любым числом, формулой или ссылкой на ячейку.… Функция РУБЛЬ преобразует число в строку. Однако РУБЛЬ возвращает строку в денежном формате с заданным числом…Задание к работе
Задание 1
1. Откройте новую книгу в Excel.
2. В ячейку В1 с использованием функций введите текущую дату. В ячейку В2 с использованием формулы введите дату и время последнего изменения данных на листе.
3. Занятие продолжается с 9:30 до 11:00 без перерыва. Какова продолжительность занятия в минутах и сколько академических часов (45 мин) оно продолжается?
Указание: в качестве формата ячеек укажите общий, а в строке тип введите значение [м].
4. Используя формулы, представленные на рис. 43. Для текущей даты вычислить:
- Порядковый номер дня с начала года;
- Сколько осталось дней до конца недели, до конца месяца.
Рис. 43. Работа с формулами Дата
5. Получите даты с шагом два месяца для начальной даты 31/12/прошлого года и конечной даты 31/12/текущего года.
6. Получите рабочие дни для марта текущего года.
7. Сосчитайте количество рабочих дней в марта текущего года. Проверьте по календарю.
8. Введите в столбец А даты от 1 марта до 30 апреля текущего года. Наложите на диапазон условный формат, чтобы даты, на которые приходятся суббота и воскресенье, отображались полужирным красным шрифтом.
Указание: Выделить блок с датами. На вкладке Условное форматирование выбрать Создание правила форматирования - Использовать формулу для форматирования выделенных ячеек, ввести формулу =ИЛИ(ДЕНЬНЕД(A1;2)=6;ДЕНЬНЕД(A1;2)=7).
Задание 2. Расчет распределения прибыли по итогам работы за год.
В конце отчетного года организация имеет некоторую величину денежных средств N, которую необходимо распределить между сотрудниками в качестве премии. Распределение производится на основе оклада сотрудника и в соответствии со стажем его работы в данной организации.
1. Требуется создать таблицу, состоящую из граф: № п/п (1), ФИО сотрудника (2), Дата приема на работу (3), Стаж работы (4), Оклад сотрудника (5), Модифицированный оклад (6), Премия сотрудника(7).
2. Данные граф 1, 2, 3, 5 задайте самостоятельно (не менее 10 строк).
3. Значение графы 4 рассчитайте с помощью функций даты и времени ДНЕЙ360 или ГОД и СЕГОДНЯ.
4. Для приведения сотрудников к одному знаменателю рассчитывается промежуточный показатель – модифицированный оклад, зависящий от стажа работы сотрудника (если стаж работы не менее пяти лет, то размер модифицированного оклада равен двойному окладу, в противном случае модифицированный оклад равен окладу). При расчете графы 6 используйте функцию ЕСЛИ.
5. Рассчитайте коэффициент распределения (К), как отношение всей суммы премиальных средств N к сумме всех модифицированных окладов. Данный коэффициент показывает, сколько рублей премии приходится на рубль модифицированного оклада.
6. Вычислить премию каждого сотрудника путем умножения величины модифицированного оклада на коэффициент распределения. Полученную премию следует округлить до целого. При расчете графы используйте функцию ОКРУГЛ и абсолютную ссылку.
[1] В случае появления сообщения о том, что пакет статистического анализа не установлен на компьютере и предложения установить его, нажмите кнопку <Да>
[2] Если этот флажок не доступен, необходимо сначала снять защиту с книги, а затем снять этот флажок
[3] Для этого используйте настраиваемую сортировку.
– Конец работы –
Используемые теги: методические, указания, выполнению, лабораторных, самостоятельных, работ, информатика, вычислительная, техника0.132
Если Вам нужно дополнительный материал на эту тему, или Вы не нашли то, что искали, рекомендуем воспользоваться поиском по нашей базе работ: Методические указания по выполнению лабораторных и самостоятельных работ – «Информатика и вычислительная техника»
Что будем делать с полученным материалом:
Если этот материал оказался полезным для Вас, Вы можете сохранить его на свою страничку в социальных сетях:
| Твитнуть |
Хотите получать на электронную почту самые свежие новости?
Подпишитесь на Нашу рассылку
Реклама
Информация в виде рефератов, конспектов, лекций, курсовых и дипломных работ имеют своего автора, которому принадлежат права. Поэтому, прежде чем использовать какую либо информацию с этого сайта, убедитесь, что этим Вы не нарушаете чье либо право.
© copyright 1999 - 2024 allRefs.net. Все права защищены. Страница сгенерирована за: 0.243 сек.
Новости и инфо для студентов