РАБОТА СО СПИСКАМИ И ГРУППАМИ ТАБЛИЦ

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

Задание

Проверка вводимых данных

1.1. Создайте в своей папке книгу MS Excel с именем Фамилия_5_n.

1.2. Переименуйте Лист1 на Сотрудники. Скопируйте на этот лист одноименную таблицу Сотрудники, созданную в лабораторной работе № 1 (файл Фамилия_1_n.xls).

1.3. Отсортируйте данные таблицы по возрастанию табельных номеров.

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

1.5. Предположим, что значения табельных номеров сотрудников должны быть четырехзначными целыми числами от 1000 до 9999. Организуйте проверку вводимых в столбец А значений табельных номеров таким образом, чтобы исключить возможность ввода других значений.

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

1.7. Введите не менее пяти значений табельных номеров сотрудников в столбец А.

1.8. Измените условие проверки и сообщение, созданные в пп.1.5 – 1.6, таким образом, чтобы исключить возможность ввода повторяющихся значений в ячейки столбца А.

1.9. Заполните еще не менее пяти значений табельных номеров сотрудников в столбец А.

1.10. Присвойте столбцу А на листе Сотрудники имя Номер.

1.11. Сохраните изменения в файле Фамилия_5_n.xls.

Выпадающий список в ячейке

2.1. Перейдите на Лист2 (или создайте его) файлаФ амилия_5_n.xls и переименуйте его на Выборка.

2.2. Вставьте в верхний колонтитул слева свою фамилию, справа – факультет (институт).

2.3. На листе Выборка в ячейку А1 введите текст "Табельный номер" и задайте формат по образцу "шапки" на листе Сотрудники.

2.4. Сформируйте для столбца А на листе Выборка выпадающий список со значениями столбца Номер (лист Сотрудники). Такой список позволит быстро и без ошибки выбрать один из табельных номеров работающих на предприятии сотрудников, так как будет автоматически появляться при добавлении или корректировке (удалении / изменении) значений столбца А.

2.5. В столбце А на листе Выборка заполните не менее 5 ячеек разными значениями, воспользовавшись выпадающим списком. Задайте границы для заполненных ячеек.

2.6. Сохраните изменения в файле Фамилия_5_n.xls.

Поиск и выборка данных из других таблиц данных

3.1. Выделите на листе Сотрудники ячейки В1, С1, D1 и F1, скопируйте их содержимое, установите курсор в ячейку В1 листа Выборка и вставьте скопированное содержимое. Задайте границы для заполненных ячеек.

3.2. С помощью функции ВПР организуйте в столбце В выборку фамилий сотрудников в соответствии с выбранными из списка табельными номерами в ячейках столбца А. Окончательный вид формулы в ячейке В2:

= ВПР(A2; Сотрудники!A:C; 2)

где А2 – значение, которое должно быть найдено в первом столбце таблицы;

Сотрудники!A:C – ссылка на ячейки листа Сотрудники с таблицей, в которой производится поиск данных;

2 – номер столбца в таблице, из которого нужно вернуть значение.

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

3.3. С помощью функции ПРОСМОТР организуйте в столбце С выборку имен сотрудников в соответствии с выбранными из списка табельными номерами в ячейках столбца А. Выполните автозаполнение формулы на остальные ячейки столбца С, для которых заполнены табельные номера и фамилии.

3.4. Убедитесь в правильности работы функций столбцов В и С, изменив одно из значений табельного номера.

3.5. Для автоматического выбора отчеств сотрудников в ячейках столбца D в соответствии с выбранными из списка табельными номерами в ячейках столбца А воспользуйтесь функциями ИНДЕКС и ПОИСКПОЗ.

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

3.6. Убедитесь в правильности работы функций столбцов В, С и D, изменив одно из значений табельного номера.

3.7. Сохраните изменения в файле Фамилия_5_n.xls.

3.8. Для выборки должностей сотрудников в ячейках столбца Е в соответствии с выбранными из списка табельными номерами в ячейках столбца А используйте совместно функции СМЕЩ и ПОИСКПОЗ.

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

3.9. Убедитесь в правильности работы функций столбца Е, добавив и/или изменив несколько значений табельных номеров.

3.10. Организуйте в столбце F выборку окладов сотрудников в соответствии с выбранными из списка табельными номерами в ячейках столбца А.

Поскольку значения окладов находятся в другом файле (лист Сотрудники файла Фамилия_3_n.xls), то сначала, не закрывая эту книгу, откройте файл Фамилия_3_n.xls, выделите ячейки А1:N11 листа Сотрудники и для корректной работы функции ВПР выполните сортировку по столбцу Табельный номер по возрастанию его значений. Сохраните изменения в файле, но не закрывайте его.

Вернитесь в файл Фамилия_5_n.xls на лист Выборка и в ячейку F1 введите текст "Оклад", задайте ее форматирование подобно другим ячейкам "шапки".

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

3.11. Перед столбцом Е со сведениями о должности вставьте новый столбец. В ячейку Е1 введите текст "ФИО". Начиная с ячейки Е2, с помощью функций СЦЕПИТЬ и ЛЕВСИМВ (категория Текстовые) создайте формулу для выборки фамилий и инициалов сотрудников.

3.12. Запишите в протокол лабораторной работы созданные Вами формулы в ячейках В2, С2, D2, E2, F2 и G2 этого листа и опишите работу каждой из них.

3.13. Скройте столбцы В, С и D.

3.14. Сохраните изменения в файле Фамилия_5_n.xls.

Просмотр и удаление строк с повторяющимися данными

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

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

4.2. Удалите дублирующиеся в этой таблице строки одним из специальных инструментов, имеющихся в Вашей версии Excel.

4.3. Сохраните изменения в файле Фамилия_5_n.xls.