Методические указания по курсу

Электронные таблицы

Excel

Основы работы

Методические указания по курсу

«Информатика» (часть III)

 

Ростов-на-Дону

 

2002

 

 

Излагаются основы работы в среде Excel. Рассмотрены средства и возможности использования электронных таблиц MS Excel  на примере лабораторных работ. Предложены варианты индивидуальных заданий. Для студентов всех специальностей дневной и заочной формы обучения.

 

Составитель:

к.ф.-м.н., доц. Власков Г.А.

 

Начало работы. Общие сведения о книгах M S Excel

Книга в MS Excel представляет собой файл, используемый для обработки и хранения данных. Как правило, такой файл имеет расширение xls. Каждая книга может состоять из нескольких листов, поэтому в одном файле можно поместить разнообразные сведения и установить между ними необходимые связи.

 Итак, вызовем программу excel. exe с помощью ярлыка на рабочем столе, либо из Главного меню, либо из панели «MS Office». В результате на экране появляется окно приложения «Microsoft Excel - Книга1», которое содержит обычные элементы управления – меню, заголовок, кнопки управления окон, панели инструментов, полосы прокрутки и т.д.

Рис. 1

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

Общие сведения о листах MS Excel

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

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

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

Создание книги

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

2. Чтобы создать новую пустую книгу, выберите вкладку Общие и дважды щелкните значок "Книга". Для создания книги по шаблону выберите вкладку Решения или вкладку со списком пользовательских шаблонов, а затем дважды щелкните шаблон, по которому создается книга.

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

Открытие книги на жестком диске или в сети

1. Нажмите кнопку Открыть .

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

3. Дважды щелкните книгу, которую нужно открыть.

Сохранение книги

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

Сохранение новой книги

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

2. В поле Папка укажите диск и папку, в которую будет помещена книга.

Чтобы сохранить книгу в новой папке, щелкните Создать папку.

3. В поле Имя файла введите имя книги (лучше, если имя отражает ее содержимое, поэтому не рекомендуется оставлять предлагаемое имя «Книга1»).

4. Нажмите кнопку Сохранить.

Выделение листов в книге

Если выделено несколько листов, изменения, сделанные на текущем листе, повторяются на всех остальных листах. Это может привести к потере данных.

Чтобы выделить	Сделайте следующее
Отдельный лист	Щелкните ярлычок листа.
Два или более смежных листа	Щелкните ярлычок первого листа, затем, удерживая клавишу SHIFT, щелкните ярлычок последнего листа.
Два или более несмежных листа	Щелкните ярлычок первого листа, затем, удерживая клавишу CTRL, щелкните ярлычки остальных листов.
Все листы книги	Установите указатель на произвольный ярлычок листа и нажмите правую кнопку мыши. Затем выберите команду Выбрать все листы в контекстном меню.

Вставка нового листа

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

Чтобы вставить несколько листов, выберите необходимое количество листов, удерживая нажатой клавишу SHIFT, затем выполните команду Лист из меню Вставка.

Удаление листов из книги

1. Выберите листы, которые нужно удалить.

2. Выберите команду Удалить лист в меню Правка.

Переименование листа

1. Выберите двойным нажатием кнопки мыши нужный ярлычок листа.

2. Введите новое имя поверх старого.

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

Ввод и исправление данных

Ячейка, на которую установлен указатель, называется активной. Активная ячейка обрамлена рамкой (на рис. 1 такой является ячейка А1). Теперь в ячейку можно внести данные. Чтобы перевести ячейку в режим редактирования по ней нужно дважды щелкнуть (в ячейке появится клавиатурный курсор) либо сразу начинать ввод, который автоматически переводит ячейку в этот режим. Редактирование ячейки закрывается нажатием клавиши Enter или щелчком по другой ячейке.

Например, в ячейки А1 и В1 можно внести числа, а в ячейку С1 записать формулу =А1*В1 (формула всегда начинается с символа “=”). Это означает, что ячейка С1 является вычисляемой и ее значение равно произведению чисел, записанных в ячейки А1 и В1. Теперь в этих ячейках числа можно менять. При этом содержимое ячейки С1 будет автоматически пересчитываться (в том случае, если установлен в настройках Excel режим автоматического вычисления. Это можно проверить, вызвав команду Сервис – Параметры –вкладка Вычисления).

Например, введите в ячейку C1 число 1958; в ячейку А2 - текст Первый опыт; в ячейку С2 - формулу =С1+17 (после нажатия клавиши Enter должно появиться число 1975, то есть 1958+17); введите в ячейку С3 число 3,14159. Обратите внимание на то, что дробная часть отделяется от целой части запятой (впрочем, это зависит от настроек Windows); введите в ячейку C4 дату: 18.11. Обратите внимание, что на самом деле ввелось "18 ноя".

Исправление ошибок ввода. Если клавиша Enter не нажата, то данные в таблице еще не зафиксированы. Можно нажать клавишу Esc чтобы отменить редактирование. Как исправить уже введенную ячейку, если Enter уже была нажата? Нужно подвести курсор к ячейке, нажатьклавишуF2, которой ячейка переводится в режимредактирования, или дважды щелкнуть. Теперь данные можно исправить и нажать Enter.

Замените в ячейке А2 текст Первый опыт на какой-либо другой. Выполненную Вами операцию можно в любой момент отменить кнопкой Отменить , а вернуть выполненную отмену можно кнопкой Вернуть . Эти кнопки расположены на панели Стандартная. Отмены и возвраты можно повторять сколько угодно раз до тех пор, пока кнопки активны. Пассивные кнопки имеет серое изображение.

Иногда нужно выполнить операции над группой ячеек. Например, удалить сразу данные из нескольких ячеек, перетащить данные из них на другое место с сохранением порядка размещения и т. д. Для этого группу ячеек сначала выделяют, а потом выполняют нужную операцию. Выделенная группа ячеек называется диапазоном ячеек  и обозначается, например, так: A 1: C 2. Выделение ячеек снимается щелчком в любом месте окна, в том числе по любой ячейке диапазона.

Групповой перенос.

1. Введите в ячейки диапазона С4:D7 какие-нибудь числа.

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

3. Щелкните на панели Стандартная на кнопке Вырезать . Данные диапазона будут выделены бегущей рамкой.

4. Щелкните на ячейке E10, затем на кнопке Вставить  панели Стандартная. Данные появится на новом месте, а со старого места исчезнут. Таким способом совершен перенос данных.

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

Так можно вставлять одни и те же данные сколько угодно раз до тех пор, пока в буфере не появятся другие данные после нового копирования. Причем эти операции можно выполнять не только из Excel, а из любой другой программы, например Word, если она работает параллельно. Можно скопировать данные из Word и вставить их в таблицу Excel и наоборот. Попробуйте это.

Групповое удаление. Для удаления данных из выделенного диапазона нужно после выделения диапазона нажать клавишу Delete.

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

Сохраните рабочую книгу так, как это было описано выше.

После сохранения рабочей книги выполните команду Файл – Закрыть. При этом окно рабочей книги очистится. Щелкните на кнопке Открыть, найдите в появившемся окне ранее сохраненный файл и щелкните на кнопке Открыть. Сохраненная книга появится в окне программы Excel.

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

Теперь у Вас уже есть необходимый минимум знаний для работы с электронными таблицами. Создадим таблицу, которая показана на рис. 2.

Рис. 2.

Закройте старую книгу и создайте новую кнопкой Создать.

Сначала в таблицу нужно ввести строки с наименованием дисциплин в столбец А. Они расположены в строках 3-10. Для того, чтобы увеличить ширину столбца установите курсор на серый заголовок между ячейками с надписями А и В так, чтобы он принял форму креста с разнонаправленными стрелками (на рисунке он находится в заголовке между столбцами D и E). Нажмите левую клавишу мыши и плавно сдвиньте границу вправо так, чтобы надписи полностью поместились в ячейки. Далее занесите в ячейки имеющиеся в таблице числа.

Другой способ подбора ширины столбца следующий. Выделите нужные ячейки и выполните команду Формат – Столбец – Автоподбор ширины. Теперь ширина будет соответствовать ширине содержимого ячеек. Это другой и вполне совершенный способ выравнивания ширины столбцов.

Введите слово «ИТОГО» в строку 11, как показано на рис. 2, и отформатируйте его кнопкой По правому краю  панели Форматирование.

Введите надписи к столбцам, которые расположены в строке 2. Выделите ячейки этой строки, щелкните на кнопке По центру  панели Форматирование, затем на кнопке Курсив (на ней надписана буква К).

Чтобы надписи были отчеркнуты линиями снизу и сверху как на рис. 2, не убирая выделения щелкните на указателе справа от кнопки Границы  панели Форматирование, затем выберите в предложенном наборе подходящий элемент разметки (границы сверху и снизу) и щелкните по нему. Аналогично разметьте все остальные части таблицы, которые выделены на рис. 2 линиями границ.

Вновь вернемся к строке 2 для того, чтобы увеличить высоту ячеек. Выделите ячейки строки. Выполните команду Формат-Строка-Высота и установите во всплывшем окошке высоту 19,75. Теперь текст нужно выровнять по высоте. Не снимая выделения ячеек выполните команду Формат-Ячейки. В ответ всплывет окно Формат ячеек, которое показано на рис. 3. Установите в нем свойство Выравнивание – по вертикали в значение по центру и нажмите кнопку ОК.

Рис. 3.

Теперь займемся строкой 1, в которой находится заголовок таблицы. Выделите ее ячейки и выполните команду Формат - Ячейки. Вновь появится окно рис. 3. В нем на закладке Выравнивание щелчком установите галочку в окошко объединение ячеек и щелкните на кнопке ОК. Теперь введите в объединенную ячейку текст заголовка. Отформатируйте его по центру. Установите размер символов в значение 14.

После ввода данных в ячейки ширину столбцов можно еще раз отрегулировать. Для этого нужно выделить ячейки таблицы и выполнить команду Формат-Столбец-Автоподбор ширины. Предварительная часть работ закончена. Таблица должна иметь вид рис. 2.

Автоматизация расчетов

Приступим к автоматизации расчетов. Суть расчетов заключается в следующем:

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

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

· найти общее количество часов для всех дисциплин по всем видам занятий (общая сумма).

Сначала найдем суммарное количество часов для первой дисциплины (физика). Плавно проведите курсором от ячейки B3 до ячейки G3 и щелкните по кнопке Автосумма  панели Стандартная. В ячейке G3 появится сумма чисел из ячеек B3:F3. Щелкните дважды по ячейке G3. Там содержится формула =СУММ(B3:F3), т. е. формула, вызывающая функцию СУММ суммирования нужных нам чисел, которую Excel подставил после нажатия кнопки.

Можно было бы аналогичным способом выполнить расчет по другим дисциплинам. Но мы применим другой, более эффективный прием. Щелкните на ячейке G3. Смещайте курсор к правому нижнему краю ячейки. Как только он примет форму маленького черного крестика , который называется маркером автозаполнения, нажмите левую клавишу мыши, протяните курсор по столбцу от ячейки G3 до ячейки G10 и отпустите клавишу. Теперь столбец G в строках 3-10 заполнился нужными нам суммами. Измените в любой ячейке диапазона B3:F10 число, нажмите Enter или щелкните по любой другой ячейке. Программа немедленно пересчитает суммы столбца G в соответствии с новыми данными.

Теперь заполним строку 11, которая должна содержать суммы по видам занятий. Сначала заполним ячейку B11. Она соответствует количеству часов по всем дисциплинам для вида Лекции. Выделите ячейки B3:B10, протянув по ним курсор с нажатой левой клавишей, затем щелкните на кнопке Автосумма . В ячейке B11 появится сумма чисел, размещенных в выделенных ячейках столбца, – вновь Excel “догадался”, что именно в эту ячейку нужно вставить нужную нам сумму.

Заполним всю строку 11. Щелкните по ячейке B11. Найдите маркер автозаполнения (черный крестик) в правом нижнем углу и протяните курсор до ячейки G10. Все выделенные ячейки заполнились требуемыми суммами.

Наконец, протяните курсор по ячейкам G3:G11 и нажмите кнопку Автосумма. Теперь правый столбец и нижняя строка заполнены числами.

Щелкните по ячейке G11, затем по кнопке с надписью Ж на панели Форматирование. Итоговое число 725 выделится полужирным начертанием.

Окончательный вид рассчитанной таблицы показан на рис. 4.

Измените какие-нибудь числа в прямоугольнике B3:F10. Обратите внимание, что после нажатия клавиши Enter или щелчка по другой ячейке суммы в строке 11 и столбце G тут же автоматически пересчитываются.

Рис. 4.

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

Всякую таблицу можно упорядочить по какому-нибудь критерию. Эта операция называется сортировкой. Предположим, что необходимо таблицу рис. 4. расположить в алфавитном порядке следования дисциплин. Это значит, что таблицу нужно отсортировать по полю Дисциплины.

Рис. 5.

Поскольку нужно сортировать не всю таблицу, а только ее строки 3-10, то сначала их нужно выделить. После выделения выполните команду Данные - Сортировка. При этом появится окно рис. 5, в котором следует установить способ сортировки. Оно состоит из трех списков для выбора полей сортировки, справа от которых расположены опции порядка сортировки. В верхнем списке нужно выбрать поле Дисциплины и справа от него установить опцию по возрастанию.

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

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

Ориентация текста в ячейках

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

· Выделите строку 2 и выполните команду Формат-Ячейки. В открывшемся окне Формат ячейки (рис. 3) на закладке Выравнивание установите справа ориентацию текста в 90 градусов и нажмите кнопку ОК. Надписи теперь расположены вертикально, но их размер слишком мал и требует коррекции.

· Для увеличения размеров надписей выполните команду Формат - Строка-Автоподбор высоты. Теперь надписи имею прежний размер и вертикальную ориентацию.

· Теперь ширину столбцов можно уменьшить. Для этого выделите всю таблицу и выполните команду Формат-Столбец-Автоподбор ширины. Таблица уменьшилась, но теперь не вмещается ее заголовок. Щелкните по верхней ячейке с надписью-заголовком, назначьте размер 12, начертание – жирное.

Печать таблицы

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

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

Работа с большими таблицами. Получение данных из внешних источников.

В реальной практике работы в Excel обычно приходится оперировать большим числом данных, когда таблица может иметь десятки столбцов, а также десятки, сотни, а иногда и тысячи строк. Иногда данные хранятся в другом месте, и их требуется перенести для обработки в Excel. Эти данные могут находиться не только в другом файле, но и на другом компьютере, подключенном к локальной сети или даже в сети INTERNET. Кроме того, способ организации этих данных может отличаться от таблиц Excel. Тем не менее, в Excel предусмотрена возможность сформировать запрос и получить нужные данные из любых доступных источников. Это осуществляет специальная надстройка в MS Office, которая носит название MS Query.

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

Рассмотрим типичную потребность торгового предприятия в учете продажи товаров. Запустите Excel, создайте новую книгу. На ее первом листе нужно будет поместить данные, содержащиеся в файле базы данных формата MS Access. В качестве учебного примера используем файл Tovar2000.mdb. Эта база данных содержит следующую информацию: вид товара, наименование товара, дата заказа, количество, цена за единицу, заказчик и номер варианта. Вся информация помещается в четыре связанных между собой таблицы. При обращении к базе данных необходимо выбрать только те записи, которые соответствуют номеру варианта, равному N. Пусть N=0.

Рис. 6.

Вызовем команду Данные-Внешние данные-Создать запрос. Появится окно, представленное на рис. 6. Следует выбрать нужный нам источник (База данных MS Access) и щелкнуть кнопку OK. Теперь для подключения к источнику данных необходимо найти и указать упомянутый файл Tovar2000.mdb.

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

Рис. 7.

Рис. 8.

Далее (щелкните такую кнопку) окончательно сформируем запрос (нам нужны не все записи, а только те, что соответствуют варианту № 0. Поле «Вариант» есть в таблице «Заказы»). Сейчас мы находимся в среде MS Query. В верхней части окна представлены таблицы источника данных, которые были выбраны на предыдущем шаге. Они связаны по ключевым полям (см. рис. 8). Если по каким-либо причинам связь не установилась, не огорчайтесь. Вы с легкостью можете установить нужные связи вручную. Мышью достаточно перетащить поле одной таблицы к соответствующему полю другой таблицы, и между таблицами протянется ниточка. Эта ниточка и указывает на связь таблиц.

Перейдем к отбору (фильтру) данных. Вызов команды Условия - Добавить условия выводит окно, показанное на рисунке 9. Здесь нужно выбрать из списка Поле «Номер варианта» из таблицы «Заказы», выбрать Оператор «равно», а Значение поставить равным нулю.

Рис. 9.

Чтобы не вводить значение с клавиатуры, можно получить список всех значений, содержащихся в выбранном поле (кнопка Значения…). Щелкните кнопку Добавить, а затем Закрыть. Мы выполнили свою задачу, хотя MS Query предоставляет более богатые возможности. Вполне возможно добавление нескольких условий, соединенных операциями И или ИЛИ (обратите внимание на соответствующие селекторные кнопки). Выбор групповой операции позволит найти сумму или среднее для числовых данных.

Введенное нами условие появилось в рабочем окне MS Query. В нижней части окна отражаются результаты запроса. Сравните полученное на вашем компьютере с тем, что было ранее (как на рис. 8).

Прежде чем сделать последний шаг и отправить данные в рабочую книгу, загляните в другой режим просмотра сформированного запроса. Выберите команду Вид-Запрос SQL …. Теперь можно просмотреть и пролистать текст сделанного нами запроса на специальном и достаточно стандартизированном языке. Этот язык, называемый «SQL» служит для управления базами данных с форматами очень широкого спектра. Например, наш запрос можно было написать, используя этот язык, не прибегая к помощи мастера запросов. Впрочем, как видите, и без этих знаний в данном случае можно решить задачу. Поэтому не будем ничего исправлять в тексте и щелкнем кнопку Отмена.

Теперь мы опять в рабочем окне MS Query. Выберем команду Файл-Вернуть данные в MS Excel. На промежуточный вопрос «куда помещать полученные данные», укажем ячейку A1 первого листа нашей рабочей книги. Результат представлен на рис. 10. В первой строке размещаются заголовки столбцов. Excel активизирует панель Внешние данные. Одним щелчком мыши или выбором команды Данные - Обновить данные можно получить свежую информацию по сформированному запросу. Это очень важно, ведь источник может постоянно обновляться. В случае необходимости можно запрос корректировать.

Рис. 10.

Предположим все же, что данные больше обновляться не будут, и поработаем с рабочим листом. Добавим два столбца: «год» и «цена товара».

Удаление строк и столбцов

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

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

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

Если таблица содержит достаточно большое количество данных, то часто возникает потребность отобрать и систематизировать отдельные данные по определенному признаку или набору признаков. Например, может потребоваться узнать сколько в созданной ранее таблице зафиксировано заказанных товаров АО «Природа» за май месяц и на какую сумму, или мы захотим систематизировать товары по каждому заказчику в отдельности с простановкой сумм по каждому из них и общей суммой продаж. Таких потребностей может накопиться множество, и хотелось бы чтобы результат можно было получить быстро и в удобном виде. Excel имеет такие возможности.

Откройте созданную на прошлой лабораторной работе книгу.

Простой отбор данных

Сначала решим простую задачу. Показать в таблице только те заказы, которые относятся к ООО "Интер-Зоо".

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

Щелкните по ярлычку в ячейке Заказчик. Распахнется список, показанный на рис. 11.

Рис. 11.

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

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

Щелкните в списке на строке ООО "Интер-Зоо". Будет произведена фильтрация покупателей по полю Заказчик, его значению ООО "Интер-Зоо". Результат фильтрации показан на рис. 12. В таблице показаны только те заказы, которые сделаны ООО "Интер-Зоо".

Рис.12.

Сложный отбор данных

Предположим, потребовалось отфильтровать из таблицы заказчиков: фирму «Челюсти» и центр «Престиж», которые заказали товары по цене от 100 до 400 руб. Щелкните по ярлычку ячейки Заказчик, затем в списке – по строке Условие (рис. 11). В окне Пользовательский автофильтр выберите условия так, как показано на рис 13. Установите опцию «ИЛИ». Это означает, что следует отбирать все строки, относящиеся к обоим заказчикам. Закройте окно рис. 13 кнопкой ОК.

Рис. 13

Щелкните в таблице по ярлычку в ячейке Цена, в списке щелкните по строке Условие. Установите в окне Пользовательский автофильтр параметры отбора «больше или равно 100» и «меньше или равно 400». Обратите внимание, что теперь использована опция «И». Это значит, что нужны цены, которые одновременно больше или равны 100 и меньше или равны 400. Если бы мы установили опцию «ИЛИ», т.е. потребовали раздельного выполнения условий, то в фильтрованный список попали бы, например, товары с ценами, которые меньше 100, т. к. они удовлетворяют условию меньшие или равно 400. Это привело бы к ошибке фильтрации. Просмотрите результат и попробуйте какие-либо другие условия.

Итоги по группам

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

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

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

Рис. 14.

Теперь таблица кроме введенных строк содержит еще желаемые строки с итогами по каждому заказчику и общий итог по выбранным полям. Смысл появившихся слева обозначений в столбцах с заголовком “1 2 3” прост. Левая длинная “скобка” указывает выбранный диапазон товаров, средние скобки – диапазоны отдельных заказчиков, точки столбца 3 – строки товаров отдельного заказчика. “Минус” на кнопках означает, что диапазон распахнут. Его можно закрыть этой кнопкой. После закрытия на кнопке появляется “плюс”. Поупражняйтесь с кнопками, раскрывая и закрывая диапазоны. Попробуйте также щелкнуть по кнопкам с надписями “1 2 3”. Весьма удобный способ представления итоговых таблиц.

Сводные таблицы

Exсel имеет средство, с помощью которого можно на основании одной таблицы формировать различные сводные таблицы.

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

Прежде всего придется убрать в нашей таблице группировку и промежуточные итоги. Рекомендуем сначала скопировать все данные на другой лист, чтобы не терять все недавно проделанное, а потом сделать следующее. Выделив всю таблицу, кроме первой строки, вызовите команду Данные-Группа и структура-Удалить структуру. Затем удалите строки, содержащие промежуточные итоги. Теперь можно приступать к созданию сводных таблиц.

Снова выделите всю таблицу кроме первой строки и выполните команду Данные-Сводные таблицы. Будет запущен Мастер сводных таблиц, который за несколько шагов создаст нужную Вам сводную таблицу.

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

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

Рис. 15.

Дважды щелкните по перемещенной в таблицу кнопке с надписью Общая цена. Откроется окно Вычисление поля сводной таблицы, которое показано на рис. 16. Выберите в нем операцию Сумма (обратите внимание на другие операции списка – пригодится). Нажмите кнопку Формат и назначьте формат Финансовый, 2 знака в дробной части, обозначение – Нет (к суммам не нужно приписывать наименования). Закройте все окна кнопками ОК, и в последнем окне, выбрав опцию новый лист, щелкните кнопку Готово. Сводная таблица создана. Таблица разместится на новом листе с наименованием Лист4, ярлычок которого можно видеть в нижней части окна. Самостоятельно разберите возможности работы с полученной сводной таблицей. В частности, команда Сводная таблица-Формулы-Вычисляемое поле позволила бы обойтись без столбца Общая цена. Также найдите возможность фильтрации данных сводной таблицы по своим значениям.