- •Часть 2
- •Скрытие строк, столбцов, листов и книг
- •Режим скрытия формул
- •Защита листов, книг, данных. Защита книг и листов
- •Ввод паролей
- •Удаление защиты
- •Защита данных
- •Команда Подбор параметра
- •Лабораторная работа № 10 Тема: Адресация
- •Абсолютные, относительные и смешанные ссылки
- •Изменение типа ссылок
- •Ссылки на ячейки за пределами текущего рабочего листа
- •Ссылки на ячейки из других рабочих листов
- •Ссылки на ячейки из других рабочих книг
- •Лабораторная работа №11 Тема: Числовые форматы. Особенности форматирования Назначение и удаление форматов
- •Применение автоформата
- •Общий формат
- •Дробные форматы
- •Создание пользовательских числовых форматов
- •Форматирование положительных, отрицательных, нулевых и текстовых значений
- •Включение цвета в форматы
- •Использование условий в пользовательских форматах
- •Условное форматирование
- •Управление правилами условного форматирования
- •Лабораторная работа №12 Тема: Работа со строками и датами
- •Использование функций
- •Синтаксис функций
- •Функция пстр
- •Функции заменить и подставить
- •Функции даты и времени
- •Форматирование дат и времени
- •Ввод дат и времени
- •Арифметические операции с датами и временем
- •Функция дней360
- •Функция час
- •Изменение шкалы оси времени
- •Задание минимального и максимального значений
- •Изменение цены основных и промежуточных делений
- •Изменение единицы измерения
- •Лабораторная работа №13 Тема: Списки
- •Что можно делать со списком
- •Планирование списка
- •Ввод данных в список
- •Ввод данных вручную
- •Ввод данных с помощью формы ввода
- •Фильтрация списков
- •Автоматическая фильтрация
- •Автоматическая фильтрация по значениям в нескольких столбцах
- •Пользовательский автофильтр
- •Наложение условия по списку
- •Построение диаграммы по данным отфильтрованного списка
- •Расширенная фильтрация
- •Установка диапазона критериев
- •Множественный критерий отбора
- •Текстовые и числовые критерии
- •Вычисляемые критерии
- •Другие возможности расширенной фильтрации
- •Копирование отобранных строк
- •Отображение только уникальных строк
- •Сортировка списка
- •Простая сортировка
- •Более сложная сортировка
- •Правила сортировки в Excel
- •Особый порядок сортировки
- •Создание промежуточных итогов
- •Лабораторная работа №14 Темы: Сводные таблицы
- •Создание сводной таблицы
- •Добавление нового поля
- •Обновление сводной таблицы
- •Форматирование сводной таблицы
- •Использование сводных таблиц для консолидации данных
Правила сортировки в Excel
Поскольку в ячейках может содержаться информация разного типа, вам, наверное, интересно знать, как эта информация сортируется. Ниже приведен список элементов в порядке убывания их значимости при выполнении сортировки по возрастанию.
Числа. Числа сортируются от наименьшего отрицательного к наибольшему положительному. Даты и время обрабатываются как числа. Во всех случаях при сортировке обрабатывается реальная величина (а не ее внешний вид после форматирования).
Текст. Сортируется в следующем алфавитном порядке:
0 1 2 3 4 5 6 7 8 9 (пробел) ! " # $ % & ' ( ) * + . - . / : ; < = > ? @ [ \ ] ^ _ ' { | } ~ ABCDEFGHIJKLMNOPQRSTUVWXYZА Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я По умолчанию во время сортировки строчные и прописные буквы не различаются. Однако есть возможность изменить это в диалоговом окне Параметры сортировки.
Логические значения. Ложь следует перед Истина.
Ошибочные значения. Такие ошибочные значения, как #знач! и #н/д, появляются в их первоначальном порядке и не сортируются по типу ошибок.
Пустые ячейки. Всегда появляются последними.
При выполнении сортировки по убыванию приведенные последовательности используются в обратном порядке (за исключением пустых ячеек, которые по-прежнему остаются последними).
Особый порядок сортировки
Сортировка чисел или упорядочение текстовых строк чаще всего выполняется по алфавиту. Однако в некоторых случаях бывает необходимо отсортировать данные особым образом. Например, если данные представляют собой названия месяцев, то желательно, чтобы они располагались не в алфавитном порядке, а в порядке возрастания номеров месяцев. Для выполнения такой сортировки можно использовать диалоговое окно Параметры сортировки, выбрав список, в соответствии с которым будет проводиться сортировка, из раскрывающегося спискаСортировка по первому ключу. По умолчанию вExcelопределены четыре стандартных списка. Кроме того, пользовать может также определить собственные списки. Вот перечень стандартных списковExcel.
Сокращенные названия дней недели: Пн, Вт, Ср, Чт, Пт, Сб, Вс.
Полные названия дней недели: Понедельник, Вторник, Среда, Четверг, Пятница Суббота, Воскресенье.
Сокращенные названия месяцев: Янв, Фев, Map, Апр, Май, Июн, Июл, Авг, Сен, Окт, Ноя, Дек.
Полные названия месяцев: Январь, Февраль, Март, Апрель, Май, Июнь, Июль, Август, Сентябрь, Октябрь, Ноябрь, Декабрь.
Иногда необходимо создать свой список. Например, у вашей компании есть несколько магазинов и вам необходимо, чтобы они были размещены в определенном порядке (не алфавитном). Если вы создадите пользовательский список, операция сортировки будет помещать элементы в том порядке, в котором они указаны в списке. Отсортировать данные по списку пользователя можно с помощью команды Данные/Сортировка. Чтобы указать, по какому именно списку сортировать, щелкните на копкеПараметры.
Создание промежуточных итогов
Чтобы использовать эту возможность, список должен быть предварительно отсортирован, поскольку промежуточные итоги будут создаваться каждый раз при изменении значения определенного поля. На рис.13.10 показан список для подведения промежуточных итогов, отсортированный по значениям поля Товар.
Рис. 13.10 Список для подведения промежуточных итогов
Чтобы формулы промежуточных итогов вставлялись в список автоматически, поместите табличный курсор где-нибудь на списке и выберите команду на вкладке Данныев группеСтруктуракомандаПромежуточные итоги. Появится диалоговое окноПромежуточные итоги.
В этом диалоговом окне представлено несколько опций.
При каждом изменении в. В этом раскрывающемся списке показаны все поля вашего списка. Выбранное поле должно быть отсортировано.
Операция. Вам предоставлены на выбор 11 функций. Обычно используется функция Сумма. Она задана по умолчанию.
Добавить итоги по. В этом окне выводятся названия всех полей вашего списка. Разместите отметку радом с полем или полями, в которых вы хотите подвести промежуточные итоги.
Заменить текущие итоги. Любые существующие формулы итогов заменяются новыми.
Конец страницы между группами. Excel вставляет символ конца страницы после подведения каждого промежуточного итога.
Итоги под данными. Итоги будут расположены под текущими данными, если эта опция выбрана (она установлена по умолчанию). В противном случае вначале будет расположен итог, а затем данные.
Убрать все. После щелчка на этой кнопке из списка удаляются все формулы итогов.
Если щелкнуть на кнопке OK,Excelпроанализирует список и вставит формулы, которые были определены, а также структурирует таблицу. Все формулы используют функциюПромежуточные итоги. На рис.13.11 показан рабочий лист после добавления промежуточных итогов.
Рис. 13.11 Рабочий лист после добавления промежуточных итогов
Практические задания:
Студенты группы сдавали тесты по 7 предметам: физика, история, иностранный язык, теоретическая механика, политология, философия, экология и получили количество баллов от 0 до 100.
Введите данные в список, используя форму.
Отсортируйте фамилии студентов в алфавитном порядке.
Найти средний балл по всем предметам каждого студента.
Показать 5 студентов с лучшими и 5 студентов с худшими средними баллами.
Найти студентов, которые получили по истории более 80 баллов.
Вывести на экран список студентов, фамилия которых начинаются с буквы ”С” и которые имеют по экологии “автомат” (ставится за 57 баллов и выше).
Найдите студентов, которые получили от 50 до 70 баллов либо по философии, либо по истории.
Найти студентов у которых оценка по физике от 50 до 70 баллов.
Отсортировать список сначала по баллам по политологии, затем по баллам по теоретической механике, а потом по баллам по иностранному языку (всю сортировку проводить по возрастанию).
Найти студентов у которых по истории и по философии от 30 до 70 баллов, а по иностранному и экологии от 50 до 85.
Отобразить список студентов, фамилии которых начинаются на “А”, набравших средний балл менее 50, но набравших по физике более 20 баллов, по истории - менее 40, по иностранному – более 10, по тер.меху – менее 60, по политологии – более 45, по философии – более 30 и по экологии – менее 40.