Базы данных и списки в Excel
Рекомендации в работе со списками: Каждый столбец должен содержать информацию одного типа. Одна или две верхних строки списка должны содержать заголовки. Не включайте в список пустые строки и столбцы. Для списка отводите отдельный лист. Не размещайте данные слева или справа от списка. Используйте Закрепление областей для закрепление верхней строки либо правого столбца (Вид|Закрепить области).
Закрепленная верхняя строка
Проверка вводимых значений: Данные|Проверка данных Варианты проверки: Задание типа данных и допустимых значений; Задание списка допустимых значений; Использование формулы для проверки данных; Задание сообщения для ввода; Задание сообщения об ошибке.
Использование фильтров для анализа списков. Автофильтр (Данные|Фильтр)
Использование фильтров для анализа списков. Расширенный фильтр Дополнительные возможности Расширенного фильтра Допускается задавать условия, соединенные логическим оператором ИЛИ для нескольких столбцов. Допускается задавать 3 и более условий для конкретного столбца. Допускается задавать вычисляемые условия. Позволяет сразу копировать отобранные строки.
Примеры диапазонов условий
Использование фильтров для анализа списков. Расширенный фильтр Правила формирования множественного критерия: Если критерии указываются в каждом столбце на одной строке, то они считаются связанными условием И. 2. Если условия записаны в нескольких строках, то они считаются связанными условием ИЛИ.
Данные|Дополнительно
Использование вычисляемых условий Заголовок над вычисляемым условием должен отличаться от любого из столбцов списка. Ссылки на ячейки, находящиеся вне списка, должны быть абсолютными ($). Ссылки на ячейки в списке должны быть относительными. ИСТИНА или ЛОЖЬ.
Использование промежуточных итогов для анализа списков ДАННЫЕ|Промежуточный итог Для получения итогов по группам следует заранее упорядочить строки списка. Подведение итогов выполняется при изменении значений в столбце, который образует группы. Команда Итоги может выполняться для одного и того же списка записей многократно.
Перед применение Промежуточных итогов необходимо отсортировать список
Сводные таблицы (Вставка|Сводная таблица)
Работа с формулами в Excel Копирование формул в пределах столбца осуществляется перетягиванием черного крестика в правом нижнем углу ячейки с формулой. Абсолютная ссылка на ячейку даёт возможность зафиксировать (не менять при копировании) строку и столбец ячейки. Абсолютная ссылка может быть создана с помощью знака доллара - $: A$3 – зафиксирована строка $A 3 – зафиксирован столбец $A$3 – зафиксирована вся ячейка Для создания абсолютной ссылки удобно использовать клавишу абсолютной ссылки F 4, которая осуществляет преобразование относительной ссылки в абсолютную и наоборот.
Функции Excel для работы со списками Функции для анализа списков: СЧЁТЕСЛИ, СУМЕСЛИ. =СУММЕСЛИ(I 2: I 18; "<45"; H 2: H 18) n диапазон суммирования критерий n Функции баз данных: ДСРЗНАЧ, БСЧЁТ, БСЧЕТА, БИЗВЛЕЧЬ, ДМАКС, ДМИН, БДПРОИЗВЕД, ДСТАНДОТКЛ, ДСТАНД ОТКЛП, БДСУММ, БДДИСПП, СЧИТАТЬПУСТОТЫ. =ДСРЗНАЧ(A 1: I 18; "Оклад"; A 22: A 23), база данных; поле; условия
