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

• Выделить ячейки G3: G17;
• Выполнить команду меню Правка/Заполнить/Вниз.
7. Ввести данные в созданную таблицу можно с помощью команды меню, Данные/Форма (для этого необходимо выделить всю таблицу с названиями столбцов). Выполните самостоятельно.
8. Подвести итог по графе Сумма:
• Установить курсор в ячейку G18;
• Выбрать пиктограмму Автосумма на панели Стандартная.
9. Сделайте обрамление таблицы:
• Выделите ячейки А3:G17;
• Выбрать пиктограмму Границы на панели Форматирование (меню Формат/Ячейки вкладка Граница).
10. Переименуйте данный лист:
• Установить курсор на ярлычок листа;
• Нажмите правую клавишу;
• В контекстном меню выберите пункт Переименовать;
• Введите имя – соответствующей вашей таблицы месяц.

3. Сформировать аналогичную таблицу за следующий месяц. Для этого:

1. Скопировать таблицу на Лист2;

• Установить указатель мыши на прямоугольнике пересечения столбцов и строк;

• Щелкнуть левой кнопкой мыши;
• Выполнить команду меню Правка/Копировать, или вызов контекстного меню правой кнопкой мыши – команда Копировать, или пиктограмма Копировать на панели Стандартная;
• Перейдите на Лист2;
• Выполнить команду меню Правка/Вставить, или вызов контекстного меню правой кнопкой мыши – команда Вставить, или пиктограмма Вставить на панели Стандартная.

Таким образом, копируются не только данные, но и форматы.

3.2.Внести  необходимые корректировки, а  именно:

   Изменить  месяц в графе Дата отгрузки:

• Выделить ячейки D3:D17;
• Выполнить команду меню Правка/Заменить;
• В открывшимся окне указать в строке Найти: .09 (т.е. текущей месяц), в строке Заменить на : .10 (на нужный месяц);

Изменить  дату в графе Дата отгрузки:

• Выделить ячейки D3:D17;
• Выполнить команду меню Правка/Очистить/Все или контекстное меню Очистить содержимое;
• Не снимая выделения, введите нужную дату в ячейку D3;
• Выполнить команду меню Правка/Заполнить/Прогрессия, указать тип – Даты, шаг – 3, единицы – День, предельное значение – 31.10.06 (т.е. последнее число введенного месяца);

3.3.Переименуйте  лист в названия месяца.

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

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

Часть 2. Фильтрация данных. Графики.

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

    Фильтрация позволяет находить и отбирать для обработки часть записей (строк), которые содержат определенное значение или отвечают определенным критериям. Остальные строки при этом скрыты.

1.1.Установка  автофильтра.

Последовательность  действий:

• Установить курсор в диапазон таблицы;
• Выберем команду Данные/Фильтр/Автофильтр;

    Метки (заголовки) столбцов преобразуются  в раскрывающиеся списки (кнопки-стрелки), в которых можно задавать нужные критерии для поиска данных. В раскрывающемся списке выводятся все значения, встречающиеся в столбце, и дополнительные опции: Все, Первые 10, Условие, Пустые или Непустые.

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

    Если  необходимо найти записи, содержащий похожий, но не совпадающий полностью  текст, на помощь проходят специальные  символы, называемые символами шаблона (* -звездочка и ? – знак вопроса).

    Для наложения нескольких условий поиска используется логические операции и, или (and, or).

• Удалить Автофильтр - командой Данные/Фильтр/Автофильтр.

1.2.Установить  расширенный фильтр.

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

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

• Скопировать шапку таблицы в ячейку А20 для ввода условий фильтрации;
• Ввести условия для фильтрации:

1. Вывести данные равные, какому-либо количеству;

2. Вывести данные по количеству или цене больше или меньше, заданного значения;
3. Вывести количество, находящиеся в определенном промежутке;
4. Вывести получателей, начинающихся на какую-либо первую букву;
5. Вывести получателей, начинающихся на какую-либо вторую букву;
6. Вывести товары, начинающиеся на две какие-либо буквы;
7. Вывести данные за определенный месяц.

• Выберем команду Данные/Фильтр/Расширенный фильтр…;

• В открывшимся окне установить переключатель Обработка/фильтровать список на месте или скопировать результат в другое место;
• Задайте Исходный диапазон: диапазон таблицы вместе с шапкой и Диапазон условий: ячейку шапки и ячейку или ячейки с условием;
• Задайте, при необходимости, Поместить результат в диапазон: указать диапазон новой таблицы вместе с шапкой, куда будет выводиться результат;
• Отметить фильтрацию командой Данные/Фильтр/Отобразить все.

 

2. Построить три вида диаграмм (гистограмма, график, круговая).

    Графики и диаграммы лучше всего строить  по шагам и для построения сформировать отдельную таблицу.

Последовательность  действий:

• Выполнить команду меню Вставка/Диаграмма;
• Загрузиться Мастер диаграмм, шаг 1 – Тип диаграммы, выбрать нужный тип по вкладкам Стандартные и Нестандартные;
• Нажмите Далее;
• Шаг 2 – Источник данных диаграммы; отмечается диапазон данных, для построения графика ли диаграммы;
• Нажмите Далее;
• Шаг 3 – Параметры диаграммы; выбираются параметры диаграммы; Заголовки, Подписи данных, Легенда и т.д.;
• Нажмите Далее;
• Шаг 4 – Размещение диаграммы, устанавливается переключатель Поместить диаграмму на листе: отдельным, имеющимся;
• Нажмите Готово.

    Диаграмма построена. Редактирование ее осуществляется, с помощью меню Вставка/Диаграмма, при этом диаграмма должна быть выделена или с помощью Контекстного меню (выделить диаграмму, нажать правую клавишу мыши, в появившимся окне, выбрать необходимое, для редактирование меню). 

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

1. С помощью  установки связи, создать “Ведомость отгрузки товаров (продукции) за (квартал) 2006 год”. Для этого:
1. Если в рабочий книге только три листа, то добавить еще один лист (выполнить команду Вставка/Лист и после того как новый лист появится, захватить мышью ярлычок этого листа и перенести его на последнее место). Переименовать его.

1.2.Выделите  на первом листе диапазон всей  таблице без заголовка.

1.3.Выполнить  команду Правка/Копировать.

1.4.Перейдите  на новый рабочий лист, сделайте  активной, например ячейку А2 и выполните команду Правка/Специальная вставка, в появившимся окне нажать кнопку Вставить связь.

1.5.Не  снимая выделения, выполнить на  том же листе команду Правка/Специальная вставка, в появившимся окне (установив переключатель Вставить в положение форматы) нажать кнопку ОК (т.е. скопировать форматы).

1.6.Выделить  на втором листе диапазон всей  таблицы, кроме шапки, выполнив  команду Правка/Копировать, перейдите на четвертый рабочий лист, сделайте активной ячейку в колонке А, следующую после предыдущих скопированных данных и повторите указанные выше действия по вставке связи и копированию форматов.

1.7.Данные  третьего месяца таким же образом  скопируйте на четвертый рабочий  лист.

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

1.8.Вставить  колонку Е, озаглавить колонку Месяц отгрузки.

Для заполнения колонки Месяц отгрузки вызвать функцию Месяц. Для этого:

• Выполнить команду Вставка/Функция главного меню или пиктограмма Вставка функции панели Стандартная;
• В открывшимся окне выбрать: Категория - Дата и время, Функция - Месяц;
• Введите в диапазон Дата_как_число ячейку D3;
• Скопируйте функцию до конца таблицы.

1.9.Отобразить  формулы (Сервис/Параметры/Формулы) и зависимость ячеек (Сервис/Зависимости/Влияющие ячейки).

2. Подвести промежуточные итоги, вложенные промежуточные итоги.

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

2.1.Подвести  промежуточные итоги по полю, например Наименование товара.

• Отсортировать таблицу по заданному полю:

Последовательность  действий:

• Выделить всю таблицу вместе с шапкой (лучше с помощью мыши);
• Выполнить команду меню Данные/Сортировка;
• В открывшимся окне, в строке Сортировка по, выбрать заданное поле Наименование товара;
• Нажмите ОК.