Построение графика функции с двумя и более условиями.

Теоретические сведения

 

Создание и сохранение таблицы, ввод и корректировка данных, числовые форматы, вычисления, оформление и т.д. Работа с таблицами многоразового использования: задание постоянной информации и формул, закрепление областей, защита

 

Электронные таблицы (ЭТ) - специальные пакеты программ для решения задач, исходные данные и результаты вычислений в которых можно представить в виде таблиц. Преимущество использования ЭТ состоит в возможности решения таких задач без помощи программиста.

Документ Excel называется рабочей книгой. Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет имя.

Рабочий лист состоит из строк и столбцов, строки пронумерованы цифрами от 1 до 65536, столбцы – латинскими буквами от A до IV (256). С помощью заголовков строк (серая область с номером в левой части экрана) или столбцов.

На пересечении строки и столбца находится ячейка, которая служит для ввода данных (текста, формулы, числа).

Для указания на конкретную ячейку используется адрес ячейки, состоящий из заголовков столбца и строки, на пересечении которых находится ячейка, например A1 или K234.

Диапазон– группа смежных ячеек в строке или столбце. Для ссылки на диапазон необходимо задать адрес первой и последней ячеек диапазона через двоеточие, например A1:D4. Выделить диапазон можно протягиванием указателя от одной угловой ячейки до другой.

Отдельная ячейка может содержать данные одного из трех типов: текст, число, формула. Тип данных определяется автоматически. Для ввода данных нужно выделить ячейку и набрать текст, не дожидаясь появления курсора. Текст можно также вводить сразу в строке формул. Вводимые данные можно:зафиксировать, нажав клавишу Enter, отменить –ESC, удалить Delete.

 
 

 

Форматировать отдельную ячейку (или выделенный диапазон) можно в окне Формат Ячеек, которое можно вызвать,щелкнув кнопку вызова диалогового окна . Окно имеет следующие вкладки:

 

Число предлагает различные числовые форматы данных
Выравнивание позволит записывать текст в ячейке в несколько строк, под углом, объединять ячейки, центрировать текст по горизонтали и вертикали
Шрифт настраивает формат шрифта
Граница задает внешний вид границы
Заливка предлагает способы заливки
Защита предлагает защитить ячейки от изменения и скрыть формулы

 

Помимо большого количества встроенных форматов, пользователь может создавать и свои.

 

 

Быстро производить форматирование помогут кнопки на вкладке Главная в группе Число.

Формулы.Формула– это запись математической формулы по правилам MS Excel. Формула всегда начинается со знака равенства.

 

 

Формула может содержать один или несколько адресов ячеек, чисел и арифметических знаков и специальных функций. Например, если вы хотите определить среднее арифметическое трех чисел, содержащихся в ячейках А1, В1 и С1, вам потребуется записать формулу: =СРЗНАЧ(A1:C1).

Адреса ячеек могут вводиться в формулу двумя способами: непосредственным набором или щелчком по требуемой ячейке.

Ссылки на ячейки.Формула может содержать ссылкина адреса ячеек, содержимое которых используется в вычислениях. Таким образом, ячейка, содержащая формулу, становиться зависимой,а ячейка ссылающаяся на формулу в другой ячейке – влияющей. Наглядно отобразить зависимости позволяет пункт Влияющие ячейки на вкладке Формулыв группе Зависимости формул.

Различают три типа ссылок:относительную,абсолютнуюи смешанную.

Относительнаяссылка – обозначение ячейки в виде номера строки и столбца: A1. При этом адрес ячейки в формуле вычисляется относительно позиции формулы и меняется при копировании. По умолчанию в формулах используется относительнаяссылка.

Абсолютная ссылка– обозначение ячейки в виде номера строки и столбца: $A$1. Используется в тех случаях, когда не требуется изменения адреса ячейки при копировании или перемещении формулы. На абсолютность ссылки указывает символ $ (клавиша F4), "закрепляющий" как номер строки, так и номер столбца.

Смешанная ссылка – обозначение ячейки в виде номера строки и столбца: $A1 и A$1. Она используется, когда необходимо зафиксировать либо номер стобца, либо строки.

Копирование содержимого ячеек.Копировать, перемещать данные можно методом перетаскивания или через буфер обмена.

Чтобы выделить целый столбец (строку), щелкните на его (ее) заголовке. Для выделения группы соседних столбцов или строк перетащите указатель мыши по их заголовкам.

Метод перетаскивания: просто перетащите ячейку (диапазон), удерживая нажатой правую клавишу мыши. Тогда по завершении операции появится специальное меню, в котором можно выбрать конкретную выполняемую операцию.

Применение буфера обмена. Передача данных через буфер обмена имеет в программе Excel свои особенности. Вставка данных в рабочий лист возможна лишь немедленно после их помещения в буфер. Попытка выполнить любую другую промежуточную операцию приводит к очищению содержимого буфера обмена.

Автоматизация ввода.Excel предоставляет для автоматизации ввода автозавершение, автозаполнение числами и формулами.

Автозавершение. Служит для автоматизацииввода текстовых данных. В ходе набора текста в очередную ячейку Excel проверяет соответствие введенных символов символам, имеющимся в этом столбце выше, при однозначном совпадении текст автоматически дополняется. Клавиша ENTER подтверждает ввод, дальнейший набор – отменяет. Пустая ячейка в столбце преравает работу средства.

Автозаполнение числами позволяет копировать содержимое ячейки в другие ячейки той же строки или того же столбца с помощью перетаскивания маркера заполнения ячейки. Если ячейка содержит число, дату или период времени, который может являться частью ряда, то при копировании происходит приращение ее значения. Однако для определения значения приращения его необходимо указать программе. Для этого надо ввести два последовательных значения ряда, выделить две ячейки с введенными значения и затем воспользоваться функцией автозаполнения.

Чтобы точно сформулировать условия заполнения ячеек, следует дать команду Главная Редактирование Заполнить Прогрессия.

Использование стандартных функций.Стандартные функции используются только в формулах. Вызов функции состоит в указании имени функции и в скобках списка параметров через знак ";". Аргументами функции могут служить числа, ссылки на ячейки, диапазоны, имена, текстовые строки в кавычках и вложенные функции. Например, требуется значение ячейки A3 сложить с числом 5, полученный результат поделить на 3 и умножить на значение ячейки B2:=(А3+5)*В2/3.

Различают несколько категорий функций:

 

 

Практическая часть

 

Задание 5.1. Создать в Excel на основании документов, две таблицы, разместив их на разных листах. Фамилии и инициалы произвольные (12 фамилий, своя (Иванов) первая). Листы переименовать на Успеваемость и Список соответственно. Рабочую книгу сохранить под своим именем ПСФ_Фамилия.xls. в своей папке.

Примечание: Вид оплаты: 1 – обучение за счет бюджета; 2 – платное обучение.

Выполнение.

Для построения таблицы Успеваемость необходимо выполнить следующие операции:

1) Спроектировать выходную форму документа. Для этого присвоить каждой его графе соответствующий столбец, а каждой строке документа – соответствующую строку электронной таблицы.

2) Переименовать Лист1 на Успеваемость, выполнив команду Формат Лист Переименовать.

3) Ввести заголовок таблицы в ячейку с адресом А1. Для расположения заголовка по центру таблицы выделить диапазон ячеек А1:F1 и выполнить команду Главная Формат Формат Ячейки-вкладка Выравнивание по горизонтали установить – по центру выделения.

4) Ввести названия столбцов таблицы. Для этого:

• Объединить ячейки А3 и А4, для чего выделить их, и выполнить команду Формат Ячейки вкладка Выравнивание, установить флажок – объединение ячеек. Для расположения названия первого столбца таблицы по центру выделенного диапазона в этом же окне установить выравнивание по центру (вертикальное и горизонтальное). Затем ввести название "Группа".

• Аналогичные действия выполнить для ввода названия второго столбца таблицы – "№ зачетки", объединив ячейки В3 и В4.

• Ввести в ячейку С3 заголовок "Экзаменационные оценки" и расположить его по центру трех столбцов. Для этого выделить диапазон ячеек С3:Е3 и выполнить команду Формат Ячейки вкладка Выравнивание, установить по горизонтали – по центру выделения.

• В ячейки С4, D4, Е4 ввести соответственно названия столбцов: Математика, Информатика, Философия.

• Для ввода названия последнего столбца "Средний балл студента" дополнительно к уже описанным действиям необходимо установить флажок – переносить по словам, чтобы текст распределился внутри выделенного диапазона в несколько строк.

• Аналогичным образом объединить ячейки A17 и B17 и ввести текст "Средний балл по дисциплине".

В результате получим таблицу:

 

 

5) Ввести формулы в соответствующие ячейки таблицы:

Установить режим отображения формул в таблице, выполнив команду Формулы Зависимости Формул Показать Формулы.

Вставить функцию вычисления среднего балла первого студента. Для этого выполнить действия:

• Поместить курсор в ячейку F5;

•выполнить команду Формула Автосумма Среднее.

•на втором шаге задать аргументы функции. Для этого установить курсор в поле := СРЗНАЧ(А5:E5). и ввести адрес диапазона ячеек С (английскими символами) либо выделить мышью диапазон С5:E5;

•Нажать кнопку [ОК].

Скопировать формулу из ячейки F5 в диапазон ячеек F6:F16.

Для вычисления среднего балла по математике курсор установить в ячейку С17 и ввести формулу =СРЗНАЧ(С5:С16), а затем скопировать ее в ячейки D17 и Е17. Для получения результата с одним десятичным знаком выделить диапазоны ячеек с формулами и выполнить команду Формат Ячейки вкладка Число. Затем установить числовой формат с числом десятичных знаков – 1.

6) Отформатировать таблицу. Для этого выделить таблицу (диапазон A3:F17) и провести горизонтальные и вертикальные линии, выполнив команду Главная Формат Формат Ячеек вкладка Граница. В открывшемся диалоговом окне выбрать тип и цвет линии, внешние и внутренние границы.

7) Защитить таблицу.Таблица должна быть защищена таким образом, чтобы пользователь мог вводить в нее только исходные данные, но не иметь доступ к ячейкам, значение которых не должно изменяться (шапка таблицы, формулы). В данной таблице область исходных данных расположена в диапазоне А5:Е16. Для этого необходимо:

• выделить диапазон ячеек А5:Е16;

• выполнить команду Формат Ячейки вкладка Защита– убрать флажок Защищаемая ячейка;

• выполнить команду Главная Формат Ячейки Защитить лист.

8) Закрепить шапку таблицы для фиксации заголовков столбцов, которые будут оставаться на экране при прокрутке листа. Для этого:

• установить курсор в ячейку, находящуюся в левом верхнем углу под шапкой таблицы, т.е. в ячейку А5;

• выполнить команду Окно Закрепить области.

9) Заполнить таблицу исходными данными, которые приведены в документе. Для расположения данных в столбцах таблицы по центру необходимо выделить ячейки соответствующего столбца и выполнить команду Формат Ячейки вкладка Выравнивание-по горизонтали установить – по центру.

Таблица в режиме формул выглядит:

 

10) Установить режим отображения на экране значений, выполнив команду Формулы Зависимости Формул – снять флажок Показать Формулы.

11) Сохранить таблицу на диске под именем ПСФ_Иванов.xlsх.

Таблица Список – "Список студентов группы ПМФ 1-го курса" формируется аналогичным образом на листе Список. Необходимо создать ее самостоятельно в соответствии с приведенной формой.

 

 

Контрольные вопросы

 

1. Копирование и перемещение методом перетаскивания?

2. Как копировать и перемещать данные через буфер обмена?

3. Что такое автозавершение?

4. Как произвести автозаполнение?

5. Использование стандартных функций.

6. Категории функций.

7. Относительный и абсолютный адрес ячейки.

8. Настройки числовых форматов.

9. Как защитить таблицу?

10. Формулы в Excel.

 

Варианты заданий

 

Заполнить таблицы задания 5.1 по принципу:

– список начинается со своей фамилии;

– группа 1130 + n, где n – номер по журналу.

– номер зачетки – поставить № номер своей зачетки, остальные – произвольно.

 

Построение диаграмм в Excel

 

Теоретические сведения

 

Диаграмма представляет собой вставной объект, внедренный на один из листов рабочей книги. Диаграмма сохраняет связь с данными, на основе которых была построена, и при их изменении немедленно меняет свой вид.

Для создания большинства диаграмм, например для линейчатых диаграмм и гистограмм, можно воспользоваться данными из строк и столбцов листа. Однако для некоторых типов диаграмм, например, для круговых и пузырьковых, требуются специально организованные данные.

Выбор типа диаграммы.На первом этапе работы выбирается форма диаграммы.

Втрой этап служит для выбора данных.

Третий этап состоит в выборе оформления диаграммы. На вкладках задаются:

• заголовок диаграммы, подписи осей (вкладка Заголовки);

• отображение и маркировка осей координат (вкладка Оси);

• отображение сетки дополнительных линий (вкладка Линии сетки);

• описание построенных графиков (вкладка Легенда);

• отображение надписей, соответствующих отдельным элементам данных на графике (вкладка Подписи данных);

• представление данных, использованных при построении графика, в виде таблицы (вкладка Таблицы данных);

Размещение диаграммы.Диаграмма может располагаться на этом же или отдельном листе.

Редактирование диаграммы.Готовая диаграмма состоит из набора отдельных элементов (ряды данных, оси, подписи, область построения и пр.), каждый из которых можно изменить.

 

Практическая часть

 

1. На вкладке Вставкав группе Диаграммы выполните одно из следующих действий.

– Выберите вид диаграммы и затем, подвид диаграммы, который необходимо использовать.

– Для просмотра доступных видов диаграмм выберите вид диаграммы и затем, щелкните пункт Все виды диаграмм для вызова диалогового окна Вставка диаграммы, используйте стрелки для просмотра имеющихся видов и подвидов диаграмм, и затем выберите нужный.

Диаграмма размещается на листе в виде внедренной диаграммы. Если необходимо поместить диаграмму на отдельный лист диаграммы, то измените ее размещение:

1. Щелкните внедренную диаграмму или лист диаграммы для отображения инструментов для работы с диаграммой.

2. На вкладке Конструктор в группе Расположение нажмите кнопку Переместить диаграмму.

3. В разделе Разместить диаграмму выполните одно из следующих действий:

– Для вывода диаграммы на лист диаграммы выберите параметр на отдельном листе.

– Чтобы заменить предложенное имя диаграммы, введите новое имя в поле на отдельном листе.

– Для вывода диаграммы в виде внедренной диаграммы на листе выберите параметр на имеющемся листе и выберите лист в поле на имеющемся листе.

Чтобы быстро создать диаграмму на основе типа диаграммы по умолчанию выберите данные, которые следует использовать для ее построения, и нажмите клавиши ALT+F1 или F11. При нажатии клавиш ALT+F1 диаграмма будет отображена как внедренная диаграмма; при нажатии клавиши F11 — на отдельном листе диаграммы.

Если при создании диаграмм часто используется определенный тип диаграмм, можно установить этот тип в качестве типа диаграмм по умолчанию. После выбора типа и вида диаграммы в диалоговом окне Вставка диаграммы нажмите кнопку Сделать стандартной.

При создании диаграммы открывается доступ к инструментам для работы с диаграммой: отображаются вкладки Конструктор, Макет и Формат. Команды этих вкладок можно использовать для изменения представления данных на диаграммах. Например, вкладка Конструктор используется для отображения рядов данных по строкам или по столбцам, внесения изменений в исходные данные, изменения размещения диаграммы, изменения типа диаграммы, сохранения диаграммы в качестве шаблона или выбора предварительно определенных параметров макета и форматирования. Вкладка Макет используется для изменения таких элементов диаграммы, как заголовки диаграмм и подписи данных, использования инструментов рисования, а также добавления к диаграмме текстовых полей и рисунков. Вкладка Формат позволяет добавлять заливку цветом, изменять тип линий или использовать специальные эффекты.

Для построения используют группа Диаграммы на вкладке Вставкаглавного меню.

Задание 5.2. Построить объемную круговую диаграмму, иллюстрирующую сведения о средних баллах, полученных студентами в экзаменационную сессию.

Выполнение.

Основными элементами для построения диаграммы являются: область диаграммы, область построения диаграммы, ряды данных, оси координат, заголовки, легенда, линии сетки, подписи данных.

Для построения диаграммы необходимо выполнить следующие действия:

1) Выделить в таблице диапазон ячеек с исходными данными (область данных диаграммы). Для примера на листе Успеваемость(задание 5.1.) выделить диапазон ячеек F5:F16 (на круговой диаграмме можно отобразить значения только одного ряда данных).

Если выделен диапазон ячеек, содержащий более 1 строки или более 1 столбца, то Excel интерпретирует строки или столбцы как отдельные ряды, содержащие элементы данных.

Замечание. Для выделения несвязанных диапазонов ячеек таблицы необходимо выполнить эти действия при нажатой клавише Ctrl.

2)Вставить диаграмму, выполнив команду: вкладке Вставка Диаграммы Круговая.

 

 

В списке выбрать – Объемная круговая. Нажать кнопку [Ok]. Появится следующая диаграмма в заданном месте рабочего листа.

 

 

3)Определить названия рядов и подписи категорий.

– Перейти на вкладку Работа с диаграммами.

– Выбрать вкладку Выбрать данные. Откроется диалоговое окно Выбор источника данных.

– Позиционировать курсор в поле Изменить, щелкнуть по кнопке свертывания, находящейся в правой части поля, и выделить ячейки С5:С16 на листе Список с фамилиями студентов для задания текста легенды.

– Выполнить щелчок по кнопке свертывания. В поле Подписи категорий появится ссылка: =Список!$C$5:$C$16.

– Нажать кнопку [OK].

4) Задать заголовок диаграммы, указать расположение легенды, отобразить подписи значений рядов.Для этого выполнить действия:

– перейти на вкладку Макет Название диаграммы;

– в поле Название диаграммы выбрать позицию Над диаграммой и ввести текст Сравнительный анализ среднего баллауспеваемости студентов по фамилиям;

– перейти на вкладку Легенда, включить параметр Добавить легенду и установить флажок Справа для указания расположения легенды;

– перейти на закладку Подписи данных;

– в группе переключателей выбрать параметр У вершины внутри для отображения на диаграмме значения среднего балла каждого студента;

– нажать левую клавишу мыши.

При необходимости можно откорректировать размер диаграммы с помощью маркеров размера.

Вид построенной диаграммы представлен на рисунке.

 

 

После активизации диаграммы (щелчка левой клавиши мыши внутри диаграммы) можно выделить любой из ее элементов для корректировки, которая заключается в удалении либо изменении свойств элемента. Изменение вида элемента (цвета фона, выбор узора, использование рамок и т.д.) или шрифта (типа, стиля и размера используемых символов текста) выполняется с помощью форматирования. Окно форматирования для любого элемента диаграммы можно открыть с помощью двойного щелчка мыши на нем.

Задание 5.3. Построить диаграмму, отображающие оценки, полученные студентами по всем предметам в экзаменационную сессию, используя таблицы Задания 5.1.; выбрать тип диаграммы Гистограмма. Для области диаграммы установить размер шрифта - 8, для оси категорий изменить способ выравнивания подписей.

Выполнение.

Указываем диапазон ячеек, в котором располагаются данные:

– установить курсор в нужном месте листа;

– выделить на листе Успеваемость диапазон ячеек С5:Е16, содержащий оценки студентов по трем предметам;

– рядом сВставка Диаграммынажмите на Кнопку вызова диалогового окна и в окне выберите Гистограмма Гистограмма с группировкой.

Отображаемподписи значений элементов ряда:

– выполнить команду Конструктор Выбрать данные;

– в окне Изменить щелкнуть по кнопке свертывания, находящейся в правой части поля, и выделить ячейки С5:С16 на листе Список с фамилиями студентов для задания;

– нажать кнопку [ОК].

Отображаемназвания рядов в легенде:

– в списке Элементы легенды окна Выбор источника данных выделить значение Ряд 1, в окне Изменить щелкнуть по кнопке свертывания и ввести текст Оценка по математике. Аналогично для рядов Ряд 2 и Ряд 3 ввести текст Оценка по информатике и Оценка по химии соответственно;

Отображаем заголовок диаграммы:

– выполнить команду Макет Название диаграммы;

– ввести текст в поле Название диаграммы: Сравнительный анализ успеваемости студентов, в поле Название осейв поле Название основной горизонтальной оси ввести Фамилии, в поле Название основной вертикальной осиПовернутое название: Оценки;

– нажать на кнопку [ОК]. Получим следующую диаграмму:

 

 

Задание 5.4. Построить график функций .

Выполнение.

Для построения графика функции необходимо сначала надо построить таблицу ее значений при различных значениях аргумента. Аргумент обычно изменяется с фиксированным шагом. Пусть шаг изменения х равен 0,1. Надо найти у(0), у(0.1), … у(1). В ячейки A2:A12 вводятся автозаполнением числа 0, 0.1, … ,1. В ячейку B2 вводится формула заданной функции. Заполняем теперь ячейки B2:B12 значениями у вычисленными по формуле путем протягивания ячейки B2 вниз до B12. Технология построения графика:

1. Выделяем диапазон ячеек A1:B12.

2. Выбираем Вставка Диаграммы Точечная Точечная с гладкими кривыми и маркерами.

3. Используя инструменты для работы с диаграммами, отформатируем полученный график:

 

Построение графика функции с двумя и более условиями.

Для построения таких графиков используется функция логического ветвления ЕСЛИ(лог_выражения; значение_если истина; значение_если_ложь).Эта функция вводится в первую ячейку, где начинается вычисление функции и далее, всё проводится, как и для простого графика. Еслизадана функция с тремя условиями, то вводится формула =ЕСЛИ(лог_выр1; значение1; ЕСЛИ(И (лог_выр2; лог_выр3);Значение2; Значение3)).

 

 

Содержание отчета

 

Отчет должен содержать ход работы и результаты выполнения своих вариантов:

1. Представить Круговую диаграмму, а также Гистограммув соответствии со своим вариантом.

2. Построить график функции , где (n – номер варианта).

3. Построить график функции согласно своему варианту.

 

Контрольные вопросы

 

1. Что такое диаграмма в Excel?

2. Как задать ряды данных?

3. Как задать заголовок диаграммы?

4. Как задать подписи к осям диаграммы?

5. Что такое легенда?

6. Как изменить тип диаграммы?

7. Какие диаграммы можно построить только одного ряда данных?

8. Построение графика функции с двумя и более условиями.

 

Варианты заданий

 

1. Построить диаграммы на базе таблиц Задания 5.1. в соответствии со своими вариантами.

 

Задание 1

 

Вариант 1. Круговая. Легенда сверху.

Вариант 2.Разрезанная круговая. Легенда снизу.

Вариант 3. Объемная Разрезанная круговая. Легенда справа.

Вариант 4.Вторичная круговая. Легенда слева.

Вариант 5. Вторичная гистограмма. Легенда сверху.

Вариант 6. Кольцевая. Легенда снизу.

Вариант 7.Разрезанная кольцевая. Легенда справа.

Вариант 8. Лепестковая. Легенда слева.

Вариант 9.Заполненная лепестковая. Легенда сверху справа.

Вариант 10.Поверхность. Контурная. Легенда сверху.

 

Задание 2

 

Вариант 1.Гистограмма с накоплением.

Вариант 2.Гистограмма с группировкой.

Вариант 3.Объемная гистограмма.

Вариант 4. Объемная гистограмма с накоплением.

Вариант 5.Цилиндрическая с накоплением.

Вариант 6. Объемная цилиндрическая.

Вариант 7. Пирамидальная с накоплением.

Вариант 8.Объемный график.

Вариант 9.Линейчатая с группировкой.

Вариант 10.Объемная линейчатая с накоплением.

2. Построить графики функций с двумя и более условиями на заданном диапазоне.

 

Вариант 1. Вариант 2.  
Вариант 3. Вариант 4.
Вариант 5. Вариант 6.
Вариант 7. Вариант 8.
Вариант 9. Вариант 10.

 

Сортировка и фильтрация данных. Создание связанных таблиц

 

Теоретические сведения

 

Сортировка.Excel позволяет сортировать ячейки таблицы по возрастанию или убыванию.Команда Сортировка Данные открывает диалоговое окно сортировки.

В раскрывающемся списке Сортировать по выберите имя первого поля, по которому будет производиться сортировка. Выберите опцию по возрастанию или по убыванию. Если необходимо отменить сортировку, произведенную до этого, в списке Сортировать по укажите Не сортировать.

Чтобы произвести сортировку по нескольким столбцам, в раскрывающемся списке Затем по выберите имя поля, по которому будут дополнительно сортироваться данные.

Будьте внимательны: если выделена не вся область данных, то при сортировке они перемешаются.

Фильтрация.В отфильтрованных данных отображаются только строки, соответствующие заданным условиям, а ненужные строки скрываются. После отбора данные в этом подмножестве можно копировать, искать, изменять, форматировать, преобразовывать в диаграммы и выводить на печать; при этом их местонахождение и порядок не изменятся.

Возможен отбор по нескольким столбцам. Несколько фильтров можно применять одновременно. Фильтры действуют по дополнительному принципу, т. е. каждый новый фильтр накладывается на фильтр, примененный до него, и в еще большей степени ограничивает подмножество данных.

С помощью автофильтра можно создать три типа фильтров: по значениям списка, по формату или по условиям. Все они являются взаимоисключающими в пределах диапазона ячеек или столбца таблицы. Например, можно выполнить отбор по цвету ячеек или по списку чисел, но нельзя использовать оба типа одновременно; точно так же, необходимо выбрать один тип из двух, если требуется выполнить отбор по значкам или на основе фильтра, заданного пользователем.

Для получения более точных результатов рекомендуется не смешивать в одном столбце данные разных форматов, например текст и числа, числа и даты, поскольку для каждого столбца может использоваться только один тип команды фильтра. Если в столбце представлено несколько форматов, отображена будет команда для преобладающего формата. Например, если столбец содержит три значения в числовом формате и четыре — в текстовом, то отображается команда фильтра Текстовые фильтры.

Консолидация.Консолидация данных позволяет создавать таблицу-сводку по одной или нескольким категориям данных, объединив информацию разных листов одной или нескольких книг.

Консолидировать данные (команда Данные Работа с данными Консолидация) можно по расположению или по категории. Если вы консолидируете данные по расположению, MS Excel собирает информацию из одинаково расположенных ячеек каждого исходного листа. При консолидации данных по категории Excel использует в качестве основы для объединения листов заголовки столбцов или строк. Консолидация по категории предоставляет большую свободу для организации данных в исходных листах. При консолидации можно установить или убрать связь с исходнами листами. Функции консолидации:

– Количество значений;

– Среднее;

– Максимум;

– Минимум;

– Произведение/Количество чисел;

– Несмещенное отклонение;

– Смещенное отклонение;

– Несмещенная дисперсия.

Промежуточные итоги.Этот инструмент помогает быстро подвести итоги. Подведение итогов начинается с сортировки. Необходимо сгруппировать записи, по полю или полям которых требуется получить итоговые значения.Команда Данные Структура Промежуточные итоги откроет диалоговое окно Промежуточные итоги.

Сводные таблицы.Сводная таблица служит для быстрого подведения итогов или объединения больших объемов данных. Создание сводных таблиц осуществляется с помощью Мастера сводных таблиц(Вставка Таблицы Сводная таблица).

Этапы работы мастера:

1. На первом этапе выбираем, откуда выбираются данные: из одного диапазона, разных диапазонов или внешних источников.

2. Собственно выбор диапазонов

3. Макетирование сводной таблицы: необходимо мышью перетащить кнопки полей.

 

Практическая часть

 

Команды меню Данные работают корректно, когда шапка таблицы расположена в одной строке (если бы колонки таблицы были пронумерованы, то для работы команды Данные можно было бы использовать нумерацию в качестве названия колонок). При работе с командой Данные Excel рассматривает таблицу как базу данных, в которой строки являются записями, а столбцы полями.

Для дальнейшей работы преобразуем на новых листах таблицы из Задания 5.1. и 5.2. к виду:

 

 

 

Задание 5.5. Найти в преобразованной таблицеСписок(см. выше) тех студентов, у которых фамилия начинается с буквы "Б".

Выполнение.

1. Установить курсор в позиции «Ф.И.О»,

2. Выполнить команду Данные Фильтр. Раскрыть список команд столбца «Ф.И.О».

3. Выбра позицию Текстовые фильтры, затем – Начинается с..

4. В окне Пользовательский автофильтр в поле Ф.И.О., которое будет участвовать в определении критерия, ввести критерий: Б*.

 

 

6. Нажать кнопку ОК, чтобы перейти к записи, удовлетворяющей введенному критерию.

Задание 5.6. Отсортировать таблицу Список по столбцам Группа и Номер зачетки.

Выполнение.

1) Выделить диапазон таблицы, включая шапку (А2:F14).

2) Выполнить команду Данные Сортировка.

3) В диалоговом окне Сортировка диапазона в поле Сортировать по выбрать из списка имя столбца Группа, а в поле Затем поНомер зачетки, установив переключатели для обоих столбцов –По убыванию.

 

 

4)Нажать кнопку [ОК].

Задание 5.7. Использование числового фильтра. Выбрать из таблицы Успеваемость, тех студентов, средний балл которых >=6.

Выполнение.

1) Выделить диапазон таблицы, включая шапку (A3:F15).

2) Выполнить команду Данные Фильтр. Ячейки с названиями полей превратятся в раскрывающиеся списки.

3) Раскрыть список Автофильтрав столбце Средний балл студента.

4) Выбрать команду Числовые фильтры Больше или равно. В новом окне Пользовательский автофильр ввести значение 6.

5) Нажать кнопку [ОК].

6) Для отмены условия выбрать в раскрывающемся списке столбцаСредний балл студента команду Снять фильтр.

Задание 5.8. Работа с расширенным фильтром. Выбрать из таблицыСписок, записи со студентами группы 113010, у которых Вид оплаты =1. Результат поместить в отдельный выходной блок ниже исходной таблицы.

Выполнение.

1. Скопируем преобразованную таблицу Список(заголовки располагаются в одной строке каждый) на отдельный лист.

2. На этом же листе сформируем диапазон условий (A18:D19), отделив его от исходного диапазона (A2:D14) тремя пустыми строками. Чтобы создать диапазон условий необходимо скопировать заголовки полей исходного диапазона, которые будут ключевыми при отборе записей (для нашего случая - это Группа и Вид оплаты), и заполнить строки критериев:

– в ячейку A18:D18 скопировать заголовки полей;

– в ячейку A19ввести 113010;

– в ячейку D19 ввести 1.

3. Установить курсор в любую ячейку исходного диапазона.

4. Выполнить команду Данные Сортировка и фильтр Дополнительно. В диалоговом окне Расширенный фильтр выполнить следующие действия:

– В группе Обработкавыберем Скопировать результат в другое место– исходная таблица не изменится, а отобранные записи будут помещены в выходной диапазон.

– В поле Исходный диапазонуже введена ссылку на диапазон, содержащий исходную таблицу (A2:D14).

– В поле Диапазон условий ввести ссылку на диапазон условий (A18:D19).Ввод ссылок во всех полях данного диалогового окна легче и лучшевсего осуществлять путем протаскивания указателя мыши по нужному диапазону.

 

 

– В поле Поместить результат в диапазон установить ссылку на выходной диапазон (ячейка A21), так как выбран переключатель Скопировать результат в другое место.

– Необходимо установить флажок Только уникальные записи, чтобы oдинаковые записи не повторялись (будет выводиться только первая из всех удовлетворяющих критерию одинаковых записей).

– Нажать кнопку [ОК].

 

 

1. Получаем новую таблицу со строками исходного диапазона,отобранными согласно условию:

 

 

Задание 5.9.Подведение частных и общих итогов. Рассчитать средние баллы по всем дисциплинам, каждой из учебных групп (на примере таблицы Список).

Выполнение.

1. Выделить диапазон A3:F15– всятаблица, включая заголовки столбцов.

2. Выполнить команду Данные Структура Промежуточные итоги. В диалоговом окне Промежуточные итоги:

– в поле При каждом изменении в: из раскрывающегося списка выбрать Группа;

– в поле Операция из раскрывающегося списка выбрать Среднее;

– в поле Добавить итоги по: установить флажки: Математика, Информатика, Философия;

– установить флажок Итоги под данными.

3. Нажать кнопку [ОК].

4. Округлить полученные итоги до двух десятичных знаков с помощью команды Формат Ячейки.

Результат выполненного задания:

 

 

В результате подсчитаны средние баллы по группе и общий средний балл. Если с экрана убрать детали, нажав кнопку с изображением цифры "2"(второй уровень итогов), расположенную левее нумерации строк, то на экране останутся только полученные итоги.

5. Покажите результат второго уровня итогов.

Для получения развернутой информации по группе следует нажать кнопку с изображением знака "+" для соответствующей группы; для свертывания - кнопку с изображением знака "-".

Все промежуточные результаты скопировать в отчет.

Задание 5.10.Создание связанных таблиц. Сформировать в Excel таблицу для расчета сумм стипендий студентам факультета ПСФ в соответствии с формой, представленной в таблице Ведомость начисления. При этом учесть следующие условия:

– стипендия не начисляется студентам, обучающимся на платной основе;

– студенты получают минимальную стипендию, если средний экзаменационный балл больше 3;

– студенты, имеющие средний балл более 4.5, получают 50% надбавки к стипендии.

 

 

Выполнение.

1. В рабочей книге ПСФ.Иванов.xls перейти на новый лист и переименовать его на Начисления.

2. В строку 1 ввести название таблицы Ведомость начисления стипендии студентам ПСФ 1-го курса.

3. В ячейку B2 ввести текст Сумма минимальной стипендии.

4. В ячейку D2 ввести сумму минимальной стипендии – 120000.

5. Оформить шапку таблицы. Для этого в ячейки А3:Е3 ввести заголовки столбцов таблицы.

6. Столбец Номер зачетки заполнить данными. Чтобы не набирать еще раз номера зачеток, в столбце Номер зачетки установить ссылки на ячейки B5:B16, находящиеся на листе Список. Для того, чтобы при изменении номеров зачеток на листе Список, на листе Начисления они изменялись автоматически, выполните действия:

– перейти на лист Начисления, активизировать ячейку А4 и ввести знак "=" для определения формулы;

– перейти на лист Список. Ярлык листа Начисления выделится подсветкой, а в строке формул появится ссылка на лист-источник в следующем виде: Список!;

– выполнить щелчок левой клавишей мыши на ячейке В5 и нажать клавишу Enter;

– на листе Начисления в ячейке А4 будет введена формула вида: =Список!B5;

– скопировать формулу из ячейки А4 в остальные ячейки столбца А5:А15.

Замечание. Если нужная информация находится в другой книге (например, в книге с именем Отчет, на листе Ведомость, в ячейке В5), то необходимо перейти в нее с помощью команды Вид Окно Перейти в другое окно, при этом книга Отчетдолжна быть заранее открыта, затем перейти на нужный лист и выделить необходимую ячейку (например, В5) или диапазон ячеек. В строке формул появится ссылка вида: [Отчет.xls]Ведомость!В5.

8.Заполнить столбец Ф.И.О. данными. Для этого в ячейках В4:В15 листа Начисления установить ссылки на ячейки С5:С16 из листа Список.

9. Ввести формулы для вычисления суммы стипендии студентам:

– для расчета стипендии первому студенту активизируйте ячейку С5 на листе Начисления;

– перейти к пункту меню Формулы, выбрать категорию Логические, а в списке – функцию Если;

– щелкнуть на кнопке [ОК];

– во втором диалоговом окне задать аргументы функции. Для нашего примера, устанавливая курсор в каждой строке, ввести следующие операнды логической функции:Логическое выражение:И(Список!D5=1;Успеваемость!F5>=6), Значение если истина:$D$2,Значение если ложь:0;

– нажать кнопку [ОК].

В результате в ячейке С4 появится значение, рассчитанное по формуле: =ЕСЛИ(И(Список!D5=1;Успеваемость!F5>=3);$D$2;0).

Для задания логического выражения используется логическая функция И, которая возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, и возвращает ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Чтобы ссылка на ячейку D2 оставалась неизменной при копировании формулы, необходимо сделать ее абсолютной путем указания знака $ перед именем столбца и номером строки –$D$2.

Для вычисления величины стипендии всем студентам скопировать формулу из ячейки С4 в диапазон ячеек С5:С15.

10. Ввести формулы для вычисления величины надбавки к стипендии студентам:

– ввести формулу для расчета величины надбавки первому студенту в ячейку D4 на листе Начисления, загрузить Мастер функций и выбрать функцию Если;

– ввести следующие операнды логической функции:

Логическое выражение: И(Список!D5=1;Успеваемость!F5>=4,5),

Значениееслиистина : $D$2*0,5,

Значениееслиложь: 0;

– нажать кнопку [ОК].

В ячейке D5 появится значение, рассчитанное по формуле:

=ЕСЛИ(И(Список!D5=1;Успеваемость!F5>=4,5);$D$2*0,2;0).

– Для расчета величины надбавки всем студентам скопировать формулу из ячейки D4 в диапазон ячеек D6:D15;

– ввести формулы для расчета общей суммы начислений каждому студенту. Для этого в ячейку Е4 ввести формулу: =C4+D4 и скопировать ее в диапазон ячеек Е5:Е15.

В результате получится следующая таблица:

 

 

11. Сохранить рабочую книгу.

 

Контрольные вопросы

 

1. Порядок сортировки по убыванию.

2. Использование Автофильтра.

3. Консолидация данных.

4. Что такое Фильтрация?

5. Фильтры типа Первые 10…?

6. Как консолидировать данные по категории?

7. Функции консолидации.

8. Использование функции Расширенный фильтр.

9. Назначение средства Промежуточные итоги.

10. Для чего нужна Сводная таблица?

11. Этапы работы Мастера сводных таблиц.

12. Какие логические функции вы знаете?

13. Формат функции ЕСЛИ.

14. Логические функции И и ИЛИ.

15. Как получить связанные таблицы?

16. Что означает знак «!» в ссылках?

 

Варианты заданий

 

Вариант 1

 

1. На 3-х листах создайте списки групп вашего потока и заполните графы:

 

 

Каждый лист назовите по номеру группы.

2. С помощью инструмента Фильтр на любом из листов:

а) отобразите только те записи, средний балл которых в диапазоне от 6 до 8;

б) отобразите только те записи, у которых прочерк в графе количество пропусков.

3. С помощью инструмента Сводная таблица создайте сводную таблицу успеваемости по предметам, посчитав средний балл по каждому предмету по всему потоку

4. Создайте лист Стипендия.

 

Стипендию начислите по следующим правилам:

а) ставку стипендии укажите в отдельной ячейке, присвойте этой ячейке имя Stavka;

б) количество пропусков для начисления не более 5;

в) при среднем балле от 4,5 до 5,5 начисляется 65%; от 5,6 до 7 – 75%; от 7,1 до 8,9 – 85%, от 9 до 10 – 100% указанной ставки. Сумма стипендии заносится в графу Сумма, начисляемый процент – в графу Тариф.

Лист, на котором находится таблица,защитите паролем, скройте расчетные формулы,уберите сетку.

5. С помощью инструмента Промежуточные итоги на листе Стипендия просмотрите:

а) количество студентов, получающих стипендию по каждому из тарифов;

б) общую сумму стипендии;

в) количество студентов, получающих стипендию, в каждой группе;

г) среднюю стипендию по каждой группе.

 

Вариант 2

 

1. Создать телефонный справочник:

 

 

2. Заполните справочник:

а) в справочникедолжно быть не менее пяти записей с одинаковыми фамилиями и инициалами;

б) с помощью текстовых (левсимв, правсимв, пстр) и логических функций (если) определите и внесите в примечания принадлежность номера телефонной компании (по первой цифре: 2-город, 4-Белсел, 6- Velcom, 7-MTC).

3. Выполните сортировку справочника:

– по возрастанию номеров телефонов;

– по алфавитному порядку фамилий.

4. Выделите записи из справочника при помощи фильтра (Данные Фильтр Автофильтр):

– выделить записи, у которых номер телефона принадлежит Velcom или MTC;

– затем среди выделенных записей выделить записи, в которых фамилии начинаются с буквы П.

5. С помощью инструмента Промежуточные итоги подсчитайте количество абонентов в каждой сети.

6. Скопируйте списки абонентов каждой сети на отдельные листы, сохранив связь с исходным листом, каждый лист назовите по имени сети.

7. Создайте лист Итог, на котором поместите Сводную таблицу, подсчитывающую количество абонентов каждой компании.

 

Вариант 3

 

1. Создать таблицу учета товарооборота реализации продукции через торговые точки:

 

 

2. Заполните таблицы:

а) заполните первую;

б) для второй скопируйте данные из первой.

2. С помощью инструмента Фильтр отобразите на любой из таблиц:

а) продукцию определенной ценовой категории (больше одной цены, но меньше другой);

б) только хлебобулочные изделия.

3. С помощью инструмента Промежуточные итоги отобразите на любой из таблиц:

а) сумму продаж по каждому виду продукции;

б) среднюю цену по каждому виду продукции.

4. По полученным таблицам построить сводную таблицу, содержащую итоговую информацию по всем наименованиям, и по всем торговым точкам.

 

 

Вариант 4

 

1. Создайте таблицу следующего вида:

 

 

2. Заполните ее таким образом, чтобы некоторые фамилии повторялись.После заполнения, выполните подгон ширины столбцов, отформатируйте текст заголовка по вашему вкусу, выберите шрифт, его размер и начертание, расположите заголовок по центру блока ячеек, включите перенос по словам.

3.Применяя текстовые функции и функции даты рассчитайте зарплату на сегодняшний день с учетом обычных выходных (функции Сегодня, Чистрабдни,Левсимвол, Сцепить):

 

 

2. С помощью инструмента Фильтр отобразите:

а) премированных работников;

б) работников, получивших зарплату в заданном диапазоне (например, больше 100$, но меньше 200$).

3. С помощью инструмента Промежуточные итоги отобразите в 1-ой таблице:

а) количество различных тарифных ставок (9$, 10$, 15$...);

б) сумму удержаний по каждому работнику.

4. Спроектируйте итоговую таблицу начислений так, чтобы каждая фамилия встречалась один раз.

 

Вариант 5

 

1. Создайте таблицу следующего вида:

 

 

2. Заполните ее, перечислив не менее 15 товаров 5-ти категорий:

 

 

2. С помощью инструментов Фильтр, Сортировать, Итоги выполните:

а) сортировку понаименованиям, по категориям и датам (т.е. по датам в каждой категории), по цене;

б) поиск товаров, цена которых находится в некотором диапазоне;

в) поиск товаров категории Кондитер. изд., купленных по цене, не превышающей 1500 р.;

г) стоимостные итоги по товарам, относящимся к одинаковым категориям;

д) максимальную и минимальную цену товаров каждой категории.

3. Создайте сводную таблицу, отражающую стоимость товаров по категориям за различные периоды времени.

4. Поработайте со сводной таблицей:

– внесите изменения в исходную таблицу и обновите сводную;

– уберите из исходной несколько записей и перестройте сводную;

– сгруппируйте графу дата по месяцам.

 

Вариант 6

 

1. Cоздайте таблицу для начисления квартплаты:

 

 

В таблице ячейки, выделенные зеленым цветом, должны содержать ссылки на ячейку с соответствующей информацией; синим цветом –формулы для вычислений. Курсивом набраны пояснения. Пеня начисляется в размере 0,25% на каждый день просрочки платежа, платеж должен проходить не позднее 10-ого числа текущего месяца, дата везде должна стоять текущая. Сверхнормативной считается площадь свыше 20 кв.м на человека.

2.Заполните неизменные данные (тарифы, перерасчет, ссылки и формулы) и сохраните как шаблон.

3. Отдельным файлом создайте список жильцов:

 

 

4. На основе шаблона и файла создайте книгу платежей, в которой извещения должны формироваться автоматически.

 

Вариант 7

 

1. Заполните таблицу. Зачет получает только тот студент, который сдал все лабораторные, количество пропусков при этом не более 10 часов, средний балл контрольных работ не менее 6, неудовлетворительных оценок (1, 2, 3) на контрольных нет.

 

 

2. С помощью инструмента Фильтр выявите тех, у кого:

– количество пропусков более 10 часов;

– средний балл контрольных работ 8 и выше;

– нет несданных лабораторных работ.

3. Создайте аналогичные таблицы для нескольких предметов, назвав листы по предмету.

4. Выполните консолидацию данных, подсчитав количество пропусков для каждого студента

 

Вариант 8

 

1. Создать таблицу учета товарооборота реализации продукции через торговые точки:

 

 

2. Заполните таблицы:

а) заполните первую;

б) для второй скопируйте данные из первой.

2. С помощью инструмента Фильтр отобразите на любой из таблиц:

а) продукцию определенной ценовой категории (больше одной цены, но меньше другой);

б) только хлебобулочные изделия.

3. С помощью инструмента Промежуточные итоги отобразите на любой из таблиц:

а) сумму продаж по каждому виду продукции;

б) среднюю цену по каждому виду продукции.

4. По полученным таблицам построить сводную таблицу, содержащую итоговую информацию по всем наименованиям, и по всем торговым точкам.

 

 

Вариант 9

 

1. Составить таблицу данных по погоде в городах Республики Беларусь:

 

 

2. ИспользуяПользовательский автофильтропределить:

– города, температура в которых за конкретную дату превышала 9 °С. Отсортировать полученные данные ла по городу, а затем — по возрастанию температуры;

– данные по погоде для заданного города за конкретный промежуток времени. Отсортировать полученные данные сначала по виду, а затем — по возрастанию количества осадков;

– города, в которых наблюдалось северо-восточное направление ветра за конкретный период времени. Отсортировать эти данные сначала по городам, а затем возрастанию даты;

– города, температура в которых наблюдалась в пределах от 5 до 14 °С за конкретную дату. Отсортировать полученные данные сначала по городам, а затем — возрастанию температуры.

3. Используя расширенный фильтр определить:

– города, для которых направление ветра – северное или северо-западное, температура воздуха в которых больше 8 °С, но меньше 12 °С;

– данные о погоде для Санкт-Петербурга или Минска за некоторый конкретный промежуток времени;

– города, в которых за конкретный промежуток времени выпал снег или снег с дождем, а также – температура в которых находилась в пределах от 5 °С до +3 °С;

– города, в которых сила ветра не превышает 10% от средней силы ветра для города Гродно, и количество осадков – больше либо равно среднему значению для всех городов, или города, вид осадков в которых – град с дождем;

– города с западным или юго-западным направлением ветра, сила которого больше минимальной для Минска, или города, температура в которых за некоторый промежуток времени составляет 20% от средней температуры для Могилева.

4. Выведите следующие промежуточные итоги:

– среднее количество осадков данного вида с учетом данного города и конкретной даты;

– суммарное и среднее количество осадков данного вида для конкретного города;

– количество случаев определенного направления ветра с учетом конкретной даты;

– средние значения температуры и давления для конкретного города с учетом конкретной даты;

– средняя сила ветра определенного направления для конкретного города.

5. Используя консолидацию, определите среднее количество осадков, средние значения температуры и давления, для некоторых городов области.

 

Вариант 10

 

1. Составьте таблицуСпортивные достижения студентов.

 

 

2. ИспользуяПользовательский автофильтр,определить:

– студентов конкретного года рождения, оценки которых за спортивные нормативы больше. Отсортировать данные сначала по факультету, затем – по фамилиям студента;

– студентов конкретного года рождения, которые сдали определенный норматив. Отсортировать эти данные сначала по году поступления студентов, а затем по результатам сдачи;

– студентов конкретного факультета, которые сдали определенный норматив. Отсортировать эти данные снам по курсу, а затем – по результатам сдачи;

– студентов конкретного курса, оценка которых за сдачу спортивного норматива больше 2, но меньше либо равна на 4. Отсортировать полученные данные сначала по факультету, а затем – по оценке.

3. Используя Расширенный фильтр,определить:

– студентов одного года рождения, обучающихся на математическом, физико-техническом или экономическом факультетах, сдавших с оценкой "4" бег на 100 м или с оценкой "5" прыжки в длину;

– студентов первого или второго курсов исторического и юридического факультетов, оценки которых за спортивный норматив больше либо равны средней оценке для всех студентов;

– студентов с максимальными показателями (оценками) по всем спортивным нормативам для каждого курса факультета;

– студентов с неудовлетворительными оценками для всех курсов и факультетов;

– студентов данного года рождения и данного года поступления, сдавших бег на 100 м или прыжки в длину с оценкой "4" и выше.

4. Выведите следующие промежуточные итоги:

– среднийбалл по факультету;

– суммарный и среднийбалл для каждого спортивного норматива;

– количество студентов, приходящихся на каждый год поступления;

– среднийрезультат по каждому курсу.

 

Создание макросов

 

Теоретическая часть

 

Макрос –действие или набор действий, используемые для автоматизации выполнения повторяющихся задач. Макросы записываются на языке программирования Visual Basic для приложений (VBA) в Microsoft Office Excel. После создания макроса можно назначить его объекту (например, кнопке панели управления, графическому объекту или элементу управления), чтобы запускать этот макрос по щелчку объекта. Если макрос больше не нужен, его можно удалить.