Лабораторное занятие. Поиск, сортировка, фильтрация в табличном процессоре.
Информационные технологии в профессиональной деятельности
17 марта 2020 г.
Лабораторное занятие. Работа в табличном процессоре.
Цель: научится создавать, редактировать и форматировать табличную информацию, уметь решать задачи с вводом формул и использованием стандартных функций Excel
Сведения из теории
Формула – это выражение, по которому выполняются вычисления. Формула не может содержать более 1024 символов. Каждая формула должна начинаться со знака «=», иначе все введенные символы будут рассматриваться как текст или число. Формула может содержать операнды (константы, ссылки или диапазоны ссылок, заголовки, имена, функции) и операторы.
В Excel используется 4 вида операторов:
1. Арифметические операторы (+, –, *, /, %, ^) используют для выполнения основных математических вычислений над числами. Результатом вычисления формул, которые содержат арифметические операторы, всегда является число.
2. Операторы сравнения (=,>,=,) используют для сравнения двух чисел. Результатом вычисления формул, которые содержат операторы сравнения, являются логические значения ИСТИНА или ЛОЖЬ.
3. Текстовый оператор (&) используют для объединения последовательностей символов в одну последовательность.
4. Адресные операторы используют для объединения диапазонов ячеек с целью осуществления вычислений. К ним относятся:
¾ Оператор диапазона, который используется для обозначения диапазона ячеек, к которым применяется формула. Например, А3:В6.
|
|
|
¾ Оператор объединения, который используется для объединения ячеек диапазона. Например, МИН(В1:В10,С1:С15) ищет минимальное значение в диапазонах В1:В10 и С1:С15.
Пробел – оператор пересечения, который используется для ссылки на общие ячейки диапазона. Например, в формуле МАКС(А1:В2 А1:А4) ячейки А1 и А2 – общие для обоих диапазонов. Таким образом, результатом вычисления формулы будет нахождение максимального значения из этих двух ячеек.
При записи формул важно учитывать приоритет выполнения операций:
¾ адресные операторы «:», «,», «»;
¾ знаковый минус «–»;
¾ вычисление процента %;
¾ арифметические операторы ^, *, /, +, –;
¾ текстовый оператор &;
¾ операторы сравнения =, , =, .
По умолчанию вычисления в формуле осуществляются слева направо, а для изменения порядка вычислений используют скобки. После ввода формулы в ячейку в ней отображается результат вычисления. Можно включить режим отображения формул с помощью команды Показать формулы в группе Зависимости формул на вкладке Формулы.
Использование ссылок в формулах
Ссылка – идентификатор ячейки или группы ячеек. При использовании ссылок, формула становится связанной с этими ячейками. Значение формулы становится зависимым от содержимого этих ячеек и будет изменяться при их изменении.
|
|
|
В формулах можно использовать как ссылки на ячейки одного листа, так и ссылки на ячейки других листов книги, другой книги или даже на данные другого приложения.
Excel использует 3 типа ссылок:
Относительные ссылки указывают на ячейку относительно той ячейки, в которой находится формула. При копировании или перемещении формулы относительные ссылки изменяются.
Абсолютными являются ссылки на ячейки, которые фиксируются при копировании или перемещении. В абсолютных ссылках используется знак $ перед именами столбцов и именами строк.
Смешанные ссылки – это ссылки, которые являются комбинацией относительных и абсолютных ссылок. Например, $А3. В этом случае при копировании или перемещении такой ссылки будет изменяться только номер строки, а имя столбца изменяться не будет.А3. В этом случае при копировании или перемещении такой ссылки будет изменяться только номер строки, а имя столбца изменяться не будет.
Основные функции Excel
Функция – стандартная формула, обеспечивающая выполнение определенных действий над значениями, которые выступают в качестве аргументов. Аргументами функций могут быть числа, текст, логические значения, ссылки. В качестве аргументов могут использоваться константы или формулы, которые в свою очередь могут содержать другие функции. Таким образом, аргументы могут быть представлены функциями. Функции также используются для упрощения длинных и сложных формул.
|
|
|
Excel содержит широкий набор функций, которые объединены в категории:
¾ Математические – используются для простых и сложных математических вычислений (вычисление суммы, максимального и минимального значения, округление чисел и т.д.).
¾ Инженерные – используются для выполнения инженерного анализа (работа с комплексными переменными, преобразование чисел из одной системы счисления в другую и т.п.).
¾ Логические – используются для проверки выполнения условия (функции ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ).
¾ Статистические, которые предназначаются для выполнения статистического анализа данных.
¾ Финансовые – для осуществления типичных финансовых расчетов.
¾ Текстовые – для обработки текста (преобразование, сравнение, объединение строк текста и т.д.).
|
|
|
¾ Дата и время.
¾ Ссылки и массивы – для поиска в списках или таблицах.
¾ Проверка свойств и значений – для перехвата ошибок и значений #Н/Д и предотвращения их распространения по рабочему листу.
¾ Аналитические – для работы с аналитическими кубами, позволяющими создавать отчеты по базе данных оперативной аналитической обработки (OLAP).
¾ Работа с базой данных – для анализа данных из списков или баз данных.
¾ Определенные пользователем – для расширения возможностей MS Excel за счет добавления пользовательских команд и новых функций.
Для ввода функций можно использовать несколько возможностей, которые запускают Мастер функций: Команда Вставить функцию в группе Библиотека функций на вкладке Формулы. Список команды Сумма в группе Редактирование на вкладке Главная. Кнопка Вставить функцию в начале Строки формул.
Задание 1.
Запустите табличный редактор MS Excel.
Сохраните в своей папке Работа в Excel на диске D: рабочую книгу под именем Расчетные_задачи.xlsx
На первом листе созданного файла оформите таблицу с названием Финансовая сводка за неделю.
Произведите расчеты, используя формулы:
1.В ячейке Е3 – расчет финансового результата за понедельник вычислить как разность Расхода и Дохода, т.е. формула примет вид =D3-C3
2.Скопируйте содержимое ячейки E3 в диапазон Е4:Е9
3.Вычислите среднее значение для Дохода, Расхода и Финансового результата:
4.В ячейку C10 введите формулу =СРЗНАЧ(С3:С9)
5.Скопируйте формулу из С10 в диапазон D10:Е10
6.В ячейке Е11 определите общий (суммарный) результат по формуле =СУММ(Е3:Е9)
Задание 2.
На втором листе созданного файла оформите таблицу с названием Анализ продаж.
Произведите расчеты, используя формулы:
1.В ячейке Е3 – вычислите сумму для первого наименования (туфли), как произведение Цены на Количество, т.е. = C3*D3
2.Скопируйте содержимое ячейки E3 в диапазон Е4:Е10
3.В ячейке Е11 вычислите общую сумму по формуле =СУММ(Е3:Е10)
4.В ячейке Е12 определить максимальную сумму по формуле =МАКС(Е3:Е10)
5.В ячейке Е13 определить минимальную сумму по формуле =МИН(Е3:Е10)
Задание 3.
На третьем листе созданного файла оформите таблицу с названием Ведомость учета брака.
1.Настроить в соответствующих диапазонах форматы денежный и процентный
2.Сумму брака считать как произведение Процента брака и Суммы зарплаты
Задание 4.
На четвертом листе созданного файла оформите таблицу с названием Анализ продаж.
1.В столбце Всего вычислить суму Безналичных и Наличных платежей
2.Выручка от продаж вычисляется как произведение Цены на Всего
Задание 5
На каждом рабочем листе оформите границы для таблиц
На первом и втором листах сделать заливку некоторых ячеек по своему усмотрению
На третьем листе оформить таблицу, используя Стили ячеек (страница ленты Главная, группа Стили)
Назовите ярлычки рабочих листов по названию задач соответственно.
Контрольные вопросы и задания:
1. Какие форматы данных в программе Excel существуют?
2. Для чего предназначена формула?
3. Из каких элементов состоит формула?
4. Опишите технологию ввода формулы
5. Какие способы копирования формулы вы знаете?
6. Опишите самый рациональный способов копирования
7. Какие виды ссылок существуют?
8. Приведите примеры разных видов ссылок
9. Для чего предназначены функции?
10. Что называется синтаксисом функции?
11. Опишите технологию ввода функции.
23 марта
Лабораторное занятие. Поиск, сортировка, фильтрация в табличном процессоре.
Цель: Изучить и сформировать навыки сортировки, фильтрации и поиска данных в программе MS Excel.
Сведения из теории
Одной из наиболее часто решаемых с помощью электронных таблиц задач является обработка списков, которые в каждом конкретном случае могут называться по-разному: телефонные списки, списки активов, пассивов, список товаров и др.
Формирование списка.
Для обеспечения эффективности работы со списками необходимо
соблюдать следующие правила при их создании:
1. Каждый столбец должен содержать однородную информацию.
2. Одна или две верхние строки в списке должны содержать метки, описывающие назначение соответствующего столбца.
3. Необходимо избегать пустых строк и столбцов внутри списка.
Правило 1 предполагает, что, например, при создании списка персонала можно отвести один столбец для табельных номеров работников, другой - для их фамилий, третий - для их имен, четвертый - для даты приема на работу и т.д. Это же правило запрещает размещать в одном столбце разнородную информацию, например, номер телефона и год окончания школы.
Правило 2 обеспечивает присвоение имен полям. Эти имена постоянно используются при обработке списков.
Правило 3 обеспечивает возможность работы со списком как с единым целым. В идеале на рабочем листе не должно быть ничего, кроме списка. Если это невозможно, то список нужно отделить от других данных по крайней мере одной пустой строкой и пустым столбцом.
На рис.4 приведен список из 10 столбцов.
Рисунок 4.
Сортировка списков.
MS Excel предоставляет многочисленные способы сортировки (упорядочения) интервалов ячеек рабочих листов независимо от того, считается ли данный интервал списком. Возможна сортировка по строкам или по столбцам, по возрастанию или убыванию, с учетом или без учета прописных букв.
Пример .
Продемонстрируем сортировку на списке рис.4. Нужно отсортировать список по столбцу Бригада. Для этого:
1) выделите одну ячейку (не интервал) в этом списке;
2) выполните команду Сортировка (Вкладка Данные, группа Сортировка и фильтр);
3) откроется диалоговое окно Сортировка (рис.4);
4) выберите поле, по которому нужно сортировать (в этом примере - Бригада).
Рекомендуется сразу же проверять результат сортировки. Если результат не устраивает, воспользуйтесь командой Отменить и восстановите предыдущий порядок строк в списке. Для восстановления исходного порядка строк в списке после различных сортировок, необходимо до сортировки создать столбец с номерами строк. В нашем примере это столбец №пп. Это позволяет восстановить первоначальный порядок строк, отсортировав список по этому столбцу.
Анализ списка с помощью фильтров.
Отфильтровать список - это значит скрыть все строки кроме тех, которые удовлетворяют заданным критериям. Excel предоставляет две команды фильтрации: Автофильтр- для простых критериев, и Расширенный фильтр - для более сложных критериев.
Команда автофильтр.
Для применения обычного или автофильтра нужно выполнить следующую последовательность действий:
1) выделите какую-либо ячейку в списке;
2) нажать кнопку Фильтр в группе Сортировка и фильтр Справа от каждого заголовка столбца появиться кнопка "Раскрывающийся список" (со стрелкой вниз). Если щелкнуть по этой кнопке, то раскроется список уникальных значений данного столбца, которые можно использовать для задания критерия фильтра. На рис.5 показан результат фильтрации по столбцу Бригада, выбраны только те строки, где значение Бригада равно 21. Номера строк, не удовлетворяющие критериям команд Фильтр (Автофильтр) и Расширенный фильтр, MS Excel просто скрывает. Номера отфильтрованных строк выводятся контрастным цветом, а в строке состояния появляется сообщение Найдено записей.
Рисунок 5.
Критерии команды Фильтрможно задавать по одному столбцу, затем
полученный список можно отфильтровать по другому столбцу и т.д.
Удаление автофильтров.
Для удаления фильтра по столбцу нужно в раскрывающемся списке критериев этого столбца выбрать параметрВыделить все. Для удаления всех действующих фильтров выберите командуОчистить (Вкладка Данные, группа Сортировка и фильтр). Стрелки раскрывающихся списков критериев удаляются при повторном нажатии кнопки Фильтр.
Задание 1.
На листе представлены данные о 17 озерах.
Отсортируйте данные:
а) по названию озера (по возрастанию);
б) по названию озера (по убыванию);
в) по площади озера (по убыванию);
г) по наибольшей глубине (по возрастанию).
Каждое из заданий выполните на отдельном листе одной рабочей книги.
Задание 2.
На листе представлены данные о крупнейших островах Европы.
Получите таблицу (также из четырех столбцов), в которой данные будут отсортированы:
а) по названию острова (в алфавитном порядке);
б) по площади острова (по убыванию).
Допускается изменение структуры исходной таблицы.
Каждое из заданий выполните на отдельном листе одной рабочей книги.
Задание 3.
Создайте таблицу
| A | B | C | D | E | F | G | |
| 1 | РАСЧЕТ ДОХОДА СОТРУДНИКОВ ОРГАНИЗАЦИИ | ||||||
| 6 | |||||||
| 7 | Таблица расчетов заработной платы | ||||||
| 8 | |||||||
| 9 | № | Ф.И.О. | Оклад | Подоходный налог | Отчисления в благотворительный фонд | Всего удержано | К выдаче |
| 10 | 1 | Петров И.С. | 1250 | 110,5 | 37,5 | 148 | 1102 |
| 11 | 2 | Антонова Н.г. | 1500 | 143 | 45 | 188 | 1312 |
| 12 | 3 | Виноградова Н.Н. | 1750 | 175,5 | 52,5 | 228 | 1522 |
| 13 | 4 | Гусева И.Д. | 1850 | 188,5 | 55,5 | 244 | 1606 |
| 14 | 5 | Денисова Н.В. | 2000 | 208 | 60 | 268 | 1732 |
| 15 | 6 | Зайцев К.К. | 2250 | 240,5 | 67,5 | 308 | 1942 |
| 16 | 7 | Иванова К.Е. | 2700 | 299 | 81 | 380 | 2320 |
| 17 | 8 | Кравченко Г.И. | 3450 | 396,5 | 103,5 | 500 | 2950 |
| 18 | Итого | 16750 | 1761,5 | 502,5 | 2264 | 14486 | |
Произведите сортировку по фамилиям сотрудников в алфавитном порядке по возрастанию.
Произведите фильтрацию значений дохода, превышающих1600 р.
Определите по таблице фильтрацией, у кого зарплата меньше 2000р.
Контрольные вопросы и задания:
1. Чем сортировка отличается от фильтрации
2. Опишите способы сортировки
3. Опишите способы поиска
4. Опишите способы фильтрации
Дата добавления: 2020-04-08; просмотров: 61; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!