Приведем примеры использования некоторых функций:
• Суммирование чисел из диапазона C3:F5:
Рис. 10.4. Копирование формул
В строке формул после копирования показана формула, в самой ячейке – результат выполнения формулы.
Бывают случаи, когда не нужно изменять ссылки (адреса ячеек), используемые в формуле. Тогда применяется специальное имя, которое называется
Рис. 10.5. Расчет суммы в долларах
Защита ячеек, листов и книг
Иногда необходимо защитить данные от несанкционированного доступа или скрыть формулы, по которым производится расчет. Рассмотрим, как устанавливается защита.
Защита листа от изменения
Защита листа от внесения изменений выполняется с помощью команды Сервис > Защита > Защитить лист. В открывшемся окне (рис. 10.6) установите необходимые варианты защиты, выбрав одно из положений переключателя: Содержимое, Объекты, Сценарии. Затем, если необходимо, введите пароль и подтвердите его.
Рис. 10.6. Установка защиты
Рис. 10.7. Добавление записи в список при помощи формы
4. Введите в каждое поле диалогового окна данные для следующих строк (см. рис. 10.7). Переходите от поля к полю с помощью щелчка мыши. Закончив ввод строки, щелкните на кнопке Добавить. 5. Внесите несколько записей и сохраните документ под названием База данных.
Поиск данных в списках
Если в списке много записей, то отыскать в нем какую-то одну довольно трудно. Для поиска необходимой записи используйте форму для быстрого поиска информации с помощью определенных критериев:
1. Для организации поиска выберите команду Данные > Форма.
2. Щелкните на кнопке Критерии.
3. Введите требуемые критерии поиска в подходящие поля. Можно искать величины большие, меньшие некоторого значения или совпадающие с ним. Для обозначения соответствующих операций сравнения используйте знаки < (меньше), > (больше), >= (больше или равно), <= (меньше или равно), ? (не равно).
Задание 1
В файле База данных организуйте поиск по полю Цена. Для организации поиска поместите рамку активной ячейки в начало списка (поиск организуется сверху вниз) и выберите команду Данные > Форма. Щелкните на кнопке Критерии, выберите цену больше 1200 (рис. 10.8) и нажмите клавишу Enter.
Рис. 10.8. Организация поиска по полю Цена
Просмотреть отобранные записи можно с помощью кнопок Назад и Далее.
Выбор элементов из списка
Формы являются удобным средством простого поиска. Для более удобной фильтрации данных из списка можно использовать специальные автофильтры:
1. Для использования данной команды щелкните в любом месте списка.
2. Выберите команду Данные > Фильтр > Автофильтр.
В заглавной строке каждого столбца появляются маленькие кнопки-стрелки. Щелчок на стрелке приводит к появлению списка, содержащего несколько команд. С помощью этих команд можно выбрать следующие критерии:
• Первые 10. Этот вид фильтра позволяет отобразить записи, имеющие наибольшее (или наименьшее) значение в выбранном поле. Данный фильтр работает только с числовыми полями. При выборе этого критерия открывается диалоговое окно Наложение условия по списку. В этом окне можно выбрать 100 записей в порядке увеличения или 20 в порядке уменьшения;
• Условие. Если выбрать из списка пункт Условие, то на экране появится диалоговое окно Пользовательский автофильтр со списком операций сравнения. Щелкните на кнопке раскрывающегося списка и выберите в списке значение Больше или равно (рис. 10.9). В правом поле укажите значение для сравнения;
• Пустые. Этот способ фильтрации доступен, если в столбце имеются пустые ячейки;
• Непустые. Этот способ фильтрации доступен, если в столбце имеются пустые ячейки. При выборе этого пункта на листе отображаются те ячейки, которые содержат непустое значение;
• Все. Восстанавливает первоначальное состояние списка на экране.
Рис. 10.9. Настройка пользовательского автофильтра
Для поиска в базе данных можно использовать расширенный фильтр (команда Данные > Фильтр > Расширенный фильтр). Задание 2
Покажем на примере, как использовать режим расширенного фильтра:
1. В ячейку с адресом D1 введите фразу Количество проданных товаров, а в ячейку Е1 – значение Дата. Заполните полученную базу данных сведениями о проданных товарах и дате продажи.
2. Выделите диапазон ячеек А1:Е1.
3. Скопируйте выделенный диапазон в ячейки А9:Е9, чтобы оформить шапку таблицы.
4. Определите критерии поиска, например: количество проданных товаров – больше 10, даты продаж – после 10.12.01.
5. Выделите диапазон ячеек А9:Е10.
6. Выполните команду Вставка > Имя > Присвоить. Откроется окно Присвоить имя.
7. Введите критерий и щелкните на кнопке ОК.
8. Выберите команду Данные > Фильтр > Расширенный фильтр и убедитесь, что в поле Исходный диапазон указан диапазон базы данных, а в поле Диапазон условий – диапазон условий с установленными вами критериями.
9. В поле Обработка установите флажок Скопировать в другое место.
10. Щелкните на кнопке ОК.
11. Результат поиска приведен на рис. 10.10.
Рис. 10.10. Поиск с помощью расширенного фильтра
На рисунке критерии поиска находятся в ячейках E9:F10, а результат поиска – в ячейках А12:Е13.
Сводные таблицы
Термин
Рис. 10.11. Первый шаг мастера построения сводных таблиц
2. После щелчка на кнопке Далее на втором шаге мастер предложит уточнить диапазон ячеек для включения в сводную таблицу. Снова щелкните на кнопке Далее и в открывшемся окне щелкните на кнопке Макет.
3. Удерживая левую кнопку мыши, перетащите названия полей в правой части окна в соответствующие поля на макете будущей таблицы. Поле Наименование мы переместили в поле Строка, а в поле Данные поместили поле Кол-ство (рис. 10.12).
Рис. 10.12. Создание макета будущей таблицы
4. Щелкните на кнопке ОК и затем на кнопке Готово. Будет сформирована таблица, из которой понятно, сколько всего изделий продано (рис. 10.13).
Рис. 10.13. Окно с готовой сводной таблицей
Если в поле Наименование товара выбрать из списка конкретный товар, то можно увидеть количество проданного товара данного наименования.
Панель инструментов Сводная таблица поможет вернуться к редактированию сводной таблицы, ввести дополнительные поля, создать отчет. Преобразованная сводная таблица показана на рис. 10.14.
Рис. 10.14. Сводная таблица, отражающая количество проданных товаров одного наименования
Следует иметь в виду, что способ обработки данных в сводной таблице по всем представленным в ней полям в любой момент можно изменить. Если дважды щелкнуть на любой кнопке, откроется диалоговое окно Вычисление поля сводной таблицы, позволяющее выбрать нужную операцию.
4. Выделите ячейки А1:Е1 и щелкните на кнопке Объединить и поместить в центре на панели инструментов форматирования. Заголовок должен оказаться в центре журнала.
5. Заполните журнал данными. Если хозяйственная операция занимает несколько строк, выберите команду Формат > Ячейки, вкладка Выравнивание и установите флажок в поле Переносить по словам.
6. Цифры можно выровнять по центру ячейки. Щелкните для этого на кнопке По центру на панели инструментов форматирования, выделив предварительно ячейки, начиная с ячейки ЕЗ.
7. Отформатируйте эти ячейки, выбрав денежный формат. Для этого выберите команду Формат > Ячейки, в открывшемся диалоговом окне перейдите на вкладку Число. Установите формат Денежный в поле Числовые форматы и укажите значение 2 в поле Число десятичных знаков (рис. 11.1).