Приведем примеры использования некоторых функций:

• Суммирование чисел из диапазона C3:F5:

...

CYMM(C3:F5)

• Вычисление среднего значения чисел из указанных диапазонов:

...

СЗНАЧ(А1:АЗ;ВЗ:В5)

• Округление значения ячейки М10 до второго знака после запятой:

...

ЭКРУГЛ(М10;2)

Относительная и абсолютная адресация

Полезной особенностью электронной таблицы является возможность автоматического изменения адресов ячеек при копировании и перемещении формул.

Вернемся к примеру с накладной (см. рис. 10.1). Скопируем формулу из ячейки D4 в ячейки D5 и D6. Самым быстрым способом копирования является копирование с помощью маркера в нижнем правом углу выделенной ячейки: подведите указатель мыши к маркеру и, удерживая левую кнопку, перетащите формулу в соседние ячейки (рис. 10.4). Обратите внимание: при копировании в формулах автоматически изменяются адреса ячеек.

Рис. 10.4. Копирование формул

В строке формул после копирования показана формула, в самой ячейке – результат выполнения формулы.

Бывают случаи, когда не нужно изменять ссылки (адреса ячеек), используемые в формуле. Тогда применяется специальное имя, которое называется абсолютным.В этом случае в адрес ячейки вносится знак абсолютной адресации $:

• $С$7 – абсолютная адресация: при копировании адрес ячейки не меняется;

• В$4 – частичная абсолютная адресация: при копировании не меняется номер строки;

• $В4 – частичная абсолютная адресация: при копировании не меняется номер столбца.

Вернемся к примеру с накладной. Допустим, необходимо подсчитать сумму в долларах и при этом учесть возможность изменения курса.Добавьте еще один столбец и введите в нем формулу =D4/$B$1.

...

Примечание

Абсолютный адрес ячейки можно получить, указав в формуле ячейку и нажав затем функциональную клавишу F4. При последующих нажатиях F4 будут выведены частичные абсолютные адреса.

При копировании формулы в графе Сумма в долларах адрес ячейки В1 не изменится (рис. 10.5).

Рис. 10.5. Расчет суммы в долларах

Защита ячеек, листов и книг

Иногда необходимо защитить данные от несанкционированного доступа или скрыть формулы, по которым производится расчет. Рассмотрим, как устанавливается защита.

Защита листа от изменения

Защита листа от внесения изменений выполняется с помощью команды Сервис → Защита → Защитить лист. В открывшемся окне (рис. 10.6) установите необходимые варианты защиты, выбрав одно из положений переключателя: Содержимое, Объекты, Сценарии. Затем, если необходимо, введите пароль и подтвердите его.

Рис. 10.6. Установка защиты

...

Внимание!

После выбора команды Защитить лист будут защищены от изменений только ячейки, помеченные как защищаемые. Чтобы изменить это свойство ячейки, используйте команду меню Формат → Ячейки и на вкладке Защита установите или снимите флажок Защищаемая ячейка.

Защита с помощью команды Скрыть

Можно полностью закрыть доступ к некоторым частям защищенного листа. Для этого выделите ячейки, информацию в которых необходимо скрыть. Выберите команду Формат → Ячейки и на вкладке Защита появившегося диалогового окна установите флажок Скрыть формулы.

Работа со списками

Программа Excel позволяет обрабатывать наборы самых разнообразных данных. Рассмотрим, как производится ведение больших списков в программе.

Понятие списокобозначает то же, что и база данных. Со списками вы сталкиваетесь на каждом шагу. Прайс-листы, данные о поставщиках и покупателях, сведения о товарах – вот примеры списков, или баз данных.

В программе Excel список – это таблица, данные в строках которой имеют однородную структуру, то есть в каждом столбце списка содержатся данные одного типа.

Список формируется на базе трех основных структурных элементов:

•  шапка таблицы,или заглавная строка, – она располагается в начале списка;

•  запись –строка с описаниями элементов списка;

•  ячейкаили поле,которое содержит отдельные данные записи.

Каждое поле в записи может быть объектом поиска или сортировки. Очень удобно создавать списки в программе Excel, а затем переносить их в бухгалтерскую базу данных. При составлении списков придерживайтесь следующих правил:

• Имена полей указывайте в верхней строке списка. Лучше всего выделить заглавную строку цветом.

• Каждую запись размещайте на отдельной строке. В одинаковых полях записей должны располагаться однотипные данные.

• Между записями не вставляйте пустые строки или строки, содержащие линии. Не начинайте поле с пробелов, старайтесь сделать список удобным для восприятия.

Добавлять записи в список можно непосредственно на листе или при помощи специальной формы (команда меню Данные → Форма):

1. Создайте список, содержащий следующие сведения: наименование товара, цена, единица измерения. Введите эти данные в ячейки А1:С1.

...

Совет

Для подбора ширины столбцов используйте команду Формат → Столбец → Автоподбор ширины.

2. Заполните таблицу данными: диван кожаный, 10 000, шт. Укажите эти данные в ячейках А2:С2.

3. Выделите одну из ячеек заголовка списка и выберите команду Данные → Форма. Появится диалоговое окно для редактирования списка (рис. 10.7). Обратите внимание на то, что в форме в качестве имен полей используются значения, указанные в заголовке списка, а в качестве образца приводится информация из первой строки.

Рис. 10.7. Добавление записи в список при помощи формы

4. Введите в каждое поле диалогового окна данные для следующих строк (см. рис. 10.7). Переходите от поля к полю с помощью щелчка мыши. Закончив ввод строки, щелкните на кнопке Добавить. 5. Внесите несколько записей и сохраните документ под названием База данных.

Поиск данных в списках

Если в списке много записей, то отыскать в нем какую-то одну довольно трудно. Для поиска необходимой записи используйте форму для быстрого поиска информации с помощью определенных критериев:

1. Для организации поиска выберите команду Данные → Форма.

2. Щелкните на кнопке Критерии.

3. Введите требуемые критерии поиска в подходящие поля. Можно искать величины большие, меньшие некоторого значения или совпадающие с ним. Для обозначения соответствующих операций сравнения используйте знаки < (меньше), > (больше), >= (больше или равно), <= (меньше или равно), ¹ (не равно).

Задание 1

В файле База данных организуйте поиск по полю Цена. Для организации поиска поместите рамку активной ячейки в начало списка (поиск организуется сверху вниз) и выберите команду Данные → Форма. Щелкните на кнопке Критерии, выберите цену больше 1200 (рис. 10.8) и нажмите клавишу Enter.

Рис. 10.8. Организация поиска по полю Цена

Просмотреть отобранные записи можно с помощью кнопок Назад и Далее.

Выбор элементов из списка

Формы являются удобным средством простого поиска. Для более удобной фильтрации данных из списка можно использовать специальные автофильтры: