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

Используя Фильтр оставьте только иногородних студентов.

3. Создайте список с формулами и диаграммой следующего вида.

4. Ответить на нижеследующие вопросы в письменном виде с демонстрацией конкретных примеров

4.1. Что такое пользовательский порядок сортировки?

4.2. Как применить Автофильтр к списку?

4.3. Как отменить результаты фильтрации?

4.4. Сколько условий можно наложить на один столбец с помощью команды Автофильтр?

4.5. Каков порядок применения Расширенного фильтра?

4.6. Как отменить результат применения Расширенного фильтра?

4.7. Как задать несколько условий для одного столбца списка с помощью расширенного фильтра?

4.8. Как задать одно условие для нескольких столбцов с помощью расширенного фильтра?

4.9. Как задать разные условия для разных столбцов с помощью расширенного фильтра?

5.Работа со списками

Для демонстрации возможностей работы со списками в электронной таблице MS Excel необходимо подготовить примеры и разместить их на листе рабочей книги. Например, “Автокаталог”.

На рисунках 3-9 показаны фрагменты таблиц списка (база данных): на тему «Автокаталог», размещенных на листе "Задача 3" рабочей книги Excel.

Рис.3 – Таблица с исходным списком

Рис.4 – Таблица после сортировки

Рис.5 – Использование фильтра

Рис.6 – Использование расширенного фильтра

Рис. 7 – Подведение итогов и создание структуры данных

Рис. 8 – Сводная таблица

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

Рис.9 – Диаграмма

Фрагмент описания технологии работы со списками в MS Excel приведен ниже. Предметная область списка (база данных): “Автокаталог”.

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

В строке 4 листа рабочей книги Excel (рис. 8) введены заголовки полей списка (базы данных): «Номер»; «Марка»; «Цвет»; «Пробег»; «Год выпуска»; «Объем двигателя»; «Стоимость, тыс. руб.». Заполним таблицу данными как показано на рисунке 10. Всего заполнено 20 строк списка.

Рис. 10 – Список (база данных) «Автокаталог»

Сортировка.

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

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

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

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

Отсортируем список “Автокаталог” по двум уровням - сначала по столбцу «Год выпуска», затем по столбцу «Стоимость». Сортировку выполняем по возрастанию. Для сортировки используем меню: «Данные\Сортировка». Окно выбора параметров сортировки показано на рис. 11.

Рис. 11 – Сортировка данных

Результат выполненной сортировки списка "Автокаталог" показан на рисунке 12.

Рис. 12 – Результат сортировки

Аналогично должны быть описаны технологии выполнения: фильтрации (автофильтр и расширенный фильтр) (рисунки 5 и 6); подведения промежуточных и общих итогов (рисунок 8); создания структуры данных (рисунок 9); создания сводной таблицы (рисунок 8); создания диаграммы (рисунок 9);.

3.Задание 3.3

Создайте в Microsoft Excel таблицу, как на рисунке 13. .Количество строк в документе рассчитывается по формуле:

N_{кол. строк}= N_вар+ 15,

где N_вар. – номер вашего варианта

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

Рис. 13. Сведения о сотрудниках

Порядок выполнения задания

ВНИМАНИЕ! Вы должны придумать свой оригинальный пример и подготовить текст, описывающий технологию работы с таблицами в соответствии с Вашим примером.

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

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

В строке «2» наберите шапку таблицы как на рисунке 13.

Установите в столбце Ф.И.О. формат ячейки текстовый (выделите столбец – нажмите правой кнопкой мыши на обозначение столбца (например D), выберите пункт меню ФОРМАТ ЯЧЕЕК…, вкладку ЧИСЛО, выберите «числовые форматы» - текстовый), установите формат ячейки – текстовый – для столбцов «должность», «адрес», «пол». Для столбца «телефон» таким же образом установите тип «номер телефона» из формата дополнительный. Для столбцов содержащих даты – установите формат «дата», для столбцов №п/п, Разряд – числовой формат, 0 знаков после запятой.

Справа от столбца «Стаж работы» в ячейке К2 введите название столбца «стаж работы – округленный».

Выделите ячейку А1 и введите текст «текущая дата», в ячейку рядом введите текущую дату, задайте формат ячейке «дата».

Рассчитайте стаж работы. Для этого в столбце «стаж работы» введите формулу «(текущая дата - дата найма)/365». Установите ячейку «текущая дата» в формуле - абсолютной (используйте знак $). «Протяните» формулу. В столбце «стаж работы округленный» необходимо округлить полученный результат так, чтобы учитывались только полные годы работы. Для этого выделите ячейку в столбце «стаж работы округленный», вызовите список функций, в Математических найдите формулу ОКРУГЛВНИЗ. Откройте окно формулы, в строке число введите ту ячейку, число в которой надо округлить (J3), в строке число_разрядов введите 0, ОК. «Протяните» формулу.

Поиск необходимых сведений в базе данных

Выделите всю таблицу. Задайте ей имя «Штат_сотрудников». Для этого нажмите кнопку (вкладка ФОРМУЛЫ – группа ОПРЕДЕЛЕННЫЕ ИМЕНА). Затем выполните команду ФОРМА. В Excel 2007 данная команда доступна в разделе ПАРАМЕТРЫ EXCEL. Для добавления команды на панель быстрого доступа нажмите кнопку «OFFICE» и щелкните ПАРАМЕТРЫ EXCEL. Далее в окне «ПАРАМЕТРЫ EXCEL» выберите пункт НАСТРОЙКА. В списке ВЫБРАТЬ КОМАНДЫ ИЗ выберите ВСЕ КОМАНДЫ и найдите нужную команду (Рисунок 14).Нажмите ДОБАВИТЬ и ОК.

Нажмите кнопку на панели быстрого доступа. Появится окно, отражающее аргументы созданной таблицы. В появившемся окне просмотрите данные о продавцах женского пола работающих на предприятии больше 4-х лет. Для этого нажмите кнопку КРИТЕРИИ и введите в соответствующие ячейки заданные условия (в «стаж работы» - >4). Нажмите ДАЛЕЕ. Просмотрите список полученных результатов отбора (Рисунок 15). Закройте окно.

Рис. 14. Добавление кнопки ФОРМА на панель быстрого доступа

Для того, чтобы расположить информацию в таблице в определенном порядке используйте команду СОРТИРОВКА. Для этого выделите ячейку «Ф.И.О.», выберите команду СОРТИРОВКА И ФИЛЬТР – НАСТРАИВАЕМАЯ СОРТИРОВКА (вкладка ГЛАВНАЯ – группа РЕДАКТИРОВАНИЕ), в появившемся окне установите «Сортировать по» - разряду - по возрастанию, нажмите кнопку ДОБАВИТЬ УРОВЕНЬ и установите «Затем по» Ф.И.О. – от А до Я. Просмотрите отсортированные данные.

Рис. 15. Просмотр данных с помощью приложения Microsoft Excel – ФОРМА

При необходимости выделить из таблицы данные, отвечающие определенному условию, воспользуйтесь командой СОРТИРОВКА И ФИЛЬТР. Для этого активизируйте ячейку «Ф.И.О.». Выберите СОРТИРОВКА И ФИЛЬТР - ФИЛЬТР. В ячейках с названиями столбцов появились стрелочки. Нажмите на такую стрелку в столбце Адрес. В появившемся списке оставьте галочку в строке Пятигорск (Рисунок 16). На экране появится список работников проживающих в Пятигорске. Повторно нажмите стрелку в столбце Адрес, установите галочку в строке (ВЫДЕЛИТЬ ВСЕ). Самостоятельно сформируйте список сотрудников принятых на работу после 01.01.2002 года. Скопируйте полученный список сотрудников в нижнюю часть страницы. Первоначальную таблицу верните к исходному виду.

Если необходимо найти информацию, отвечающую двум и более условиям, используйте команду Расширенный фильтр. Для этого скопируйте шапку таблицы и вставьте ее в нижнюю свободную часть листа. В столбце Адрес запишите условие Пятигорск, в столбце Разряд - 3, в столбце Пол - жен. Затем нажмите кнопку (вкладка ДАННЫЕ – группа СОРТИРОВКА И ФИЛЬТР), в появившемся окне задайте аргументы: Исходный диапазон – диапазон исходной таблицы, Диапазон условий – таблица с условиями, в ОБРАБОТКЕ выберите Скопировать результат в другое место (Рисунок 17), в строке Поместить результат в другое место укажите пустой диапазон ниже таблиц. Нажмите ОК.

Рис. 16. Использование приложения АВТОФИЛЬТР для обработки данных

Рис. 17. Использование приложения РАСШИРЕННЫЙ ФИЛЬТР для обработки данных

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

4.Задание 3.4. Создайте таблицу, содержащую следующие сведения о сотрудниках организации аналогичные тем, которые приведены на рис. 18 (количество строк документа должно быть не менее 25).

4.1.Отсортируйте записи по:

- вариант №1 – по алфавиту фамилий и уменьшению разряда;

- вариант №2 – по алфавиту фамилий и увеличению разряда;

- вариант №3 – по дате рождения и уменьшению разряда;

- вариант №4 – по дате рождения и уменьшению разряда;

- вариант №5 – по № телефона и по алфавиту фамилии;

- вариант №6 – по адресу проживания и уменьшению разряда;

- вариант №7 – по адресу проживания и увеличению разряда;

- вариант №8 – по году рождения и месту проживания;

- вариант №9 – по году рождения и стажу работы в организации;

- вариант №10 – по стажу работы в организации и месту проживания;

4.2. Выдайте список сотрудников организации:

- вариант №1 – проживающих в Пятигорске или Ессентуках;

- вариант №2 – проживающих в Ессентуках или Кисловодске;

- вариант №3 – проживающих в Иноземцево или Железноводске;

- вариант №4 – проживающих в Ессентуках или ст. Ессентукской;

- вариант №5 – проживающих в Ессентуках или Железноводске;

- вариант №6 – проживающих в Пятигорске или Железноводске;

- вариант №7 – проживающих в Иноземцево или Железноводске;;

- вариант №8 – проживающих в Лермонтове или Железноводске;;

- вариант №9 – проживающих в Лермонтове или Кисловодске;

- вариант №10 – проживающих в Ессентуках или Минводах;

4.3. Выдайте список сотрудников организации:

- варианты №1, 5 и 9 – чей мобильник начинается на 8928;

- варианты №2, 6 и 10 – чей мобильник начинается на 8961;

- варианты №3, 7 – чей мобильник начинается на 8938;

- варианты №4, 8 – чей мобильник начинается на 8918;

4.4. Проживающих:

- варианты №1, 7 и 11 – в Пятигорске, старше 30 лет на момент осуществления поиска информации, принятых после 01.09.2010;

- варианты №3, 8 и 12 – в Железноводске, не старше 30 лет на момент осуществления поиска информации, принятых до 25.09.2008.

- варианты №4, 9 и 6 – в Ессентуках или Пятигорске, не старше 40 лет на момент осуществления поиска информации, принятых после 30.07.2010.

- варианты №5, 10 и 2 – в Пятигорске или Минводах, старше 25, но младше 50 лет на момент осуществления поиска информации, принятых после 01.06.2011.

Рис. 18. Данные о сотрудниках

5.Задание 3.5.Предприятие «Альфа» осуществляет оптовую реализацию бытовой техники со складов. Имея сведения о количестве проданной продукции в феврале, определите суммы выручки предприятия за месяц.

Создайте таблицу, отражающую реализацию:

- варианты №3, 9 - кофеварок и миксеров;

- варианты №4, 10 - чайников и СВЧ печей за месяц;

- варианты №1, 5 и 7 - миксеров и аэрогрилей за месяц;

- варианты №2, 6 и 8 - кофеварок и чайников за месяц;

Создайте таблицу, показывающую все поставки (количество строк документа должно быть не менее 35): предприятия ООО «Авангард» с 15.02.14 на сумму, превышающую 1000000 руб. на:

- склады №1, №2 или №5 (варианты №2, 10);

- склады №2, №4 или №7 (варианты №3, 1);

- склады №1, №3 или №6 (варианты №4, 6 и 9);

- склады №4, №5 или №6 (варианты №5, 7 и 8).

Рис. 19. Продажи предприятия «Альфа» за февраль