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 – проживающих в Ессентуках или Минводах;

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

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

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

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

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

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

• варианты №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. Продажи предприятия «Альфа» за февраль