Создание и ведение таблицы исходных данных

Прежде чем создавать таблицу, надо тщательно продумать её структуру и определить, какие данные включить в неё. Создание таблицы нужно начинать с формирования заголовков столбцов. Символы шрифта, используемые в заголовках таблицы должны иметь характерные начертания, отличные от тех, которые использовались для данных внутри таблицы (например, курсив).

При создании таблиц в EXCEL необходимо выполнять следующие правила:

· таблицу исходных данных рекомендуется помещать на отдельном рабочем листе;

· каждый столбец в таблице должен содержать во всех строках однотипные данные (текстовые или числовые или даты);

· не следует вводить пробелы в начале содержимого ячеек, поскольку они влияют на сортировку и поиск.

Поле со списком

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

Выполнить команду Данные / Проверка. В качестве Типа данных выбрать Список. Источник – имя именованного блока, где находятся данные. Имя выбрать с помощью функциональной клавиши F3. Нажать ОК. В результате в выделенной ячейке появится знак стрелка (признак поля со списком). Знак стрелка позволяет раскрыть список и выбрать из него значение. Скопировать эту настройку в другие ячейки столбца. Переходя последовательно в ячейки столбца, можно выбирать необходимое значение из списка.

Значения для поля со списком можно задать непосредственно в окне Источник. Для этого достаточно ввести в окне Источник значения списка, разделяя их знаком «;» (точка с запятой). Такой способ задания списка используется, когда в списке мало значений.

 

Создание исходной таблицы «Ведомость»

На новом листе Ведомость создать таблицу следующего вида:

 

Обеспечить ввод данных в поле Код заказчика для ячеек А6:А16 через список Код_зак.

Для этого выполнить действия:

· выделить ячейки А6:А16;

· выполнить команду Данные/Проверка;

· в открывшемся окне установить Тип данных – Список. Установить Источник – Код_зак. Для этого нажать функциональную клавишу F3 и выбрать из списка Код_зак. Нажать ОК.

· В таблице Ведомость в столбце Код заказчика справа от ячейки А6 появится стрелка (поле со списком). С помощью стрелки можно раскрыть список и выбрать значение Кода заказчика. Перейти в ячейку А7 и заполнить ее. Аналогично з аполнить другие ячейки столбца в соответствии с исходной таблицей.

Функции ПРОСМОТР и ВПР

Для автоматизации заполнения столбцов Наименование заказчика и Адрес таблицы можно применить функции ВПР или ПРОСМОТР. При использовании этих функций необходимо выполнить сортировку списка Заказчики по полю Кода заказчика

Применение функции ПРОСМОТР

Отобразить значения полей Наименование заказчика и Адрес согласно коду заказчика из таблицы Заказчики с помощью функции ПРОСМОТР.

Наименование заказчика и Адрес находятся в прямой зависимости от Кода заказчика. Поэтому для заполнения значений этих полей можно применить функцию ПРОСМОТР. Рассмотрим применение функции ПРОСМОТР:

· После вызова функции ПРОСМОТР в окне «Мастер функций»

нажать ОК.

· В следующем окне надо установить все параметры.

В окно Искомое_значение поместить А6 щелчком по ячейке А6 таблицы Ведомость.

В окно Просматриваемый_вектор с помощьюфункциональной клавиши F3 вызвать имя блока ячеек Код_зак.

В окно Вектор_результатов с помощью функциональной клавиши F3 вызвать имя блока ячеек Наим_зак. Нажать ОК в главном окне функции ПРОСМОТР.

· Скопировать полученную формулу в ячейки В7:В16 таблицы Ведомость. В результате столбец Наименование заказчика будет заполнен. Аналогично заполним столбец Адрес. Установим параметры.

· После ввода параметров нажать ОК.

· Скопировать полученную формулу в ячейки С7:С16.

В результате столбец Адрес будет заполнен.

 

Применение функции ВПР

Отобразить значения полей Наименование заказчика и Адрес с помощью функции ВПР (вертикальный просмотр). Для этого удалить полученные выше значения из столбцов Наименование заказчика и Адрес и выполнить:

· Установить курсор в ячейку В6 и вызвать функцию ВПР.

· Заполнить параметры функции ВПР.

В окно Искомое_значение поместить А6 (щелчком по ячейке А6 таблицы Ведомость).

· Для заполнения окна Таблица нажать функциональную клавишу F3 и выбрать из списка таблицу Заказчики.

· Для заполнения окна Номер_столбца ввести цифру 2, (это номер столбца списка Заказчики).

· При заполнении окна Интервальный просмотр надо учитывать диапазон просмотра. Если просматривать надо последовательно каждое значение, то следует ввести ноль. Нажать ОК в главном окне функции ВПР.

· Скопировать полученную формулу в ячейки В7:В16. В результате столбец Наименование заказчика будет заполнен.

Аналогично заполнить столбец Адрес.

 

Создание списка для поля Период

Период создать в виде списка значений:1 кв; 2 кв; 3 кв. Выделить столбец Период. Выполнить команду Данные/Проверка. В диалоговом окне Проверка вводимых значений заполнить источник.

Т.к. список состоит всего из 3-х значений, то их можно ввести в окне источник, разделяя знаком; (точка с запятой). Введем данные в таблицу в поле Период согласно исходной таблице:

Заполнить данными поля Сумма к выплате и Оплачено в соответствии с исходной таблицей.

Расчёт полей Разница и Долг

Рассчитать поле Разница = Сумма к выплате - Оплачено. Активизировать ячейку G6. Ввести знак = (равно), затем щелкнуть ячейку Е6, затем знак – (минус), щелкнуть ячейку F6. В ячейке получится выражение = Е6 - F6.. Нажать Enter. Затем протащить маркер по всем ячейкам столбца, в которых надо получить результат (ячейки G6: G16).

Рассчитать значения поля Долг. При значении поля Разница больше нуля Долг равен Разнице, в противном случае Долг равен нулю.

Логические функции

Логические функции ЕСЛИ, И, ИЛИ и НЕ используют логические выражения для определения истинности заданного условия. Например, каждая из приведенных ниже формул является логическим выражением:

А1>A2; B1=СРЗНАЧ($В$1:$В$6); С2=”Среднее”

Любое логическое выражение должно содержать, по крайней мере, один оператор сравнения, который определяет отношение между элементами логического выражения. Например, в логическом выражении А1>А2 оператор больше (>). В качестве операторов сравнения могут быть: =, >, <, >=, <=, <> (не равно). Результатом логического выражения является логическое значение ИСТИНА или логическое значение ЛОЖЬ.

 

Применение функции ЕСЛИ для вычисления Долга.

Активизируем ячейку Н6. Вызовем функцию ЕСЛИ и заполним значения параметров в окне функции:

В строке формул получим =ЕСЛИ (G6>0;G6;0). Формула

возвращает значение из ячейки G6, если значение в ячейке G6 больше 0. В противном случае возвращает число 0.

Функция ЕСЛИ имеет следующий синтаксис:

ЕСЛИ (логическое выражение; значение если ИСТИНА; значение если ЛОЖЬ)

Расчёт поля Долг + Пеня

Рассчитаем значения поля Долг+Пеня, используя имя Пеня. Присвоим ячейкам поля Долг+Пеня числовой формат с двумя знаками после запятой. Для расчета поля Долг+Пеня а ктивизируем ячейку I6. Введем выражение =Н6+Н6*Пеня. Имя Пеня ввести в выражение с помощью клавиши F3.

 

Вычисление общей суммы долга

Объединим ячейки В3:D3. Для этого выделим эти ячейки. Выполним команду Формат / Ячейки… Закладка Выравнивание. Установим флажок на объединение ячеек. В объединённые ячейки введём текст Общая сумма долга:

В ячейке Е3 рассчитаем сумму по полю Долг+Пеня с помощью функции Сумм. Для этого активизируем ячейку Е3, вызовем функцию Сумм с помощью команды Вставка/Функции... Затем выделим ячейки I6:I16, нажмём ОК. Получим общую сумму долга в ячейке Е3. Чтобы выделить ячейку Е3 каким-либо цветом,надо сделать ее активной, затем воспользоваться кнопкой панели инструментов (цвет заливки). Цвет для ячейки Е3 выбрать любой.

Сформированная таблица на листе Ведомость имеет вид:

Защита листа

Защитить лист Ведомость от изменений можно с помощью команды Сервис /Защита /Защитить лист… (если ввели пароль, то необходимо помнить его).

Вставка новых листов

Вставить новый лист, расположив его за листом Ведомость, назвать новый лист Рабочая ведомость. Скопировать с листа Ведомость основную таблицу (А5:I16) на лист Рабочая ведомость в ячейку А1.

Проверить правильность скопированных данных в поле Долг+Пеня. Сравнить значения полей этой и таблицы на листе Ведомость.

Дать имя Ведомость_список блоку ячеек А1:I12 на листе Рабочая ведомость.

 

Копирование листа Рабочая ведомость

Скопировать лист Рабочая ведомость 3 раза, разместить эти листы за листом Рабочая ведомость. Назвать листы Сортировка, Итоги и Автофильтр, Для копирования применить команду Правка/Переместить /Скопировать лист… В окне установить флажок на Создать копию.

 

Часть II

Сортировка таблицы

Перейдем на лист Сортировка. Отсортируем таблицу по полю Адрес, затем по полю Наименование заказчика, затем по полю Период. В окне Сортировка сделаем установки:

 

 

Получим:

Формирование итогов

Перейдем на лист Итоги. Получим итоги (операция сумма) по полям Сумма к выплате, Оплачено и Долг для каждого периода. Для этого отсортируем таблицу по полю Период. Затем выполним команду Данные/Итоги. Появится окно Промежуточные итоги. Сделаем в окне указанные установки. Нажмем ОК.

Получим:

При вычислении итогов таблица структурируется.

Чтобы отобразить на экране только итоговые данные, следует выполнить щелчок на кнопке 2 (второго уровня структуры), вследствие чего данные третьего уровня (исходные значения) будут скрыты.

 

Получим:

Для восстановления отображения исходных значений с промежуточными итогами необходимо выполнить щелчок на кнопке 3 (третьего уровня). Если выполнить команду Данные / Итоги, а в окне Промежуточные итоги нажать кнопку Убрать все получим исходную таблицу.

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