|
Методичка по Excel (Осипова, Сметкина). Введение Основы работы в Microsoft Excel
Изменение отображения секторов
Для изменения отображения сектора необходимо выполнить следующие действия:
Выделить любой сектор диаграммы.
Выбрать пункт меню Формат|Выделенный элемент данных (в контекстном меню – Формат точки данных).
В открывшемся диалоговом окне установить параметры форматирования по своему вкусу и нажать ОК. Диаграмма имеет следующий вид:
Добавление линии тренда к ряду данных
Для выявления общей тенденции изменения значений отдельного ряда на диаграмму выводится линия тренда.
Линия тренда – это линия регрессии, аппроксимирующая точки данных, или линия скользящего среднего. Линию тренда можно вывести за пределы, в которых данные уже известны, и показать тенденцию их изменения.
Линии тренда обычно используются в задачах прогнозирования, для сглаживания разброса цен, по линии тренда можно определить линейную корреляцию двух переменных и т.д.
Линии тренда различаются в зависимости от заданного математического соотношения между значениями ряда данных. Например, при сглаживании разброса цен на акции за определенный период может быть использован или линейный тип тренда, или скользящее среднее, при определении периода полураспада радиоактивного соединения понадобится экспоненциальный тип тренда и т.д. Выбор типа линии тренда всегда связан с предметной областью.
Линии тренда можно использовать не для всех типов диаграмм, а только для гистограмм, линейчатых диаграмм, графиков, XY-точечных диаграмм, диаграмм с областями.
Выведенная на диаграмму линия тренда сохраняет связь с исходным рядом, то есть при изменении данных соответственно изменяется линия тренда, а при удалении ряда линия тренда удаляется вместе с ним.
Для примера вывода на диаграмму линии тренда на новом листе рабочей книги необходимо создать дополнительную таблицу следующего вида (рис.12):
Р
ис.12
Построить гистограмму, используя данные новой таблицы, для этого:
Выбрать пункт меню Вид|Панели инструментов|Диаграммы.
Выделить блок ячеек А2:В14.
На панели Диаграммы при помощи кнопки Тип диаграммы выбрать Гистограмма.
Удалить легенду с изображения диаграммы, щелкнув по кнопке Легенда на панели Диаграммы.
Самостоятельно ввести заголовок диаграммы «Прогноз спроса на товар Видеомагнитофон».
Выделить ряд данных диаграммы и щелкнуть правой кнопкой мыши для вывода на экран контекстного меню.
В контекстном меню выбрать пункт Добавить линию тренда.
В открывшемся диалоговом окне на вкладке Тип выбрать Полиномиальная 6-й степени, на вкладке Параметры в опции Прогноз установить Вперед на 2 периода и включить опции Показывать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации (R^2), нажать ОК. Диаграмма имеет следующий вид:
Примечание: чем ближе к единице величина достоверности аппроксимации, тем правильнее был выбран тип линии тренда.
ГЛАВА 3 Управление базами данных (списками) и анализ данных
Возможность работы с базами данных (списками) у программ, предназначенных для обработки таблиц, становится в настоящее время все более важной в экономической работе. Excel имеет развитые инструментальные средства поддержки баз данных. Виды работ с ними можно разбить на 2 категории:
Организация встроенных, расположенных непосредственно на рабочем листе баз данных с помощью списков.
Работа с внешними данными с помощью специальных запросов.
Современные базы данных представляют собой набор таблиц, имеющих между собой сложные связи. Каждая такая таблица состоит из множества строк, называемых записями. Каждая из строк делится на части, называемые полями, для которых жестко определены требования к типу содержащихся в них данных.
Чтобы достичь максимальной эффективности при работе со списками, необходимо следовать следующим правилам:
Каждый столбец должен содержать информацию одного типа
Одна или две верхние строки списка должны содержать уникальные заголовки, каждый из которых описывает содержимое расположенного ниже столбца.
Список не должен включать пустые строки и столбцы.
Лучше всего, если для списка отводится отдельный лист. Если же это невозможно, то список должен быть отделен от других данных рабочего листа по крайней мере одной пустой строкой и одним пустым столбцом.
Не следует размещать данные слева и справа от списка, поскольку они могут быть скрыты в процессе фильтрации списка.
Над списками можно производить следующие действия:
Сортировать список.
Добавлять или изменять данные с помощью формы.
Фильтровать список таким образом, чтобы были выбраны только те строки, которые удовлетворяют заданному критерию.
Автоматически вычислять промежуточные итоги.
Создавать для списка структуру.
Создавать отчет, который в Excel называется Сводная таблица. С ее помощью можно мгновенно подытожить данные в списке.
Проверять вводимые в ячейку данные.
Все основные возможности работы со списками включены в пункт меню Данные.
Для получения навыков создания базы данных и ее обработки предлагается открыть новую рабочую книгу и создать таблицу – пример списка.
Для открытия новой рабочей книги:
На стандартной панели инструментов нажать пиктограмму Создать  (или выбрать команду меню Файл|Создать|Чистая книга).
Содержимое новой таблицы-списка будет основано на данных «Прайс-листа» предыдущей рабочей книги, для чего данный лист необходимо скопировать из книги «Продажи.xls» в новую рабочую книгу. Для этого необходимо:
Выбрать пункт меню Окно|Расположить|Слева направо и нажатьОК.
В книге «Продажи.xls» открыть «Прайс-лист».
Удерживая нажатой клавишу Ctrl, перетащить ярлычок копируемого листа из одной книги в другую, расположив его перед Листом1. В новой книге копия листа будет иметь то же имя, что и оригинал.
Открыть Книгу2 на весь экран.
Открыть Лист1 и переименовать его в «Отчет».
В ячейку В1 ввести заголовок: Отчет ООО «Техносервис» о продаже аудио- и видеотехники магазинам-закупщикам в 1 квартале.
Шапку таблицы заполнить по образцу (рис.13).
Рис.13
В ячейку А3 ввести 1, в ячейку А4 - 2.
Выделить блок ячеек А3:А4 и протащить маркер заполнения до ячейки А18.
Выделить блок ячеек В3:В18.
Выбрать пункт меню Данные|Проверка.
В окне Тип данных выбрать Список.
В окне Источник нажать F3 и выбрать «Наименование_товара». Нажать ОК.
Заполнить графу «Наименование товара» по образцу (рис.14).
Рис.14
Выделить блок ячеек С3:С18.
Выбрать пункт меню Данные|Проверка.
В окне Тип данных выбрать Список.
В окне Источник с клавиатуры ввести новый список Аудио;Видео (вводить текст без пробела) и нажать ОК.
Заполнить графу «Вид продукции», используя созданный список по образцу (рис.15).
Аналогичным способом заполнить графы «Наименование магазина» и «Вид оплаты».
Рис.15
Графу «Цена» необходимо заполнить с использованием функции ПРОСМОТР, для этого:
Установить курсор в ячейку F3.
В
строке формул нажать кнопку Вставка функции (или выбрать пункт меню Вставка|Функция…).
В открывшемся окне в области Категория выбрать Ссылки и массивы, в области Выберите функцию – ПРОСМОТР и нажать ОК.
В следующем окне выбрать искомое_значение; просматриваемый_вектор;вектор_результов и нажать ОК.
Передвинуть открывшееся окно так, чтобы просматривались данные таблицы.
Установив курсор в окне Искомое_значение, щелкнуть левой кнопкой мыши в ячейке В3 текущей таблицы.
Установить курсор в окно Просматриваемый_вектор и нажать функциональную клавишу F3 на клавиатуре.
В открывшемся окне выбрать имя блока Наименование_товара и нажать ОК.
Установить курсор в окно Вектор_результатов и нажать функциональную клавишу F3.
В открывшемся окне выбрать имя блока Цена_в_у.е. и нажать ОК.
Нажать ОК в главном окне функции ПРОСМОТР.
Скопировать полученную формулу в ячейки F4:F18.
В результате выполненных действий в ячейке F3 появилось значение цены товара «Аудиоплеер».
Графу «Количество» заполнить по образцу (рис.16).
Графу «Сумма» рассчитать по формуле =Цена*Количество
Рис.16
Сохранить новую рабочую книгу с именем «Списки.xls».
Использование в расчетах вложенных функций
Excel предоставляет возможность в качестве аргумента одной функции использовать другую функцию, которая называется вложенной. Можно задавать до семи уровней вложения функций.
Для примера необходимо ввести новую графу о наличии скидки в размере 5% на закупаемые товары, если они приобретаются за наличный расчет и их цена больше 500 у.е., для этого:
В ячейку I2 ввести название новой графы «Наличие скидки».
Открыть «Прайс-лист».
В ячейку А12 ввести с клавиатуры «Скидка», в ячейку В12 ввести 5%.
Выделить блок ячеек А12:В12 и выбрать пункт меню Вставка|Имя|Создать.
В открывшемся окне Создать имена установить флажок В столбце слева и нажать ОК.
Снять выделение с блока ячеек.
Перейти на лист «Отчет».
Установить курсор в ячейку I3 и щелкнуть по пиктограмме Вставка функции.
В появившемся окне Мастер функций выбрать категорию функций Логические.
В списке функций выбрать ЕСЛИ.
Нажать ОК.
Подвинуть появившееся на экране окно за пределы таблицы, чтобы не мешать указателю мыши работать с ячейками таблицы.
Установить курсор в окно Логическое выражение и щелкнуть по кнопке со стрелкой списка функций, которая располагается в строке формул на месте поля имен (см.рис.17).
Рис.17
В открывшемся списке выбрать Другие функции.
В появившемся окне Мастер функций выбрать категорию функций Логические.
В списке функций выбрать И.
Нажать ОК. Открылось следующее окно (рис.18):
Рис.18
В появившемся окне Аргументы функции (вложенной функции И), установив курсор в поле Логическое значение 1, щелкнуть левой кнопкой мыши в ячейке F3 таблицы и с клавиатуры набрать >500; установив курсор в поле Логическое значение 2, щелкнуть в ячейке Е3 и с клавиатуры набрать = «Нал» и при этом не нажимать ОК в текущем окне! (рис.19):
Рис.19
Щелкнуть левой кнопкой мыши в строке формул на имени функции ЕСЛИ (рис.20).
Рис.20
В появившемся окне Аргументы функции (функции ЕСЛИ) перевести курсор в поле Значение_если_истина.
Нажать функциональную клавишу F3 на клавиатуре и в открывшемся окне выбрать имя блока «Скидка». Нажать ОК.
Перевести курсор в окно Значение_если ложь и нажать клавишу Пробел на клавиатуре, что будет означать отсутствие скидки (рис.21).
Рис.21
Обратить внимание на формулу, которая отразилась в строке формул =ЕСЛИ(И(F3>500;E3="Нал");Скидка;" ").
Скопировать полученную формулу в ячейки I4:I18.
Сортировка списков и диапазонов
Сортировка предназначена для более удобного представления данных.
Excel предоставляет разнообразные способы сортировки данных. Можно сортировать строки или столбцы в возрастающем или убывающем порядке данных, с учетом или без учета регистра букв. Можно задать и свой собственный пользовательский порядок сортировки. При сортировке строк изменяется порядок расположения строк, в то время как порядок столбцов остается прежним. При сортировке столбцов соответственно изменяется порядок расположения столбцов.
Стандартные средства Excel по умолчанию позволяют сортировать данные по трем признакам (графам таблицы).
Внимание! Перед тем, как производить сортировку, необходимо установить курсор на любую ячейку сортируемой таблицы.
Для демонстрации работы команды Сортировка будет использоваться созданная таблица на листе «Отчет».
Для сортировки списка наименований товаров в алфавитном порядке необходимо:
Выделить блок ячеек В3:I18. Обратить внимание, что первая графа (№ п/п) таблицы не принимает участия в процессе сортировки, чтобы нумерация строк оставалась неизменной.
Активизировать пункт меню Данные.
Выбрать команду Сортировка.
В окне Сортировать по из выпадающего списка выбрать «Наименование товаров».
Установить переключатель По возрастанию.
Установить переключатель Идентифицировать поля по в положение Подписям (первая строка диапазона).
Щелкнуть по кнопке Параметры.
Установить переключатель в положение Строки диапазона.
Нажать ОК.
В окне Сортировка диапазона нажать ОК.
Снять выделение с диапазона ячеек в таблице.
В данном случае список отсортирован только по одному признаку сортировки, а строки упорядочены в соответствии с расположением наименований товаров по алфавиту.
Сортировка по нескольким столбцам
Для сортировки списка сначала по наименованию магазина, а затем по виду продукции и виду оплаты необходимо выполнить следующие действия:
На листе «Отчет» выделить блок ячеек В3:I18.
Выполнить команду меню Данные|Сортировка.
В поле Сортировать по выбрать «Наименование магазина» (это поле называется первым ключом сортировки) и установить флажок По возрастанию. В поле Затем по (второй ключ сортировки) выбрать «Вид продукции» и установить флажок По убыванию. В поле В последнюю очередь по (третий ключ сортировки) выбрать «Вид оплаты (нал./безнал.)» и установить флажок По возрастанию. Второй ключ используется, если обнаруживаются повторения в первом, а третий – если повторяется значение и в первом, и во втором ключе.
Нажать ОК.
Снять выделение с таблицы и просмотреть результат на экране.
Аналогичными способами сортируются данные по столбцам таблицы, только при этом в окне Параметры сортировки необходимо установить переключатель Сортировать столбцы диапазона.
Промежуточные итоги
Довольно часто на практике приходится анализировать данные части таблицы или списка по определенным критериям. Для решения этой проблемы Excel предлагает операцию подведения промежуточных итогов. Только после выполнения сортировки данных таблицы можно использовать команду Итоги из меню Данные, чтобы представить различную итоговую информацию. Эта команда добавляет строки промежуточных итогов для каждой группы элементов списка, при этом можно использовать различные функции для вычисления итогов на уровне группы. Кроме того, команда Итоги создает общие итоги.
Команда Итоги создает на листе структуру, где каждый уровень содержит одну из групп, для которых подсчитывается промежуточный итог. Вместо того чтобы рассматривать сотни строк данных, можно закрыть любой из уровней и опустить тем самым ненужные детали.
В качестве промежуточных итогов необходимо определить сумму продаж каждого магазина, а также общий итог продаж для этого:
Установить курсор в любую ячейку таблицы с данными.
Выбрать пункт меню Данные|Итоги.
В окне При каждом изменении в выбрать «Наименование магазина».
В окне Операция выбрать Сумма.
В окне Добавить итоги по выбрать Сумма (проверить, чтобы в остальных полях флажки отсутствовали).
Включить флажок Итоги под данными. Если флажок этого параметра установлен, то итоги размещаются под данными, если сброшен – над ними.
Нажать ОК, снять выделение с таблицы и просмотреть результат на экране.
Дополнительно к подведенным итогам необходимо подсчитать суммы, полученные магазинами за продажу аудио- и видеопродукцию, для этого
Установить курсор в любую ячейку таблицы с данными.
Выбрать пункт меню Данные|Итоги.
В поле При каждом изменении в выбрать «Вид продукции».
В поле Операция выбрать Сумма.
В поле Добавить итоги по выбрать Сумма (проверить, чтобы ничего другого выбрано не было).
Обратить внимание, что в поле Заменить текущие итоги флажок должен отсутствовать. Нажать ОК.
Снять выделение с таблицы и просмотреть результат на экране.
Убрать полученные итоги, предварительно установив курсор в таблицу и выполнив команду Данные|Итоги|Убрать все.
Обеспечение поиска и фильтрации данных
Наиболее часто используемыми операциями над списками (базами данных) в Excel являются поиск и фильтрация данных.
Отфильтровать список - значит скрыть все строки, за исключением тех, которые удовлетворяют заданным условиям отбора. Excel предоставляет две команды: Автофильтр - для простых условий отбора и Расширенный фильтр - для более сложных критериев.
Для осуществления операций фильтрации данных будет использована таблица листа «Отчет» рабочей книги «Списки.xls».
Применение Автофильтра
Перед использованием команды Автофильтр необходимо выделить любую ячейку в таблице. При этом Excel выведет кнопки со стрелками (кнопки автофильтра) рядом с каждым заголовком столбца. Щелчок по кнопке со стрелкой рядом с заголовком столбца раскрывает список значений, которые можно использовать для задания условий отбора строк.
Автофильтр можно применить к любому количеству столбцов. Для этого сначала нужно отфильтровать список по одному столбцу, затем полученный список отфильтровать по другому столбцу и т.д.
Необходимо определить, какие товары были проданы ООО «Техносервис» магазину «Техносила» по безналичному расчету, для этого:
Установить курсор в любую ячейку таблицы.
Выбрать пункт меню Данные.
Выбрать команду Фильтр, а затем Автофильтр.
Выбрать в раскрывающемся списке рядом с заголовком «Наименование магазина» - Техносила.
Выбрать в раскрывающемся списке рядом с заголовком «Вид оплаты» - Безнал.
При использовании команды Автофильтр на экране скрываются все строки, не удовлетворяющие условиям отбора. Номера отфильтрованных строк выделены синим цветом, а в строке состояния выводится количество отобранных строк и общее число записей в списке.
Удаление Автофильтра
Для применения автофильтра в соответствии с новыми критериями необходимо выбрать в меню Данные команду Фильтр и затем Отобразить все.
Для удаления всех автофильтров и их кнопок необходимо еще раз выбрать команду Автофильтр, удалив, таким образом, галочку рядом с названием этой команды в подменю Фильтр из меню Данные.
Применение Автофильтра к нескольким столбцам с заданием условий
Необходимо выбрать товары, реализованные за наличный расчет на сумму от 100000 у.е. и выше, для этого:
Удалить результаты предыдущего автофильтра.
Установить курсор в таблицу.
Выбрать Данные|Фильтр|Автофильтр.
Выбрать в раскрывающемся списке рядом с заголовком «Вид оплаты…» - Нал.
Выбрать в раскрывающемся списке рядом с заголовком «Сумма» - Условие.
В диалоговом окне Пользовательский автофильтр, в поле Сумма из выпадающего списка выбрать больше или равно.
В соседнем поле ввести с клавиатуры 100000.
Щелкнуть кнопкой ОК.
Просмотреть результат на экране и убрать автофильтр.
Примечание: с помощью пользовательского автофильтра, выбрав пункт Условие, можно создать специальный автофильтр с более гибкими возможностями. Например, выбрать товары, проданные за наличный расчет по цене менее 100 у.е. или более 500 у.е., для чего задать два условия отбора, соединенные логическим оператором ИЛИ.
Применение автофильтра в значительной степени ограничено в выборе способов фильтрации и возможностях задания критериев поиска. В случае когда нужно произвести действительно сложный поиск (фильтрацию), следует пользоваться другим средством – Расширенным фильтром.
Применение расширенного фильтра
Основной особенностью, отличающей расширенный фильтр от автофильтра, является необходимость в создании вне таблицы вспомогательного списка, содержащего критерии условий поиска.
Команда Расширенный фильтр позволяет:
Задать условия, соединенные логическим оператором ИЛИ или И для нескольких столбцов.
Задать три или более условия для конкретного столбца с использованием, по крайней мере, одного логического оператора ИЛИ.
Задать вычисляемые условия.
С помощью данной команды можно извлекать строки из списков, вставлять копии этих строк в другую часть текущего листа.
Задание диапазона условий
Команда Расширенный фильтр требует задания условий отбора строк в отдельном диапазоне рабочего листа. Поскольку при фильтрации скрываются целые строки, диапазон условий лучше поместить выше или ниже списка. Если предполагается, что список будет расширяться, то диапазон условий рекомендуется размещать выше таблицы. Диапазон условий должен содержать, по крайней мере, две строки. За исключением вычисляемых условий, заголовки столбцов в верхней строке диапазона условий должны точно совпадать с заголовками столбцов таблицы. Для обеспечения точности эти заголовки лучше копировать из таблицы.
В диапазоне условий можно ввести любое количество условий, которые интерпретируются в соответствии со следующими правилами:
Условия на одной строке считаются соединенными логическим оператором И.
Условия на разных строках считаются соединенными логическим оператором ИЛИ.
Необходимо выбрать из списка товары, проданные на сумму больше 100000 у.е. или меньше 10000 у.е.. Для этого нужно выполнить следующие действия:
Вставить четыре строки в верхнюю часть листа «Отчет», для чего растянуть курсор мыши по номерам строк с 1-й по 4-ю включительно, выделив таким образом 4 строки таблицы, и выполнить команду меню Вставка|Строки (4 пустые строки появятся над таблицей).
Скопировать в ячейку С1 заголовок «Сумма» из шапки таблицы.
В ячейку С2 ввести <10000.
В ячейку С3 ввести >100000.
Установить курсор в область таблицы.
Активизировать пункт меню Данные.
Выбрать Фильтр и Расширенный фильтр. При этом появилось диалоговое окно Расширенный фильтр и вся таблица выделилась подвижной рамкой и цветным фоном.
В окне Обработка выбрать Фильтровать список на месте.
Обратить внимание, чтобы в окне Исходный диапазон был выделен блок ячеек, соответствующий всей выделенной таблице.
Установить курсор в окно Диапазон условий и выделить с помощью мыши диапазон С1:С3. Нажать ОК.
Проверить результат фильтрации и отменить расширенный фильтр с помощью команды Данные|Фильтр|Отобразить все.
Необходимо выбрать из списка товары, проданное количество которых меньше 500 и больше 100. Для этого необходимо:
Сформировать новый диапазон условий, скопировав в ячейку Е1 и F1 заголовок графы «Количество».
В ячейку Е2 ввести <500.
В ячейку F2 ввести >100.
Установить курсор в область таблицы.
Выбрать пункт меню Данные|Фильтр|Расширенный фильтр.
В окне Обработка выбрать Фильтровать список на месте.
Проверить, чтобы в окне Исходный диапазон был отражен блок ячеек, соответствующий всей таблице.
Установив курсор в окно Диапазон условий, указать в таблице с помощью мыши интервал E1:F2. Нажать ОК.
Проверить результат фильтрации и отменить расширенный фильтр с помощью команды Отобразить все.
В двух предыдущих упражнениях, применялось использование операторов ИЛИ и И для одного столбца таблицы.
Теперь необходимо ознакомиться с примером использования ИЛИ и И для нескольких столбцов таблицы.
Необходимо найти данные по товарам, проданным магазину «Техносила» за наличный расчет, а магазину «Техношок» по безналичному расчету, для этого:
Скопировать в ячейку Н1 блок ячеек D6:E6.
В ячейку Н2 ввести с клавиатуры Техносила, в ячейку I2 – Нал (обратить внимание, что вводимый в блок критериев текст условия должен полностью совпадать с данными исходной таблицы).
В ячейку Н3 ввести Техношок, в ячейку I3 – Безнал
Установить курсор в таблицу.
Выбрать меню Данные|Фильтр|Расширенный фильтр.
Установить переключатель в положение Фильтровать список на месте.
Проверить, чтобы в исходном диапазоне был выбран блок ячеек, соответствующий всей таблице.
Перейти в диапазон условий и выделить блок ячеек Н1:I3.
Щелкнуть по кнопке ОК.
Проанализировать результаты выборки.
Отменить фильтр, с помощью команды Данные|Фильтр|Отобразить все.
Снять выделение строк.
Примечание: при каждом выполнении команды «Расширенный фильтр» просматривается полный список в таблице, а не текущее множество ранее отфильтрованных строк. Вследствие этого совсем не обязательно использовать команду «Отобразить все» перед изменением фильтра.
Расширенный фильтр с использованием вычисляемых значений
Вычисляемые условия отличаются от обычных условий сравнения тем, что позволяют использовать сравнения со значениями, которые вычисляются формулой. Для правильного использования такой фильтрации необходимо выполнять следующие правила:
Заголовок над вычисляемым условием должен обязательно отличаться от любого заголовка столбца в таблице. Заголовок условия может быть пустым или содержать произвольный текст. Внимание! Это ограничение прямо противоположно требованию для обычных условий.
Ссылки на ячейки, которые находятся вне таблицы (списка), должны быть абсолютными (при адресации используется знак доллара).
Ссылки на ячейки в таблице должны быть относительными.
Необходимо вывести на экран данные о товарах закупленных магазинами по цене выше среднего значения, и поместить результат фильтрации в другую часть текущего листа, для этого:
В ячейку К1 ввести: «Среднее значение».
В ячейку К2 ввести формулу среднего значения цены =СРЗНАЧ(F7:F22).
В ячейку К5 ввести заголовок для диапазона условий: «Выше среднего».
В ячейку К6 ввести формулу сравнения: =F7<$К$2.
Формула условия сравнивает со средним значением ячейку F7, т.к. ячейка F7 является первой ячейкой в столбце «Цена» неотфильтрованного списка, и автоматически со средним значением будут сравниваться все нижерасположенные ячейки этого столбца.
Скопировать шапку таблицы в ячейку А25 для того, чтобы поместить результат фильтрации в другую часть рабочего листа (копировать можно не всю шапку таблицы, а отдельные названия граф в соответствии с требованиями пользователя).
Установить курсор в область таблицы.
Выбрать Данные|Фильтр|Расширенный фильтр.
В окне Обработка поставить переключатель в Скопировать результат в другое место.
В Диапазоне условий указать К5:К6.
В окне Поместить результат в диапазон установить курсор, а затем на рабочем листе указать с помощью мыши диапазон ячеек А25:I25.
Щелкнуть по кнопке ОК.
Проанализировать результаты фильтрации и убрать фильтр.
Того же результата фильтрации можно добиться и другим способом. Можно не рассчитывать отдельно вне таблицы среднее значение столбца «Цена», а в диапазоне условий под заголовком «Выше среднего» в ячейке К6 сразу ввести формулу сравнения: =F7>СРЗНАЧ($F$7:$F$22).
Выполнить фильтрацию таким способом самостоятельно и предъявить работу преподавателю.
Примечание: для блоков условий можно отдельно, по желанию пользователя, выделить рабочий лист или рабочую книгу и при работе с Расширенным фильтром обращаться к ним.
Анализ данных с помощью сводных таблиц
Средство Сводная таблица представляет собой еще один инструмент организации и подведения итогов данных. Этот инструмент комбинирует возможности работы со списками.
Сводная таблица является специальным типом таблицы, которая подытоживает информацию из конкретных полей списка или базы данных. При создании сводной таблицы с помощью Мастера можно задать нужные поля, организацию таблицы (ее макет) и тип выполняемых вычислений
Сводные таблицы обеспечивают удобный интерфейс для анализа данных различной сложности и разного объема, а также возможность быстрой перестройки макета сводной таблицы фактически только с помощью мыши без применения сложного программирования.
Сводная таблица – это средство для упорядочивания информации. При создании сводной таблицы пользователь распределяет информацию, указывая, какие элементы и в каких полях будут содержаться.
Для создания сводных таблиц используются различные источники данных. Это могут быть списки и таблицы, расположенные на рабочих листах Excel, либо внешние источники данных (таблицы, созданные с помощью других программ).
Сводная таблица является многомерной и всегда связана с источником данных. Она предназначена только для чтения, а изменения можно вносить в исходные таблицы. При этом можно изменять форматирование сводных таблиц, выбирать различные параметры вычислений. Для большей наглядности на основе сводной таблицы можно создать диаграмму, которая будет перестраиваться при изменении сводной таблицы.
Необходимо создать сводную таблицу с подведением итогов по продаже товаров магазинам-закупщикам по видам продукции и видам оплаты, для этого:
Установить курсор в любую ячейку с данными исходной таблицы « Отчет ООО "Техносервис" о продаже аудио- и видеотехники магазинам-закупщикам в 1 квартале».
Выбрать пункт меню Данные|Сводная таблица.
В группе Создать таблицу на основе данных, находящихся установить переключатель в Списке или базе данных Microsoft Excel.
В группе Вид создаваемого отчета установить переключатель Сводная таблица и нажать кнопку Далее.
Проверить, что в появившемся окне в опции Диапазон указан весь диапазон ячеек исходной таблицы, и нажать Далее.
В опции Поместить таблицу в установить переключатель в новый лист и нажать кнопку Макет.
Рис.22
При помощи мыши перетащить поле «Наименование магазина» (4-е сверху) в область Страница, поле «Вид продукции» - в область Строка, поле «Вид оплаты» - в область Столбец, поле «Сумма» - в область Данные и нажать ОК.
Нажать Готово.
Рис.23
Для управления просмотром данных сводной таблицы используются кнопки со стрелками, с помощью которых можно выбрать, какие элементы выводить на экран.
Редактирование сводных таблиц
Пользователь может менять формат сводной таблицы, переименовывать поля или элементы, добавлять новые строки или столбцы, а также делать другие преобразования с внешним видом сводной таблицы.
Чтобы изменить название поля или элемента сводной таблицы, необходимо выделить нужную ячейку и набрать новое имя, которое не должно повторять существующие в исходной таблице имена.
Необходимо изменить текст заголовка ячейки А3 на «Сумма продаж», для этого:
Выделить ячейку А3 сводной таблицы.
С клавиатуры ввести новое имя.
При создании сводной таблицы по умолчанию при подсчете итоговых значений используется функция Суммирования, которую можно изменить на любую другую функцию.
Для формирования новой сводной таблицы достаточно изменить макет существующей.
Для того чтобы сформировать сводную таблицу, отражающую данные о продажах магазинам-закупщикам и доли каждого из них в общей сумме закупок, необходимо:
Установить курсор в любую ячейку созданной сводной таблицы.
На панели инструментов Сводные таблицы (если данная панель инструментов на экране отсутствует, то выполнить команду Вид|Панели инструментов|Сводные таблицы) нажать кнопку со стрелкой Сводная таблица и выбрать Мастер.
Нажать кнопку Макет.
Для очистки макета оттащить поля в любое место окна.
При помощи мыши перетащить поле «Наименование магазина» (4-я сверху) в область Строка, поле «Сумма» - в область Данные и сделать двойной щелчок левой кнопкой мыши по данному полю. В открывшемся окне в опции Имя, ввести с клавиатуры «Сумма продаж» и нажать ОК.
При помощи мыши вторично перетащить поле «Сумма» в область Данные и сделать двойной щелчок левой кнопкой мыши по данному полю. В открывшемся окне, в опции Имя ввести с клавиатуры «Доля магазина», в этом же окне нажать кнопку Дополнительно>>. В опции Дополнительные вычисления нажать стрелку вниз и выбрать Доля от суммы по столбцу. Нажать ОК.
В окне макета нажать ОК.
В открывшемся окне проверить, что включена опция Существующий лист. Нажать кнопку Готово.
Просмотреть результат (рис.24)
Рис.24
Для того чтобы сформировать сводную таблицу, отражающую данные о средней сумме закупок магазинов по видам техники, необходимо:
Установить курсор в любую ячейку созданной сводной таблицы.
На панели инструментов Сводные таблицы нажать кнопку со стрелкой Сводная таблица и выбрать Мастер.
Нажать кнопку Макет.
Для очистки макета оттащить поля в любое место окна.
При помощи мыши перетащить поле «Наименование магазина» в область Столбец, поле «Вид продукции» - в область Строка, поле «Сумма» - в область Данные и сделать двойной щелчок левой кнопкой мыши по данной кнопке поля. В открывшемся окне в опции Операция выбрать Среднее и нажать ОК.
В окне макета нажать ОК.
Нажать кнопку Параметры.
В открывшемся окне Параметры сводной таблицы в опции Формат удалить флажки Общая сумма по столбцам и Общая сумма по строкам. Нажать ОК.
В открывшемся окне проверить, что включена опция Существующий лист. Нажать кнопку Готово.
Просмотреть результат (рис.25).
Рис.25
Групповые операции в сводных таблицах
Для демонстрации группировки данных в сводных таблицах необходимо создать сводную таблицу с данными о количестве проданных товаров в определенных диапазонах цен, для этого:
Установить курсор в любую ячейку созданной сводной таблицы.
На панели инструментов Сводные таблицы нажать кнопку со стрелкой Сводная таблица и выбрать Мастер.
Нажать кнопку Макет.
Для очистки макета оттащить поля в любое место окна.
При помощи мыши перетащить поле «Цена» в область Строка, поле «Количество» - в область Данные и нажать ОК.
В открывшемся окне проверить, что включена опция Существующий лист. Нажать кнопку Готово.
Щелкнуть правой кнопкой мыши в ячейке «Цена» полученной сводной таблицы.
В контекстном меню выбрать команду Группа и структура|Группировать.
В открывшемся окне Группирование в опции С_шагом ввести c клавиатуры 300 (минимальная и максимальная цена товара - 50 и 800-устанавливается автоматически из списка) и нажать ОК.
Просмотреть результат (рис.26).
Рис.26
Фиксация заголовков столбцов и строк
Для удобства работы с таблицами Excel предлагает способ закрепления левой и верхней частей таблицы с помощью пункта меню Окно и команды Закрепить области. Закрепление позволяет просматривать заголовки столбцов, строк или того и другого во время работы со множеством строк и столбцов. Фиксируются области таблицы, которые располагаются слева и выше активной ячейки таблицы, т.е. той, в которой установлен табличный курсор. Чтобы ознакомиться с данной командой, необходимо:
Перейти на лист «Отчет».
Установить курсор в ячейку С7.
Активизировать пункт меню Окно.
Выбрать команду Закрепить области.
Проверить с помощью линеек прокрутки, что шапка таблицы и первые два столбца зафиксированы на экране, а остальную часть таблицы можно просматривать.
Отменить фиксацию с помощью меню Окно и команды Снять закрепление областей.
Самостоятельно выполнить фиксацию только шапки таблицы и первого столбца таблицы, затем отменить фиксацию.
Скрытие столбцов или строк
Работая в среде Excel, можно скрывать (делать невидимыми) столбцы и строки электронной таблицы.
Необходимо сделать невидимым столбец «№ п/п», для этого:
Выделить столбец А, щелкнув кнопкой на его имени.
Выбрать пункт меню Формат, команду Столбец|Скрыть.
Отменить скрытие столбца, выбрав команду Формат|Столбец|Отобразить. Данную операцию можно осуществить, установив курсор мыши на границу столбцов, и когда он примет вид двойной двунаправленной стрелки, расширить скрытый столбец.
Аналогичные действия выполняются для скрытия строк электронной таблицы.
Защита ячеек и рабочих листов
Защита данных и формул приобретает особое значение, если с одной рабочей книгой работает несколько пользователей.
Рабочий лист по умолчанию не защищен, поэтому к его ячейкам и объектам можно легко получить доступ для внесения изменений.
После защиты рабочего листа доступ к нему будет значительно ограничен. При попытке изменить любой из защищенных компонентов на экране появится диалоговое окно с предупреждением.
Защитив рабочий лист, невозможно выполнить следующие действия:
Изменить содержимое или формат защищенных ячеек.
Переместить защищенную ячейку или объект.
Удалить или вставить строки или столбцы.
Изменить ширину столбца или высоту строки.
Определить имя для диапазона.
Создать на рабочем листе диаграмму или графический объект.
Для включения защиты рабочего листа «Отчет» необходимо:
Выбрать команду Сервис|Защита|Защитить лист.
В открывшемся диалоговом окне по желанию введите пароль, а все параметры защиты приведите в соответствие с рисунком (рис.27):
Рис.27
Снять защиту рабочего листа, выполнив команду Сервис|Защита|Снять защиту листа.
Для защиты определенного блока ячеек на рабочем листе без защиты остальных областей необходимо:
Выделить блок ячеек шапки таблицы, которые не будут защищены, и выполнить команду Формат|Ячейки|Защита.
Отключить параметр Защищаемая ячейка и нажать ОК.
Выбрать команду Сервис|Защита|Защитить лист.
В открывшемся диалоговом окне по желанию ввести пароль, а все параметры защиты привести в соответствие с рисунком (рис.28).
Р
ис.28
Проверить, что в ячейки шапки таблицы можно вносить изменения, а в остальные ячейки рабочего листа – нет.
Снять защиту рабочего листа, выполнив команду Сервис|Защита|Снять защиту листа.
Средства для анализа данных
Подбор параметра
Средство Подбор параметра предназначено для получения заданного значения в целевой ячейке путем подбора значений в ячейке–параметре. С помощью данного средства можно найти аргумент, при котором получается задаваемое решение. При этом ячейка-параметр должна содержать значение, но не формулу и влиять на результат, который требуется получить.
Например, можно определить, как изменить количество продаваемых товаров для получения заданной величины выручки магазина.
Необходимо определить, сколько нужно продавать систем караоке, для того, чтобы выручка магазина «Техношок» увеличилась до 300000 у.е., для этого:
Открыть книгу «Продажи.xls».
Скопировать содержимое таблицы листа «Техношок» (А1:Н11) на новый лист в ячейку А1 и назвать его «Анализ данных».
Установить курсор в ячейку Н11 (итог по графе «Выручка») и выбрать пункт меню Сервис|Подбор параметра.
В диалоговом окне Подбор параметра в опции Значение ввести с клавиатуры 300000.
В опции Изменяя значение ячейки щелкнуть левой кнопкой мыши в ячейке Е7 (количество продаж систем караоке). Нажать ОК.
Р
ис.29
Просмотреть результат решения в текущей таблице и окне Результат подбора параметра. Нажать кнопку Отмена.
Р
ис.30
Таблица подстановок
Таблицей подстановки данных называется диапазон ячеек, показывающий, как изменение значений подстановки влияет на возвращаемый формулой результат. Таблицы подстановки обеспечивают быстрый доступ к выполнению одной операции разными способами, а также возможность просмотра и сравнения полученных результатов. В Excel существует возможность применения на одном рабочем листе нескольких таблиц подстановок, если необходимо:
Изменять одно исходное значение, просматривая результаты одной или нескольких формул.
Изменять два исходных значения, просматривая результаты только одной формулы.
Таблица подстановки с одной изменяющейся переменной
Необходимо создать таблицу подстановки, показывающую изменение суммы выручки при изменении количества продаж телевизора, для этого:
Установить курсор в ячейку Е14 на листе «Анализ данных» и ввести формулу =СУММ(Н3:Н10).
В ячейку D14 ввести с клавиатуры «Телевизор», в ячейку D15 ввести 40, в ячейку D16 – 80, в ячейку D17 – 100 (обратить внимание, что введенные значения количества продаж телевизора располагаются на столбец левее и на строку ниже формулы, по которой определяется выручка магазина).
Выделить блок ячеек D14:Е17, выбрать команду меню Данные|Таблица подстановки.
В
открывшемся диалоговом окне Таблица подстановки, установив курсор в опции Подставлять значения по строкам, щелкнуть левой кнопкой мыши в ячейке Е10 и нажать ОК (рис.31).
Рис.31
Таблица подстановки с двумя изменяющимися переменными
Необходимо создать таблицу подстановки, показывающую изменение суммы выручки при изменении количества продаж видеомагнитофона и видеокамеры, для этого:
Установить курсор в ячейку Е20 и ввести формулу =СУММ(Н3:Н10).
В ячейку Е21 ввести с клавиатуры 60, в ячейку Е22 ввести 80, в ячейку Е23 – 100, в ячейку F20 – 50, в G20 – 70, в Н20 - 90 (обратить внимание, что введенные значения количества продаж видеомагнитофона располагаются в ячейках столбца Е непосредственно под формулой, по которой определяется выручка магазина, а значения количества продаж видеокамеры – в ячейках строки справа от формулы).
Выделить блок ячеек Е20:Н23, выбрать команду меню Данные|Таблица подстановки.
В
открывшемся диалоговом окне Таблица подстановки, установив курсор в опции Подставлять значения по столбцам, щелкнуть в ячейке Е9, а в опции Подставлять значения по строкам щелкнуть левой кнопкой мыши в ячейке Е3 и нажать ОК (рис.32).
Рис.32
Проверка результатов с помощью сценариев
В работе часто возникают задачи, имеющие множество исходных данных и множество результатов, причем необходимо четко представлять, как изменения первых влияют на последние. Ощутимую помощь в анализе такого рода задач могут оказать сценарии Excel.
Сценарий – это инструмент, позволяющий моделировать различные экономические, математические, физические и др. задачи. Он представляет собой зафиксированный в памяти компьютера набор значений ячеек рабочего листа, т.е., создав сценарий можно получить возможность узнать, что произойдет с результатом, если поменять исходные значения в некоторых ячейках листа. Кроме того, в случае необходимости всегда можно вернуться к одному из вариантов, рассмотренных ранее.
Необходимо спрогнозировать выручку магазина в феврале и марте, в зависимости от изменения количества продаж товаров, для этого:
Скопировать исходную таблицу (блок ячеек А1:Н11) с листа «Анализ данных» на новый рабочий лист в ячейку А1, назвать лист «Прогноз».
Установить курсор в ячейку Е3, выбрать пункт меню Вствка|Имя|Присвоить.
В диалоговом окне Присвоение имени в опции Имя ввести с клавиатуры «Видеомагнитофон» и нажать ОК. Аналогичным способом присвоить соответствующие имена ячейкам Е4:Е10 (если наименование состоит из нескольких слов, между ними ставится знак подчеркивания «_»). Ячейке Н11 присвоить имя «Выручка».
Установить курсор в любую ячейку рабочего листа и выбрать команду Сервис|Сценарии.
В диалоговом окне Диспетчер сценариев нажать кнопку Добавить.
В диалоговом окне Добавление сценария в опции Имя набрать с клавиатуры «Прогноз на февраль», в опции Изменяемые ячейки указать блок ячеек Е3:Е10 и нажать ОК.
В диалоговом окне Значения ячеек сценария заменить текущие значения на:
Видеомагнитофон – 50
Видеоплеер - 15
Магнитола -60
Музыкальный_центр - 20
Система_караоке - 12
Аудиоплеер - 100
Видеокамера - 30
Телевизор – 30
В диалоговом окне Добавление сценария в опции Имя набрать с клавиатуры «Прогноз на март», в опции Изменяемые ячейки убедиться, что указан блок ячеек Е3:Е10, и нажать ОК.
В диалоговом окне Значения ячеек сценария заменить текущие значения на:
Видеомагнитофон – 40
Видеоплеер - 12
Магнитола -45
Музыкальный_центр - 27
Система_караоке - 15
Аудиоплеер - 120
Видеокамера - 25
Телевизор – 35
Нажать кнопку ОК.
В диалоговом окне Диспетчер сценариев нажать кнопку Отчет.
В диалоговом окне Отчет по сценарию включить параметр Структура в опции Тип отчета и нажать ОК. Просмотреть результат (рис.33).
Рис.33
|
|
|
Скачать 1.74 Mb.