Упражнение 1. Создание таблиц и работа с формулами

Экономическая информатика

Работа в электронных таблицах MS EXCEL

 

Набережные Челны

 

 

«Экономическая информатика: работа в электронных таблицах MS Excel»: учебное пособие для студентов экономических специальностей дневной и заочной форм обучения. /Составители: Каляшина А.В., Бусова А.М.- Набережные Челны: Издательство ИНЭКА, 2011. - с. 85

Учебное пособие «Практикум по информационным технологиям в научной деятельности» предназначено для студентов экономических специальностей дневной, заочной и дистанционной форм обучения. Включает в себя теоретический материал, основные приемы работы с электронными таблицами, обработку данных и расчеты в таблицах MS Excel.

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

Рецензент: к.ф.-м.н. Харчук С. И.

Печатается по решению методической комиссии экономического факультета НЧИ КФУ.

Лабораторная работа № 1

Создание, форматирование и редактирование таблиц. Работа с простыми формулами.

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

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

Электронные таблицы состоят из столбцов и строк. Столбцы идентифицированы буквами латинского алфавита, расположенными в заголовочной части таблицы. Строки – цифрами, расположенными в первой колонке. В табличном процессоре MS Excel 256 столбцов и более 65 тысяч строк.

Место пересечения столбца и строки называется ячейкой. Ячейка является минимальным элементом для хранения данных. Каждая ячейка имеет свой уникальный адрес, состоящий из имени столбца и номера строки, например: A4; H5. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках.

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

 

При создании таблиц в MS Excel следует придерживаться следующего порядка:

1) Ввод заголовка таблицы

2) Ввод заголовков столбцов

3) Ввод исходных данных

4) Ввод расчетных формул

5) Оформление внешнего вида таблицы (форматирование границ, шрифтов и т.д.)

Простые формулы в MS EXCEL начинаются со знака = и содержат знаки арифметических операций:

· Сложение и вычитание

· Умножение и деление

· Возведение в степень и выражения в скобках

Все операции по форматированию содержимого ячейки расположены на панели «Главная» и в диалоговом окне «Формат ячейки»

Упражнение 1. Создание таблиц и работа с формулами

Ключ к заданию

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

На первом листе в первой строке объединим ячейки в столбцах A:H и введем заголовок РАСЧЕТ ЗАРАБОТНОЙ ПЛАТЫ.

В строку, начиная с четвертой, введем заголовки столбцов. Если заголовки не умещаются выполним выравнивание ячеек: командная вкладка «Главная» – область «Выравнивание» – Перенос текста. Затем, изменим ширину столбца, если это необходимо. Отцентрируем текст в заголовках по вертикали и горизонтали с помощью командной вкладки «Главная».

Столбцы должны иметь следующие заголовки:

Месяц; табельный номер; фамилия, имя, отчество; оклад; количество рабочих дней; заработок в день; отработано дней; начислено; кол-во больничных дней; оплата по больн. листу; начислено по больн. листу; Итого; подоходный налог 13%; пенсионный 1%; процент по алиментам; удержания по алиментам; удержано всего; к выдаче.

 

Введем формулы, по которым будем вести расчеты: (при вводе формул будет появляться сообщение об ошибке #ДЕЛ/0! – т.к. исходных данных пока нет)

Заработок в день: Оклад / Количество рабочих дней

Начислено: Заработок в день * Отработано дней;

Начислено по больничному листу: Оплата по больн. листу * кол-во больничных дней * заработок в день

Итого: Начислено + Начислено по больн. листу

Подоходный налог: Итого * 13%

Пенсионный взнос: Итого * 1%

Удержание по алиментам: Итого * % по алиментам

Всего удержано: Подоходный налог + Пенсионный взнос + Удержания по алиментам

К выдаче: Итого – Удержано всего

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

На первый лист введем название месяца – ОКТЯБРЬ, на 2-й – НОЯБРЬ, на 3-й – ДЕКАБРЬ. В столбце «Месяц» введем на 1-й лист слово «Октябрь». Выделим ячейки А5:А14. Перейдем в строку формул, установим курсор в конце этой строки и нажмем одновременно Ctrl+Shift+Enter. Значение «октябрь» появится в выделенном массиве.

Вводим данные в столбцы «Количество рабочих дней», «Отработано дней», «Количество больничных дней» (см. образец). В столбец «Оплата по больн. листу» введем показатель 50%. Заполним столбцы «% по алиментам» и «Удержания по алиментам». Столбец «К выдаче» заполним с помощью функции автозаполнения.

Отформатируем столбцы, где были произведены расчеты, как числа с двумя знаками после запятой. Для этого выберем команду из контекстного меню Формат – Ячейки – Числовой – 2 знака после запятой. Числа в последнем столбце отформатируем денежным форматом в рублях с двумя знаками после запятой.

С помощью функции автосуммирования на командной вкладке «Главная» вычислим суммы по каждому столбцу «Начислено», «Начислено по больн. листу», «Итого», «Подоходный налог», «пенсион. взнос», «удержано по алиментам», «удержано всего».

Определим сумму по столбцу «К выдаче» и проверим результат.

Таблица должна выглядеть следующим образом:

Продолжение таблицы:

Удерживая клавишу Ctrl выделить столбцы «ФИО» и «К выдаче» и построить гистограмму.

Ключ к заданию

· Создайте таблицу (основные графы: №, наименование, ед. измерения, кол-во, цена, сумма).

· Заполните столбцы «наименование», «количество» и «цена» по своему усмотрению.

· Установите денежный формат числа в ячейках, где будут размещены суммы и установите необходимое количество десятичных знаков, если они нужны.

· Введите формулу для подсчета суммы и заполните ряд ячеек вниз, используя маркер заполнения

· Введите формулу для итоговой суммы (выделите ячейку, в которую нужно поместить результат, нажмите кнопку Σ панели инструментов и выделите блок тех ячеек, которые нужно сложить).

· Отсортируйте записи по алфавиту. Данные – Сортировка

·

· Для оформления счета вставьте дополнительные строки перед таблицей: Главная – Вставить – Строки на лист

· Наберите необходимый текст до и после таблицы.

 

Ключ к заданию

§ Введите заголовки граф таблицы. Во второй строке заголовка задайте выравнивание по центру и поверните текст на 90⁰, чтобы таблица разместилась на странице. На вкладке Выравнивание (из контекстного меню выбрать команду Формат ячеек) активизируйте переключатель Переносить по словам.

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

§ Вставьте формулы для количества остатка (кол-во прихода – кол-во расхода) и суммы остатка (кол-во остатка* цену расхода).

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

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

§ Воспользуемся возможностями базы данных Excel для заполнения нижней строки. Для этого на панель быстрого доступа сначала добавим команду Форма (кнопка Office, параметры Excel, Настройка, Форма, Добавить). Затем выделите таблицу без верхней строки заголовка. Выберите команду Форма на панели быстрого доступа. Форма данных, выведенная на экран, содержит имена полей базы данных и окна редактирования, в которых можно вводить и редактировать текст. После заполнения всей записи нажатием клавиши Enter осуществляется переход к новой карточке.

§ После заполнения формы вся информация автоматически воспроизводится в таблице.

Оперирование данными

Чтобы получить список товаров по отделам, применим сортировку строк.

§ Выделите таблицу без заголовка и выберите команду Сортировка на командной вкладке Данные.

§ Выберите первый ключ сортировки и вид сортировки (по возрастанию). Чтобы внутри отдела все товары располагались по алфавиту, добавить второй ключ сортировки (Добавить уровень).

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

§ Раскройте список ячейки «Кол-во остатка» и установите соответствующие параметры.

§ Вместо полного списка товаров получается список непроданных на сегодняшний день.

§ Фильтр можно усилить. Если дополнительно выбрать конкретный отдел, можно получить список непроданных товаров по отделу.

§ Чтобы снова увидеть полный список непроданных товаров, в списке «отдел» выбирают критерий «Выделить все».

§ Можно временно скрыть ненужные столбцы. Для этого выделяют столбец, вызывают контекстное меню и выбирают команду Скрыть.

§ Можно вставить дату, которая будет меняться автоматически в соответствии с установленным на компьютере календарем (командная вкладка Формулы, Дата и время, Сегодня).

§ Для получения итоговых данных по каждому отделу можно воспользоваться командой Промежуточные итоги на командной вкладке Данные.

 

Дополнительные задания

1) Заполнить таблицу, произвести расчеты и форматирование таблицы (выравнивание данных в ячейках, границы, заливка)

Формулы для расчета:

Всего по цеху = Заказ № 1 + Заказ № 2 + Заказ № 3

Итого = Сумма значений по каждой колонке (можно использовать кнопку )

2) Заполнить таблицу, произвести расчеты и форматирование таблицы (выравнивание данных в ячейках, границы, заливка)

Формулы для расчета:

Сумма надбавки = Процент надбавки * Сумма зарплаты

Примечание: В столбце «Процент надбавки» установите процентный формат чисел

В столбцах «Сумма зарплаты» и «Сумма надбавки» установите денежный формат

Лабораторная работа № 2

Относительная и абсолютная адресация ячеек

Цель работы: Изучение информационной технологии организации расчетов с абсолютной адресацией данных (при работе с константами) в таблицах MS Excel.

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

В Microsoft Excel различает три вида адресации ячеек:

1) относительная - при копировании формулы адреса ячеек, которые в нее входят изменяются соответствующим образом, согласно своему новому расположению:

 

=A1*B1

=A2*B2

=A3*B3

 

2) Абсолютная адресация – адрес ячейки остается неизменным, независимо от направления копирования формулы, например $A$4. Абсолютная адресация задается клавишей F4. Также можно создать уникальный адрес ячейки через меню Формулы – Присвоить имя

 

=$A$1*$B$1

=$A$1*$B$1

=$A$1*$B$1

 

3) Смешанная адресация, например $A4, A$4. В этом случае при копировании формулы изменяется только относительная часть адреса ячейки.

 

=$A1*B$1	=$A1*C$1	=$A1*D$1
=$A2*B$1	=$A2*C$1	=$A2*D$1
=$A3*B$1	=$A3*C$1	=$A3*D$1

 

Ключ к заданию

Исходные данные:

Константы вводить в расчетные формулы в виде абсолютной адресации.

Ячейка Е2 во всех формулах будет иметь абсолютный адрес, который задается двумя способами:

1) Нажатием клавиши F4

2) командная вкладка Формулы – Присвоить имя

Формулы для расчета данных в таблице:

1) Выпуск продукции = Количество выпущенных изделий * Отпускная цена одного изделия

Формула в ячейке С7 будет следующая: =С5*$E$2 (знаки «доллара» появятся после нажатия клавиши F4).

2) Себестоимость выпускаемой продукции = Количество выпущенных изделий * Себестоимость одного изделия. Для этого в ячейку С8 ввести формулу =С5*С6

3) Прибыль от реализации продукции = Выпуск продукции – Себестоимость выпускаемой продукции, для этого в ячейку С9 введите формулу = С7-С8

4) Рентабельность продукции = Прибыль от реализации продукции/Себестоимость выпускаемой продукции. В ячейку С10 введите формулу = С9/С8, после чего задайте процентный формат.

Ключ к заданию

Константы вводить в расчетные формулы в виде абсолютной адресации.

Исходные данные:

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

Установить курсор в ячейку С3 и выбрать на командной вкладке Формулы – Присвоить имя, появится окно:

Нажать ОК. Аналогичную операцию повторить в ячейках С4 и С5. Таким образом, мы задали абсолютные имена ячейкам.

Формулы для расчета:

1. Подоходный налог = (Оклад – Необлагаемый налогом доход)*% подоходного налога. Обратите внимание, что когда вы будете щелкать по ячейкам С3 и С4 вместо адреса будут выходить присвоенное имя! Для копирования формулы в остальные ячейки столбца D используйте маркер заполнения
2. Отчисления в благотворительный фонд = Оклад * % отчисления в благотворительный фонд (ячейка С5 уже имеет имя)! Для копирования формулы в остальные ячейки столбца Е используйте маркер заполнения
3. Всего удержано = Подоходный налог + Отчисления в благотворительный фонд
4. К выдаче = Оклад – Всего удержано

Постройте объемную гистограмму по данным столбца «К выдаче».

Ключ к заданию

Формула n-го члена: а_n = a₁ + d*(n-1).

Формула суммы n первых членов арифметической прогрессии: S_n = (a₁+a_n)*n/2.

Здесь: а₁ – первый член прогрессии;

d – разность прогрессии.

Ø Ввод заголовка таблицы. В ячейку А1 вводим заголовок таблицы. Длина текста превышает ширину таблицы, поэтому нужно сформатировать заголовок. Выделяем ячейки А1 – D1 и вызываем диалоговое окно Формат ячеек (из контекстного меню). На вкладке Выравнивание устанавливаем переключатель Переносить по словам и Объединение ячеек. Выравнивание по горизонтали – по центру, по вертикали – по центру. Можно выбрать шрифт для заголовка, его цвет и начертание (вкладка – Шрифт).

Ø Форматирование строки заголовков (d, n, a_n, S_n). Для набора нижних индексов: вызываем диалоговое окно Формат ячеек, вкладка Шрифт, переключатель Подстрочный в группе Видоизменение.

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

Ø Ввод значений n. Заполнить первые две ячейки, затем выделить их и протянуть маркер заполнения вниз на требуемое количество ячеек.

Ø Получение значений а_n. Ввести в первую ячейку значение первого члена арифметической прогрессии, во вторую – формулу для вычисления n-го члена прогрессии. Поскольку формулы содержат ссылку на ячейку С3, адрес которой при копировании должен оставаться постоянным, этот адрес нужно сделать абсолютным. Для этого в строке формул курсор ставим перед адресом С3 и нажимаем клавишу F4, адрес примет вид $C$3, что означает, что ссылка на ячейку С3 не будет меняться при копировании. Затем, выделив ячейку с формулой, заполняем остальные ячейки данной формулой, протянув маркер заполнения вниз на требуемое количество ячеек.

Ø Аналогично вводится формула для вычисления суммы n членов прогрессии.

Ø Оформить таблицу: выполнить форматирование данных и «шапки» таблицы.

Лабораторная работа № 3

Консолидация данных и сводные таблицы

Цель работы: изучение информационной технологии объединения данных в MS Excel.

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

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

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

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

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

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

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

После этого на экране с левой стороны появляется шаблон внешнего вида сводной таблицы, а с правой – список полей сводной таблицы.

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

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

В области «Строка» и «Столбец» помещают поля, которые должны быть представлены в Сводной таблице.

В области «Страница», «Строка» и «Столбец» каждое поле может помещаться только один раз.

В область «Данные» помещают поля, по которым при создании Сводной таблицы будут производиться вычисления с помощью одной из функций: сумма; количество значений; среднее; максимум и др.

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

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

Ключ к заданию.

Для создания сводной таблицы, данные должны быть однородными, поэтому исходную таблицу оформим в следующем виде:

 

§ Вызываем Мастер сводных таблиц. Командная вкладка Вставка – Сводная таблица.

§ На первом шаге Мастер просит выбрать данные для анализа и указать, куда следует поместить отчет сводной таблицы. Если выбираем вариант На существующий лист, то необходимо указать диапазон размещения:

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

Один из примеров сводной таблицы. Здесь страница – наименование точки, столбец – цена, строка – наименование, данные – сумма. Итоги подводятся по наименованиям и по ценам.

Лабораторная работа № 4

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

Цель работы: Изучение информационной технологии представления графического представления данных в MS Excel.

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

Microsoft Excelпредоставляет большой набор возможностей по графическому представлению данных. Для создания диаграмм на командной вкладке Вставка предназначена командная область Диаграммы, где можно выбрать тип диаграммы: Гистограмма, График, Круговая, Линейчатая, С областями, Точечная и Другие диаграммы. Для дальнейшей работы с созданной диаграммой, можно воспользоваться командными вкладками Конструктор, Макет, Формат (эти вкладки активизируются после выделения созданной диаграммы).

При работе с диаграммами применяется специальная терминология:

· Ряд данных – набор взаимосвязанных данных для построения диаграммы.

· Ось – одна из сторон диаграммы Excel. При построении двухмерной диаграммы горизонтальная ось называется осью X, а вертикальная – осью Y.

· Легенда – область внутри диаграммы с информацией об ее элементах.

· Заголовок диаграммы – имя диаграммы, обычно описывающее ее содержимое.

Лабораторная работа № 5

Обработка данных в электронных таблицах MS Excel

Цель работы: изучение информационной технологии обработки данных в MS Excel.

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

Данные, организованные в список, называются базой данных. При этом строки таблицы – записи базы данных, а столбцы – поля записей. Чтобы превратить таблицу Excel в список, необходимо присвоить столбцам имена, которые будут использоваться как имена полей записей. При создании базы данных необходимо выполнять следующие правила:

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

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

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

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

5. Не рекомендуется создавать искусственно строку итогов

К обработке данных в электронных таблицах MS Excel относится: сортировка, фильтрация, создание итоговых строк, использование функций для работы с базами данных. (вкладка Данные)

Сортировка данных.

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

Сортировка в особом порядке. MS Excel позволяет сортировать данные в особом порядке. Чтобы воспользоваться собственным порядком сортировки, его нужно предварительно создать:

1. Выбрать командную вкладку Данные, Сортировка.

2. В поле Порядок выбрать Настраиваемый список.

3. В поле Элементы списка ввести элементы, разделяя их нажатием клавиши Enter.

4. После ввода всех элементов нажать кнопку Добавить и закрыть окно Списки.

 

 

Фильтрация данных.

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

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

2. Расширенный фильтр. Командная вкладка Данные, Дополнительно. Его отличие от команды Автофильтр состоит в том, что отфильтрованные записи можно перенести в другое место рабочего листа Excel, не испортив исходный список. Для использования расширенного списка надо сначала создать таблицу критериев, которую желательно разместить на том же рабочем листе, что и исходный список, но так, чтобы она не была скрыта во время фильтрации. Таблица критериев должна состоять, по крайней мере, из двух строк: в верхней строке указываются имена полей, во второй и последующих - условия отбора. Для обеспечения идентичности текстовых полей нужно скопировать имена полей списка в ту часть рабочего листа, где будет располагаться таблица критериев. Кроме таблицы критериев, для выполнения расширенного фильтра надо определить вид выходного документа. Это означает, что следует скопировать в свободное место рабочего листа имена тех полей списка, которые определяют вид выходного документа. Количество строк в выходном документе Excel определит сам.

 

Диалоговое окно «Расширенный фильтр» выглядит следующим образом:

 

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

При создании диапазона условий (таблицы критерия) можно использовать логические операции ИЛИ, И, одновременно И и ИЛИ.

Задание условий с использованием логической операции ИЛИ.

Чтобы задать условия в диапазоне критериев с использованием логической операции ИЛИ, нужно эти условия расположить в разных строках:

диапазон условий:

название	количество
парта
	>9

результирующая таблица:

название	количество
парта
парта
парта
стол

 

Задание условий с использованием логической операции И.

Для этого условия в диапазоне критериев нужно расположить на одной строке:

диапазон условий:

название	количество
парта	>2

 

результирующая таблица:

 

название	количество
парта
парта

 

Задание условий с одновременным использованием логических операций И, ИЛИ.

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

диапазон условий:

название	количество
парта	>2
стол	<10

 

результирующая таблица:

название	количество
парта
стол
парта
стол

 

Создание итоговых строк.

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

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

· Операция: из раскрывающегося списка нужно выбрать функцию, используемую при подведении итогов. Наиболее часто используемая функция – это сумма. Также можно выбрать Количество, Среднее, Максимум, Минимум, Произведение и т.д.

· Добавить итоги по: нужно указать по какому (каким) полю (полям) подводить итоги.

 

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

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

При установке переключателя Итоги под данными, промежуточные и общие итоги будут расположены под данными, а если нет этого переключателя – над данными.

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

Лабораторная работа № 6

Использование функций в расчетах MS Excel

Цель занятия: изучение информационной технологии организации расчетов с использованием встроенных функций в таблицах MS Excel.

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

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

Excel предоставляет средство, которое намного упрощает использование функций. Это диалоговое окно Мастер функций, с помощью которого можно пройти весь процесс создания любой функции Excel.

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

Действия в Мастере функций состоят из двух шагов. На первом шаге нужно выбрать функцию из имеющегося списка. Все функции разбиты на категории (всего 9 категорий), поэтому сначала выбирают категорию, а затем функцию. В нижней части окна выводится подсказка: шаблон для функции, который помогает понять правила ее записи и описание ее назначения. Если вы не знаете точно, к какой категории относится функция, выберете «Полный алфавитный перечень», в котором представлены все функции, которыми располагает EXCEL в алфавитном порядке. На втором шаге Мастера функций открывается окно выбранной вами функции, в котором в определенном синтаксическом порядке располагаются аргументы функции, которые пользователь должен ввести. В этом окне также присутствует подсказка – словесное описание функции, дополнительная информация о типе аргумента.

Ключ к заданию.

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

§ Выделите цветом столбцы, соответствующие нерабочим дням.

§ Для каждого сотрудника проставьте количество часов, отработанных за день, о – если он в отпуске, б – если болеет, п – если прогуливает.

§ для фиксирования столбца «Фамилия», чтобы он оставался на своем месте: выделите столбец справа от столбца «Фамилия» и на командной вкладке Вид выберите команду Разделить.

§ Для подсчета дней явок и неявок используются формулы.

§ Сначала подсчитываем количество ячеек, содержащих числа, не суммируя их. Для этого используется функция СЧЕТ. После вызова функции указываем диапазон значений для первого сотрудника. После этого копируем формулу вниз.

§ Для подсчета количества дней, проведенных в отпуске, вставляем функцию СЧЕТЕСЛИ, в качестве критерия вводим «о».

§ Аналогично считаются дни прогулов и болезни.<