Кейс-задача №1 У Вас есть 10 000 000 руб., которые Вы можете инвестировать абсолютно в любые активы. Например: акции, облигаци. Получение навыков использования встроенных функций и фильтрации данных Работа со списком в Excel
 Скачать 0.53 Mb.
|
|
1 Получение навыков использования встроенных функций и фильтрации данных Работа со списком в Excel Практическое задание № 8 Порядок выполнения работы 1) Список — это группа строк таблицы, содержащая связанные данные. Каждый столбец списка содержит однотипные данные. Первая строка списка является его заголовком и содержит названия столбцов списка. Задан перечень столбцов списка, который будет применяться при создании базы данных: 1. Порядковый номер; 2. Табельный номер; 3. Фамилия; 4. Имя; 5. Отчество; 6. Отдел (если есть); 7. Должность; 8. Дата приема на работу; 9. Дата увольнения; 10. Пол; 11. Улица; 12. Дом; 13. Квартира; 14. Домашний телефон; 15. Дата рождения; 16. Идентификационный код; 17. Количество детей; 18. Льготы по ПН; 19. Совместитель - многодетный; 20. Непрерывный стаж с; 21. Справочный столбец. Все заголовки оформлены в одном формате: порядковый номер; 2 точка; пробел; непосредственно текст заголовка; точка с запятой. 2) Загрузите Microsoft Excel и сохраните файл с именем Excel_4. На рабочем листе 1 введите предложенный в п.1 перечень столбцов списка в заданном формате, начиная с любой ячейки, например, с А5. В этом случае заголовки должны занять 21 ячейку рабочего листа, располагаясь друг под другом – в диапазоне А5:А25. 3) Для формирования имени поля списка нам нужен только непосредственно текст заголовка. Следующим этапом работы будет очистка введенного текста от лишних символов с помощью текстовых функций Excel. Алгоритм очистки текста от лишних символов: Определить количество символов, которые содержатся в тексте, используя функцию ДЛСТР. Посредством функции ЛЕВСИМВ выбрать из исходного текста все символы, кроме последнего. Из оставшегося текста, используя функцию ПРАВСИМВ, выбрать текст, очищенный от лишних правых и левых символов. Проследить за результатом выполнения алгоритма по очистке текста можно, заполняя рабочую таблицу 1 в соответствии с этапами алгоритма (ниже табл.1.1 – отражает вид таблицы 1 с формулами; табл.1.2 – отражает вид таблицы 1 с данными). 4) Создание сложной формулы для обработки текста. Для выполнения всех этапов алгоритма очистки текста можно создать одну формулу и разместить ее в одной ячейке. С этой целью воспользуемся методом вложения одной формулы 3 (функции) в другую, при котором одни формулы (функции) применяются в качестве аргументов в других. Для выполнения работы создания сложной формулы создайте копию рабочего листа 1. Присвойте новому рабочему листу имя Формула. Таблице дайте заголовок: Таблица 2. Последовательность подстановки формул отразите поэтапно в таблице 2. Проверьте на всех этапах правильность их работы. Модификацию следует начинать с ячейки В5. Выполнить следующие шаги: Активизируйте ячейку В5 и в режиме правки в строке формул скопируйте находящуюся в этой ячейке формулу без знака равенства. Поместите табличный курсор в ячейку С5. В строке формул выделите ссылку на адрес ячейки В5 и вместо этой ссылки вставьте содержимое буфера обмена. Далее перейдите в ячейку D5 и произведите в ней аналогичную замену адреса ячейки В5 на формулу. Возвратитесь к ячейке С5 и скопируйте находящуюся в ней формулу из строки формул. Перейдите к ячейке D5 и вставьте в нее вместо ссылки на адрес ячейки С5 скопированную формулу. 5) После удаления из названий столбцов лишних символов сформируйте строку заголовка списка, для этого: скопируйте в буфер обмена полученный после обработки текст из диапазона ячеек D5:D25; откройте лист 3 книги Excel и поместите табличный курсор в ячейку А1, которая будет служить началом строки заголовка списка; выполните Специальную вставку с транспонированием 4 содержимого буфера обмена; в результате все заголовки будут расположены по горизонтали в диапазоне А1:U1. 6) Введите в список данные о пяти сотрудниках, в том числе свои данные (Фамилия, Имя, Отчество). После внесения в список данных о сотрудниках отформатируйте таблицу: ширину одних столбцов нужно увеличить, а других, наоборот, уменьшить; для строки заголовка применить, где это необходимо, автоматический перенос текста с выравниванием по центру, отметив в окне Формат ячеек, на вкладке Выравнивание, опцию Переносить по словам; строка заголовка должна отличаться от остальной области списка, поэтому следует задать вид, размер шрифта и цвет текста, выбрав нужные элементы в раскрывающихся списках Шрифт, Размер шрифта и Цвет текста. 7) Рассмотреть режимы просмотра данных: открытие нескольких окон; разделение таблицы на области; закрепление областей таблицы. В Excel существует возможность открыть для одного и того же рабочего листа два окна для одновременной работы с разными частями документа. Данную операцию можно выполнить, выбрав на вкладке Вид команду Новое окно. Закрыть одно из окон можно, нажав кнопку Закрыть окно (с крестиком) в правом верхнем углу окна. Удобный метод просмотра данных можно обеспечить, разделив таблицу на две или четыре области. 5 Таблица 1.1 A B C D 5 1. Порядковый номер; =ДЛСТР(A5) =ЛЕВСИМВ(A5;B5-1) =ПРАВСИМВ(C5;B5-4) 6 2. Табельный номер; =ДЛСТР(A6) =ЛЕВСИМВ(A6;B6-1) =ПРАВСИМВ(C6;B6-4) 7 3. Фамилия; =ДЛСТР(A7) =ЛЕВСИМВ(A7;B7-1) =ПРАВСИМВ(C7;B7-4) 8 4. Имя; =ДЛСТР(A8) =ЛЕВСИМВ(A8;B8-1) =ПРАВСИМВ(C8;B8-4) 9 5. Отчество; =ДЛСТР(A9) =ЛЕВСИМВ(A9;B9-1) =ПРАВСИМВ(C9;B9-4) Таблица 1.2 A B C D 5 1. Порядковый номер; 20 1. Порядковый номер Порядковый номер 6 2. Табельный номер; 19 2. Табельный номер Табельный номер 7 3. Фамилия; 11 3. Фамилия Фамилия 8 4. Имя; 7 4. Имя Имя 9 5. Отчество; 12 5. Отчество Отчество Таблица 2 A B C D 5 1. Порядковый номер; =ДЛСТР(A5) =ЛЕВСИМВ(A5;ДЛСТР(A5)-1) =ПРАВСИМВ(ЛЕВСИМВ(A5;ДЛСТР(A5)-1);ДЛСТР(A5)-4) 6 2. Табельный номер; =ДЛСТР(A6) =ЛЕВСИМВ(A6;ДЛСТР(A6)-1) =ПРАВСИМВ(ЛЕВСИМВ(A6;ДЛСТР(A6)-1);ДЛСТР(A6)-4) 7 3. Фамилия; =ДЛСТР(A7) =ЛЕВСИМВ(A7;ДЛСТР(A7)-1) =ПРАВСИМВ(ЛЕВСИМВ(A7;ДЛСТР(A7)-1);ДЛСТР(A7)-4) 8 4. Имя; =ДЛСТР(A8) =ЛЕВСИМВ(A8;ДЛСТР(A8)-1) =ПРАВСИМВ(ЛЕВСИМВ(A8;ДЛСТР(A8)-1);ДЛСТР(A8)-4) 9 5. Отчество; =ДЛСТР(A9) =ЛЕВСИМВ(A9;ДЛСТР(A9)-1) =ПРАВСИМВ(ЛЕВСИМВ(A9;ДЛСТР(A9)-1);ДЛСТР(A9)-4) 6 Поместите табличный курсор в ту ячейку рабочего листа, где должна появиться граница раздела, и вызовите команду Разделить на вкладке Вид. Убрать разделение таблицы на области можно, выполнив повторное нажатие кнопки Разделить. Также удобный просмотр данных можно обеспечить, выполнив закрепление областей. С целью закрепления областей выделяются необходимые ячейки и выполняется команда Закрепить области на вкладке Вид. Выполняется просмотр данных. Закрепленные области всегда будут видны на экране при прокрутке листа. Для отмены закрепления областей следует выполнить команду Снять закрепление областей. 8) Выполнить фильтрацию данныхсписка. Выбрать из списка нужные данные можно при помощи фильтрации. Чтобы воспользоваться функцией фильтрации, необходимо установить табличный курсор на одну из ячеек заголовка списка и вызвать команду Фильтр на вкладке Данные – в правом углу каждой ячейки заголовка появится маленький квадратик со стрелкой раскрывающегося списка. Рассмотрите работу с автофильтром на следующем примере: нажмите кнопку фильтрации, расположенную в ячейке с заголовком Пол, и в открывшемся списке выберите букву М; все строки, которые не удовлетворяют заданному критерию, будут скрыты. Выполните фильтрацию для других полей заголовка. Установите фильтрацию для комбинации полей. Чтобы отменить фильтрацию по определенному столбцу, необходимо открыть список автофильтра в этом столбце и 7 выбрать пункт Выделить все. Если функция фильтрации задана для нескольких столбцов, воспользоваться командой Очистить на вкладке Данные. 9) Сохраните файл Excel_4 и завершите работу с Microsoft Excel. |