Упражнение 3. «Итоги сессии».
Задание. Составить таблицу с итогами сессии в соответствии с рис. 10. Выполнить сортировку по двум признакам: первичный – Группа (по возрастанию), вторичный – Фамилия (по алфавиту).
Порядок выполнения.
1) Выделить диапазон B2:E17 и выполнитькоманду Данные – СОРТИРОВКА И ФИЛЬТР – Сортировка.
2) Задать настройки, как показано в окне «Сортировка» (рис. 11). В результате получим отсортированную таблицу (рис.12).
Обратим внимание на следующие особенности сортировки:
• в выделенный диапазон не включен столбец А – порядковые номера не сортируются;
• сортировка по вторичному признаку (фамилии по возрастанию) означает их расположение по алфавиту только в пределах одинаковых значений первичного признака (номеров групп);
• сортировка по двум признакам с выбором в качестве первичного признака фамилий в данном случае не имеет смысла, поскольку среди фамилий нет повторяющихся значений.
Рисунок 10. Итоги сессии
Рис. 11
 |
Рис. 12. Результат сортировки
Гораздо реже, чем сортировка по строкам, применяется сортировка по столбцам. Но она в Excel также возможна. В этом случае признаком сортировки является одна из строк списка, например, заголовок, или итоговая строка. Для выполнения сортировки необходимо в окне «Сортировка» нажать кнопку Параметры…и установить переключатель «Сортировать столбцы диапазона».
Применение фильтров
|
|
|
Фильтр– это средство для отбора записей в таблице по некоторому критерию. В Excel имеются два типа фильтров: автофильтр и расширенный фильтр. Автофильтр показывает записи, совпадающие с критериями фильтрации, и скрывает не совпадающие. Расширенный фильтр способен сформировать новую таблицу из отфильтрованных записей.
Автофильтр
Для применения автофильтра необходимо выделить любую клетку внутри фильтруемой таблицы и обратиться к вкладкеДАННЫЕ –СОРТИРОВКА И ФИЛЬТР – ФИЛЬТР. После обращения в заголовке таблицы должны появиться кнопки для раскрытия списков. Нажатие любой кнопки приводит к раскрытию списка элементов соответствующего столбца таблицы. Выбранный элемент является критерием фильтрации. Строки таблицы, в которых элементы столбца не совпадают с критерием будут скрыты, причем за совпавшими сохраняются их прежние порядковые номера. Выбор второго критерия в другом списке приведет к дополнительной фильтрации записей и т.д.
В качестве примера рассмотрим применение автофильтра для таблицы с итогами сессии. На рисунке 13 показаны результаты фильтрации по условию «Оценка по информатике»=5.
Рис.13. Результаты фильтрации
Обратите внимание, что записи, не отвечающие условию фильтрации, скрыты. Поэтому нумерация строк идет не по порядку и выделена синим цветом.
|
|
|
Для задания более сложного условия фильтрации необходимо в соответствующем раскрывающемся списке выбрать:Числовые фильтры – Настраиваемый фильтр… и сформулировать его в открывшемся окне «Пользовательский автофильтр». Окно содержит поля для ввода знаков логических отношений и метки логических операцийИиИЛИ. Например, для отбора записей, соответствующих студентам, получившим по информатике 4 или 5, следует выполнить настройки, как показано на рисунке 14.
Рис.14. Окно Пользовательский автофильтр
Отменить результаты фильтрации можно через ДАННЫЕ –СОРТИРОВКА И ФИЛЬТР – ФИЛЬТР.
Достоинство автофильтра в простоте его применения. Недостаток: в отсутствии возможности формулировать сложные условия, связывающие условия фильтрации в разных столбцах операцией ИЛИ.
Расширенный фильтр
Для применения расширенного фильтра требуется предварительная подготовка, состоящая из двух этапов:
· подготовки вспомогательной таблицы (диапазона) критериев;
· планирования места для размещения результатов фильтрации.
Таблица критериев состоит из строки заголовков и строк с критериями. В смежных ячейках первой строки размещаются необходимые заголовки критериев, совпадающие с заголовками основной таблицы. Лучше формировать эти заголовки копированием из основной таблицы. Под заголовками размещаются критерии, причем если несколько критериев расположены в одной строке, они считаются связанными между собой логической операциейИ, если в разных –ИЛИ.
|
|
|
В качестве критериев можно использовать содержимое ячеек таблицы или логические выражения с использованием содержимого.
В качестве примера рассмотрим условие фильтрации («Группа»=154 И«Оценка по информатике»>3) ИЛИ («Группа»=155 И «Оценка по информатике»>3).
Исходная таблица, блок критериев и новая таблица с результатами фильтрации показаны на рисунке 15.
Рис.15
В рассмотренном примере блок критериев расположен в диапазоне G1:H3.
Запуск расширенного фильтра выполняется на вкладкеДАННЫЕ – СОРТИРОВКА И ФИЛЬТР – ДОПОЛНИТЕЛЬНО. В окне «Расширенный фильтр» следует задать настройки, как показано на рисунке 16.
Рис.16. Диалоговое окно «Расширенный Фильтр»
Обратите внимание, что место для размещения результата указано диапазоном из одной строки. Excel автоматически увеличивает этот диапазон, если число записей, удовлетворяющих условию больше заданного в окне «Расширенный фильтр». Этот диапазон должен быть отделен от диапазона критериев, по крайней мере, одной пустой строкой.
|
|
|
Самостоятельные задания к практической работе:
Задание 1.
1. Открыть новый документ MicrosoftExcel.
2. Выполнить упражнения 1-3. Каждое упражнение выполнять на отдельном листе.
3. Добавить ещё 3 листа и переименовать их в «Наименование задания» соответственно. Каждое задание выполнять на отдельном листе.
4. Сохранить книгу под именем«Фамилия_группа» в личной папке.
Задание 2.«Функция»
На листе «Функция» рассчитать значение функции согласно своему варианту (Номер варианта соответствует вашему порядковому номеру в журнале. Для тех, у кого порядковый номер в журнале с 16 по 30 номер вариантас 1 по 15 соответственно).
Можно разбить функцию на несколько вспомогательных функций, для более удобной работы. Работу выполнить по образцу в упражнении 1.
Задание3. «Корни уравнения»
Составить таблицу корней квадратного уравнения (Табл. 1).
Таблица 1
| A | B | С | D | E | F | |||
| 1 | Корни уравнения вида aX2+bx+c=0 | |||||||
| 2 | a | b | c | Дискриминант | x1 | x2 | ||
| 3 | 1 | 3 | 0 | 9 | 0 | -3 | ||
| 4 | 2 | 6 | 3 | 12 | -0,63 | -2,37 | ||
| 5 | 3 | 9 | 6 | 9 | -1 | -2 | ||
| 6 | 4 | 12 | 9 | 0 | -1,5 | -1,5 | ||
| 7 | 5 | 15 | 12 | -15 | решения нет | решения нет | ||
| 8 | 6 | 18 | 15 | -36 | решения нет | решения нет | ||
| 9 | 7 | 21 | 18 | -63 | решения нет | решения нет | ||
| 10 | 8 | 24 | 21 | -96 | решения нет | решения нет | ||
| 11 | 9 | 27 | 24 | -135 | решения нет | решения нет | ||
Пояснения к выполнению.
Образец для вычисления дискриминанта = B3^2-4*A3*C3.
Образец для формулы корня:
=ЕСЛИ(D3<0;"решения нет";(КОРЕНЬ(D3)-B3)/(2*A3))
Функция КОРЕНЬ находится в категории «Математические».
Задание4. «Таблица успеваемости»
Составить таблицу успеваемости учеников (табл. 2).
Создать подсчёт:
o успевающих на 4 и 5.
o количество отличников.
o хорошистов с одной 4.
o успевающих без двоек, с одной 3.
o количество двоечников.
Таблица 2
| A | B | С | D | E | F | G | H | I | К | L | М | |
| 1 | № п/п | Фамилия | Математика | Физика | Русский | Химия | Физ-ра | Отличники | Хорошисты | Хорошисты с 1 четвёркой | Троечники с одной тройкой | Двоечники |
| 2 | 1 | Иванов | 5 | 5 | 5 | 5 | Осв. | да |
|
|
|
|
| 3 | 2 | Петров | 5 | 4 | 5 | 5 | 5 | да | да |
|
| |
| 4 | 3 | Никитин | 5 | 2 | 5 | 5 | 5 |
|
|
| да | |
| 5 | 4 | Николаева | 4 | 4 | 3 | 4 | 4 |
|
| да |
| |
| 6 | 5 | Федосова | 3 | 3 | 4 | 2 | Осв. |
|
|
| да | |
| 7 |
|
|
| Итого |
| 1 | 1 | 1 | 1 | 2 | ||
Дата добавления: 2021-01-20; просмотров: 200; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!