Формирование учетных документов

1. В книге ОПЛАТА ПОСТАВОК переименуйте пять следующих листов – 1, 2, 3, 4, 5. Для вставки новых листов используйте ВСТАВИТЬ ЛИСТ или команду ВСТАВИТЬ (перед текущим листом) из контекстного меню для ярлыка листа. Переместить лист в линейке листов можно перетаскиванием ярлыка листа.

2. На листе № 1 сформируйте таблицу – блок ячеек А1:С10 (Рис. 7), оформите шапку и обрисуйте все границы. Для блока ячеек А2:А10 установите текстовый формат, для блока ячеек В2:В11 – формат КРАТКАЯ ДАТА, для блока ячеек С2:С11 – формат ДЕНЕЖНЫЙ, 2 десятичных знака, денежная единица – р. (вкладка ГЛАВНАЯ/группа ЧИСЛО или диалоговое окно ФОРМАТ ЯЧЕЕК/вкладка ЧИСЛО – Рис. 8).

Рис. 7	Рис. 8

3. Введите функцию суммирования над данными блока ячеек С2:С10 в ячейку С11 на листе № 1:

3.1. для ячейки С11 активизируйте кнопку АВТОСУММА – вкладка ФОРМУЛЫ/группа БИБЛИОТЕКА ФУНКЦИЙ;

3.2. выделите блок ячеек С2:С10, нажмите клавишу ENTER. Для активной ячейки С11 в строке формул отобразится =СУММ(С2:С10);

4. Введите функцию ЕСЛИ в ячейку В11 (Рис. 9) (если нет общей суммы оплаты в ячейке С11, то в текущей ячейке должно быть пусто, в противном случае в текущей ячейке должна быть максимальная дата из блока ячеек В2:В10) – вкладка ФОРМУЛЫ/группа БИБЛИОТЕКА ФУНКЦИЙ/список ЛОГИЧЕСКИЕ или кнопка ВСТАВИТЬ ФУНКЦИЮ/категория ЛОГИЧЕСКИЕ.

Для вставки в поле ЗНАЧЕНИЕ_ЕСЛИ_ЛОЖЬ другой функции МАКС (Рис. 10) (используйте список функций рядом со строкой формул (Рис. 11).

В результате для активной ячейки В11 в строке формул отразится =ЕСЛИ(С11=0;””;МАКС(В2:В10)).

Для редактирования функции в активной ячейке используйте кнопку ВСТАВИТЬ ФУНКЦИЮ ( ) рядом со строкой формул. Для редактирования вложенной функции установите в нее курсор в строке формул и используйте кнопку ВСТАВИТЬ ФУНКЦИЮ.

Рис. 9

Рис. 10

Рис. 11

5. Сделайте одинаковое наполнение листов №1, 2, 3, 4, 5. На листе № 1 выделите блок ячеек А1:С11 и скопируйте его в буфер обмена (вкладка ГЛАВНАЯ/группа буфера обмена), перейдите последовательно на листы № 2, 3, 4, 5 и вставьте скопированное начиная от ячейки А1, т.е. указывая только эту ячейку для вставки.

6. Введите данные в таблицы листов №1, 2, 3, 5 (Рис. 12). При этом удобно пользоваться маркером заполнения (+ в правом нижнем углу активной ячейки) и клавишей CTRL, затем увеличивать выделение блока при нажатой левой кнопке мыши.

Рис. 12

Обратите внимание, что происходит автоматический перерасчет формул при изменении значений, на которые формула ссылается, т.к. включен режим автоматических вычислений в книге (кнопка OFFICE/кнопка ПАРАМЕТРЫ EXCEL/группа ФОРМУЛЫ/группа ПАРАМЕТРЫ ВЫЧИСЛЕНИЙ/переключатель АВТОМАТИЧЕСКИ)

7. Сохраните сделанные в книге изменения.

Анализ оплаты поставок

1. Вставьте новый последний лист и назовите его КОНТРОЛЬ в книге ОПЛАТА ПОСТАВОК.

2. Сформируйте шапку таблицы (Рис. 13) на листе КОНТРОЛЬ, начиная от ячейки А1.

Рис. 13

3. Для правильности ввода номеров договоров (т.е. таких же, как на листе ДОГОВОР) в блок ячеек А2:А6 на листе КОНТРОЛЬ воспользуйтесь проверкой данных.

4. Введите в блок ячеек А2:А6 на листе КОНТРОЛЬ с помощью списка следующие договора: 01, 02, 01, 03, 02.

5. В блоке ячеек В2:В6 на листе КОНТРОЛЬ нужно указать соответствующие номерам договоров названия фирм, что целесообразно сделать с помощью функции ВПР (Рис. 14).

Рис. 14

Перед применением функции ВПР необходимо:

5.1. отсортировать таблицу на листе ДОГОВОР по возрастанию номеров договоров;

5.2. присвоить таблице на листе ДОГОВОР (блок ячеек A1:D4) имя ДОГОВОРЫ;

5.3. для ячейки В2 на листе КОНТРОЛЬ вызвать функцию ВПР (вкладка ФОРМУЛЫ/группа БИБЛИОТЕКА ФУНКЦИЙ/список ССЫЛКИ И МАССИВЫ).

Для заполнения ТАБЛИЦАнажмите клавишу F3 и выберите из списка таблицу ДОГОВОРЫ. При заполнении ИНТЕРВАЛЬНЫЙ_ПРОСМОТР надо учитывать диапазон просмотра. Если просматривать надо последовательно каждое значение, то следует ввести ноль.

Результат: =ВПР(А2;ДОГОВОРЫ;3) (Рис. 15);

5.4. скопируйте полученную функцию в ячейки блока В3:В6 на листе КОНТРОЛЬ с помощью маркера заполнения.

Рис. 15

6. Аналогично заполните блок ячеек С2:С6 на листе КОНТРОЛЬ с помощью функции ВПР и маркера заполнения.

7. Продолжите заполнение таблицы данными (Рис. 16).

Рис. 16

8. Даты последних оплат (блок ячеек G2:G6 на листе КОНТРОЛЬ) должны соответствовать максимальным датам по каждой ТТН (ячейки В11 на соответствующих листах по ТТН №1, 2, 3, 4, 5). Для этого следует:

8.1.  в активную ячейку G2 на листе КОНТРОЛЬ ввести = ;

8.2.  перейти на лист №1 (т.к. в ячейке D2 указана ТТН №1) и сделать активной ячейку В11, нажать на клавишу ENTER.

Т.о. в ячейку G2 на листе КОНТРОЛЬ будет введена ссылка на ячейку В11 листа №1 (=’1’!В11). Аналогично заполните весь блок ячеек G2:G6 на листе КОНТРОЛЬ.

9. Аналогично заполните следующую графу СУММА ОПЛАТЫ (В РУБ.), ссылаясь на ячейки С11 листов №1, 2, 3, 4, 5.

10. Долг по оплате рассчитывается как разность между суммой отгрузки и суммой оплаты:

в ячейку I2 на листе КОНТРОЛЬ введите =F2-H2 , ссылаясь ячейки мышью.

11. Дни задержки (блок ячеек J2:J6 на листе КОНТРОЛЬ) нужно рассчитать по следующему алгоритму: если дата последней оплаты отсутствует, то в графе ДНЕЙ ЗАДЕРЖКИ будет надпись НЕТ ОПЛАТЫ, в противном случае – разность между датой последней оплаты, датой отгрузки и сроком оплаты по договору. Т.е. например, для ячейки J2 с помощью функции ЕСЛИ будет указано: =ЕСЛИ(G2=””;”нет оплаты”;G2-E2-C2).

12. Для автоматического выделения долга цветом проделайте в блоке ячеек I2:I6 на листе КОНТРОЛЬ условное форматирование – вкладка ГЛАВНАЯ/группа СТИЛИ/список УСЛОВНОЕ ФОРМАТИРОВАНИЕ/ПРАВИЛА ВЫДЕЛЕНИЯ ТОЧЕК/БОЛЬШЕ…, указать больше 0 и заливку.

Удаление условного форматирования для блока или листа – аналогично в списке условного форматирования команда УДАЛИТЬ ПРАВИЛА.

13. В ячейку L1 на листе КОНТРОЛЬ введите значение 40,50 €. В блоке ячеек К2:К6 рассчитаем сумму оплаты (в у.е.) как деление построчно суммы оплаты (в руб.) на значение ячейки L1, например =H2/L1 для ячейки К2. При использовании маркера заполнения для блока ячеек К2:К6 в большинстве ячеек отобразится #ДЕЛ/0!, т.к. изменилась адресация в формуле – ссылка на ячейку L1. Для корректности ссылок при копировании/переносе формул вместо относительной адресации требуется использовать абсолютную адресацию ($), например =H2/L$1 или =H2/$L$1 для ячейки К2. Знак $ устанавливается с помощью последовательного нажатия на клавишу F4 при курсоре в соответствующем адресе ячейки в строке формул.

14. Рассчитайте снова сумму оплаты в у.е., применяя имя ячейки L1. Аналогичное п. 13 действие можно сделать через именование ячеек, т.е. ячейке L1 присвоить имя (например, КУРС_ВАЛЮТЫ) и в формуле использовать ссылку на это имя (например, =H2/КУРС_ВАЛЮТЫ). Это удобно пользователю для понимания действия формул.

15. Подсчитайте общие суммы отгрузки, оплаты в руб. и у.е., долга в соответствующих ячейках строки 7 на листе КОНТРОЛЬ.

16. Отформатируйте блок ячеек А7:К7 на листе КОНТРОЛЬ с помощью готового стиля АКЦЕНТ – вкладка ГЛАВНАЯ/группа СТИЛИ/список СТИЛИ ЯЧЕЕК. При необходимости проведите форматирование и в других ячейках, чтобы получить конечный результат как на Рис. 17.

Рис. 17

17. Закрепите верхнюю строку с шапкой таблицы, т.е. она будет постоянно видна на экране во время вертикальной прокрутки, что удобно использовать для больших таблиц – вкладка ВИД/группа ОКНО/список ЗАКРЕПИТЬ ОБЛАСТИ/ЗАКРЕПИТЬ ВЕРХНЮЮ СТРОКУ.

Удаление закрепления – аналогично в списке ЗАКРЕПИТЬ ОБЛАСТИ команда СНЯТЬ ЗАКРЕПЛЕНИЕ ОБЛАСТЕЙ.

18. Скопируйте лист КОНТРОЛЬ (с помощью контекстного меню для ярлыка листа КОНТРОЛЬ, команда ПЕРЕМЕСТИТЬ/СКОПИРОВАТЬ – Рис. 18), вставьте три его копии, переименуйте в АВТОФИЛЬТР, РАСШ ФИЛЬТР, ИТОГИ.

Рис. 18

Ввод новых данных

При вводе новых данных формулы, ссылающиеся на них, по умолчанию автоматически пересчитываются – кнопка OFFICE/кнопка ПАРАМЕТРЫ EXCEL/группа ФОРМУЛЫ/ПАРАМЕТРЫ ВЫЧИСЛЕНИЙ/ВЫЧИСЛЕНИЯ В КНИГЕ.

1. Предположим, что ТТН №4 оплатили двумя ПП:

1.1. ПП №5 от 26.01.11 на сумму 300 руб. и ПП №6 от 27.01.11 на сумму 700 руб., т.е. полная оплата ТТН №4. Соответствующие данные введите на лист №4;

1.2. произойдет пересчет формул на текущем листе №4 и на листе КОНТРОЛЬ относительно ТТН №4 и итоговых значений.

2. Верните данные на листе №4 в первоначальное состояние – отсутствие ПП.

Автофильтр

Автофильтр применяется для отображения данных, отвечающих какому-то условию или условиям. Остальные данные будут скрыты от просмотра.

1. Очистите от содержимого и форматов итоговую 7-ю строку на листе АВТОФИЛЬТР – вкладка ГЛАВНАЯ/группа РЕДАКТИРОВАНИЕ/список ОЧИСТИТЬ/команда ОЧИСТИТЬ ВСЕ.

2. Выведите все неоплаты с помощью автофильтра:

2.1. сделайте активной любую ячейку таблицы на листе АВТОФИЛЬТР и активизируйте вкладку ДАННЫЕ/группу СОРТИРОВКА И ФИЛЬТР/кнопку ФИЛЬТР. В названии каждого столбца таблицы появится кнопка со стрелкой, с помощью которой можно управлять фильтрацией;

2.2. активизируйте кнопку со стрелкой по графе ДНЕЙ ЗАДЕРЖКИ;

2.3. в появившемся окне снимите флажок ВЫДЕЛИТЬ ВСЕ и поставьте флажок НЕТ ОПЛАТЫ.

3. Отмените условие фильтрации – кнопка ОЧИСТИТЬ на вкладке ДАННЫЕ/группа СОРТИРОВКА И ФИЛЬТР. Для отказа от возможностей фильтрации нужно отжать кнопку ФИЛЬТР.

4. Выведите на листе АВТОФИЛЬТР данные по суммам отгрузки от 10000 руб. до 50000 руб.:

4.1. активизируйте кнопку со стрелкой по графе СУММЫ ОТГРУЗКИ (В РУБ.);

4.2. активизируйте список ЧИСЛОВЫЕ ФИЛЬТРЫ/команда МЕЖДУ…;

4.3. настройте условие в открывшемся окне ПОЛЬЗОВАТЕЛЬСКИЙ АВТОФИЛЬТР, используя кнопки со списками и редактируя полученные из списков значения (Рис. 19).

Рис. 19

5. Добавьте к существующему новое условие – наличие долга. Т.о. будет реализовано 2 условия – вывод сумм отгрузки от 10000 руб. до 50000 руб И наличия долга:

5.1. активизируйте кнопку со стрелкой по графе ДОЛГ;

5.2. активизируйте список ЧИСЛОВЫЕ ФИЛЬТРЫ/команда БОЛЬШЕ…;

5.3. настройте условие в открывшемся окне ПОЛЬЗОВАТЕЛЬСКИЙ АВТОФИЛЬТР (БОЛЬШЕ) и введите 0.

Расширенный фильтр

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

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

Если к одной и той же таблице надо применить несколько условий, то диапазонам условий (как спискам) удобно присвоить имена, которые затем можно использовать вместо ссылок.

Примеры диапазонов условий:

Условия в одной строке считаются соединёнными логическим оператором И,т.е. из таблицы будут отобраны строки при выполнения всех условий (оклад больше 10000 И возраст больше 50).

Расположение условий в разных строкахдиапазона условий соответствует логическому оператору ИЛИ. Пустая ячейка в диапазоне условий означает любые значения.

Указаны два условия, соединённые логическим оператором ИЛИ:

1. оклад больше 10000 И любой возраст

ИЛИ

2. возраст больше 50 И любой оклад

Из таблицы будут отобраны строки, удовлетворяющие хотя бы одному из всех условий ИЛИ.

1. Выведите на листе РАСШ ФИЛЬТР данные по долгу больше или равно 2000 руб. И просроченным срокам оплаты (дни задержки больше 0):

1.1. для исключения итоговой строки из фильтрации присвойте блоку ячеек А1:К6 имя ТАБЛ_ДЛЯ_ФИЛЬРА;

1.2. создайте диапазон условий, начиная от ячейки А12 (Рис. 20). Заголовки для диапазона условий лучше копировать из таблицы;

1.3. активизируйте вкладку ДАННЫЕ/группу СОРТИРОВКА И ФИЛЬТР/кнопку ДОПОЛНИТЕЛЬНО;

1.4. укажите в окне РАСШИРЕННЫЙ ФИЛЬТР, что ФИЛЬТРОВАТЬ СПИСОК[3] НА МЕСТЕ исходной таблицы, исходный диапазон - ТАБЛ_ДЛЯ_ФИЛЬТРА (с помощью клавиши F3), выделите диапазон условий – блок ячеек А12:В13 (Рис. 20                                                   Рис. 21).

Рис. 20                                                Рис. 21

На месте исходной таблицы останутся видимыми строки, отвечающие условиям (Рис. 22). Итоговая строка будет отображаться, т.к. она не должна подвергаться фильтрации и не была указана в блоке ТАБЛ_ДЛЯ_ФИЛЬРА.

Рис. 22

2. Отмените фильтрацию - кнопка ОЧИСТИТЬ на вкладке ДАННЫЕ/группа СОРТИРОВКА И ФИЛЬТР.

3. При имеющемся условии выведите результат фильтрации на другом месте, начиная от ячейки D12. Для этого в окне РАСШИРЕННЫЙ ФИЛЬТР установите переключатель в позицию СКОПИРОВАТЬ РЕЗУЛЬТАТ В ДРУГОЕ МЕСТО, укажите в ПОМЕСТИТЬ РЕЗУЛЬТАТ В ДИАПАЗОН - ячейку D12 (с помощью мыши). Исходный диапазон и диапазон условий остаются прежними.

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

4.1. скопируйте блок ячеек А1:В1 и вставьте, начиная от ячейки D15;

4.2. в окне РАСШИРЕННЫЙ ФИЛЬТР укажите в ПОМЕСТИТЬ РЕЗУЛЬТАТ В ДИАПАЗОН – блок ячеек D15:Е15 (шапка таблицы-результата фильтрации). Остальные настройки остаются прежними (Рис. 23).

Рис. 23

5. Нужно вывести на листе РАСШ ФИЛЬТР данные по долгу больше или равно 2000 руб. И просроченным срокам оплаты (дни задержки больше 0). ИЛИ данные по отсутствию суммы оплаты в руб. Результат фильтрации поместите, начиная от ячейки Е12:

5.1. очистите блок ячеек D12:N16 (результаты предыдущей фильтрации);

5.2. добавьте в диапазон условий новое условие - ИЛИ данные по отсутствию суммы оплаты в руб. (Ошибка! Источник ссылки не найден.);

5.3. заполните окно РАСШИРЕННЫЙ ФИЛЬТР (Ошибка! Источник ссылки не найден.).

Рис. 24

Итоги

Итоги позволяют производить вычисления над сгруппированными данными.

1. Очистите от содержимого и форматов итоговую 7-ю строку на листе ИТОГИ.

2. На листе ИТОГИ по каждой фирме нужно подсчитать количество отгрузок (Рис. 26):

2.1. сначала надо отсортировать данные в таблице на листе ИТОГИ по тому полю, по которому создаются группы для вычисления итогов. В данном примере расчеты осуществляются по каждой фирме, следовательно, сортировка делается в таблице по возрастанию данных в графе НАЗВАНИЕ ФИРМЫ;

2.2. сделайте активной ячейку в таблице и активизируйте вкладку ДАННЫЕ/группу СТРУКТУРА/кнопку ПРОМЕЖУТОЧНЫЕ ИТОГИ;

2.3. В диалоговом окне ПРОМЕЖУТОЧНЫЕ ИТОГИ укажите (Рис. 25):

2.3.1. ПРИ КАЖДОМ ИЗМЕНЕНИИ В - название графы, по которой проводилась последняя сортировка - НАЗВАНИЕ ФИРМЫ;

2.3.2. ОПЕРАЦИЯ – КОЛИЧЕСТВО;

2.3.3. ДОБАВИТЬ ИТОГИ ПО - № ТТН. Флажки с названий других граф нужно снять;

2.3.4. флажок ИТОГИ ПОД ДАННЫМИ.

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

Удаление структуры и итогов – кнопка УБРАТЬ ВСЕ в диалоговом окне ПРОМЕЖУТОЧНЫЕ ИТОГИ.

Рис. 25

Рис. 26

3. На листе ИТОГИ дополнительно к существующим итогам нужно подсчитать общую сумму отгрузки (в руб.) по каждой фирме (Рис. 28):

3.1. в диалоговом окне ПРОМЕЖУТОЧНЫЕ ИТОГИ установите (Рис. 27):

3.1.1. ОПЕРАЦИЯ – СУММА;

3.1.2. ДОБАВИТЬ ИТОГИ ПО – СУММА ОТГРУЗКИ (В РУБ.). Флажки с названий других граф нужно снять;

3.1.3. снимите флажок ЗАМЕНИТЬ ТЕКУЩИЕ ИТОГИ, иначе последние итоги заменят предыдущие.

Рис. 27

Рис. 28

Построение диаграмм

1. Постройте на основе прежнего диапазона данных (А1:К6 на листе КОНТРОЛЬ) на новом листе новую сводную таблицу (Рис. 29), в которой по каждому договору помесячно просуммируются все данные по отгрузке в руб. :

1.1. поле НАЗВАНИЕ ФИРМЫ в области ФИЛЬТР ОТЧЕТА, поле НОМЕР ДОГОВОРА в области НАЗВАНИЯ СТРОК, поле ДАТА ОТГРУЗКИ в области НАЗВАНИЯ СТОЛБЦОВ, поле СУММА ОТГРУЗКИ (В РУБ.) в области ЗНАЧЕНИЯ;

1.2. для создания группы месяца - при активной ячейке с конкретной датой отгрузки активизируйте на вкладке ПАРАМЕТРЫ/список ГРУППИРОВАТЬ/команда ГРУППИРОВКА ПО ПОЛЮ. В диалоговом окне ГРУППИРОВАНИЕ выберите шаг группировки – МЕСЯЦЫ, период для группирования оставьте выбранный по умолчанию (с 15.01.11. по 28.01.11.);

1.3. уберите общие итоги;

1.4. уберите заголовки полей.

Рис. 29	Рис. 30

2. На основе такой сводной таблицы (на экране только необходимое для отображения на диаграмме и нет итогов) можно быстро построить сводную диаграмму (Рис. 30) на текущем или отдельном листе:

2.1. при активной ячейке в сводной таблице активизируйте вкладку ПАРАМЕТРЫ/группа СЕРВИС/кнопка СВОДНАЯ ДИАГРАММА;

2.2. в окне ВСТАВКА ДИАГРАММЫ укажите вид желаемой диаграммы – ОБЪЕМНАЯ КРУГОВАЯ;

2.3. измените название диаграммы – ОТГРУЗКА В РУБ. ПО ДОГОВОРАМ.

Настройки диаграммы – команды в контекстном меню для диаграммы или на вкладках КОНСТРУКТОР, МАКЕТ, ФОРМАТ для активной диаграммы.

Отображение на диаграмме зависит от видимого в сводной таблице и наоборот (окно ОБЛАСТЬ ФИЛЬТРА СВОДНОЙ ДИАГРАММЫ).

Содержание отчета

1. В отчете указать тему, цель работы.

2. Описать основные этапы выполнения работы

3. Записать выводы по проделанной работе.

Контрольные вопросы

1. Для чего нужен табличный процессор?

2. Что такое табличный процессор? Для чего он предназначен?

3. Как запустить Microsoft Excel ?

4. Где находится поле имени ? Для чего оно предназначено?

5. Для чего используется строка формул? Где она находится?

6. Что такое табличный курсор?

7. Где находится маркер заполнения? Как им пользоваться?

8. Какие данные можно вносить в ячейки?

9. Как исправить информацию в ячейке?

10. Каким образом производится расчет формул?

11. Как поменять формат ячеек?

12. Что такое сортировка и фильтр?

13. Как изменить количество выводимых знаков после запятой?

14. Что называется формулой в Excel ?

15. Как ввести формулу в ячейку7

16. Где отображается результат вычислений, а где содержимое ячейки?

17. Что такое относительные ссылки? Привести пример использования.

18. Что такое абсолютная ссылка на строку? Пример.

19. Что такое абсолютная ссылка на столбец? Пример.

Лабораторная работа №7
Тема: Создание таблиц

Цель работы: Научиться проектировать базу данных. Освоить создание базы данных различными способами.

Оборудование рабочего места: Персональный компьютер, ОС Windows XP и выше, Пакет ПО Microsoft Office.