Файл: По результатам.doc

Рис. 24

8. Итоги

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

1. 
   Очистите от содержимого и форматов итоговую 7-ю строку на листе ИТОГИ.
   
2. 
   На листе ИТОГИ по каждой фирме нужно подсчитать количество отгрузок (Рис. 26):
   
   1. 
      сначала надо отсортировать данные в таблице на листе ИТОГИ по тому полю, по которому создаются группы для вычисления итогов. В данном примере расчеты осуществляются по каждой фирме, следовательно, сортировка делается в таблице по возрастанию данных в графе НАЗВАНИЕ ФИРМЫ;
      
   2. 
      сделайте активной ячейку в таблице и активизируйте вкладку ДАННЫЕ/группу СТРУКТУРА/кнопку ПРОМЕЖУТОЧНЫЕ ИТОГИ;
      
   3. 
      В диалоговом окне ПРОМЕЖУТОЧНЫЕ ИТОГИ укажите (Рис. 25):
      
      1. 
         ПРИ КАЖДОМ ИЗМЕНЕНИИ В - название графы, по которой проводилась последняя сортировка - НАЗВАНИЕ ФИРМЫ;
         
      2. 
         ОПЕРАЦИЯ – КОЛИЧЕСТВО;
         
      3. 
         ДОБАВИТЬ ИТОГИ ПО - № ТТН. Флажки с названий других граф нужно снять;
         
      4. 
         флажок ИТОГИ ПОД ДАННЫМИ.
         

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

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



Рис. 25



Рис. 26

3. 
   На листе ИТОГИ дополнительно к существующим итогам нужно подсчитать общую сумму отгрузки (в руб.) по каждой фирме (Рис. 28):
   
   1. 
      в диалоговом окне ПРОМЕЖУТОЧНЫЕ ИТОГИ установите (Рис. 27):
      
      1. 
         ОПЕРАЦИЯ – СУММА;
         
      2. 
         ДОБАВИТЬ ИТОГИ ПО – СУММА ОТГРУЗКИ (В РУБ.). Флажки с названий других граф нужно снять;
         
      3. 
         снимите флажок ЗАМЕНИТЬ ТЕКУЩИЕ ИТОГИ, иначе последние итоги заменят предыдущие.
         

Рис. 27

---

Рис. 28

---

Задание 5 «Финансовые функции Excel»

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

1. 
   На основе данных листа КОНТРОЛЬ на отдельном листе постройте сводную таблицу (Рис. 29).
   

Рис. 29

1. 
   сделайте текущим лист КОНТРОЛЬ и активизируйте вкладку ВСТАВКА/группа ТАБЛИЦЫ/кнопка ВСТАВИТЬ СВОДНУЮ ТАБЛИЦУ или список СВОДНАЯ ТАБЛИЦА/команда СВОДНАЯ ТАБЛИЦА;
   
2. 
   в диалоговом окне СОЗДАНИЕ СВОДНОЙ ТАБЛИЦЫ выделите диапазон данных (вместе с шапкой), на основе которого будет строиться сводная таблица - А1:К6 на листе КОНТРОЛЬ. Укажите, что отчет сводной таблицы будет размещен на новом листе;
   
3. 
   сформируйте макет сводной таблицы в окне СПИСОК ПОЛЕЙ СВОДНОЙ ТАБЛИЦЫ⁴:
   
   1. 
      перетащите с помощью мыши поле НАЗВАНИЕ ФИРМЫ в область ФИЛЬТР ОТЧЕТА. По этому полю проводится отбор записей;
      
   2. 
      перетащите поле НОМЕР ДОГОВОРА в область НАЗВАНИЯ СТРОК;
      
   3. 
      перетащите поля СУММА ОТГРУЗКИ (В РУБ.), ДОЛГ в область ЗНАЧЕНИЯ. По этим полям проводятся расчеты (по умолчанию – суммирование).
      

Сводная таблица будет построена в левой части окна Excel.

При необходимости удаления поля из макета используйте этот же метод перетаскивания в окне СПИСОК ПОЛЕЙ СВОДНОЙ ТАБЛИЦЫ или сделайте текущим поле в сводной таблице, вызовите для него контекстное меню и выберите команду УДАЛИТЬ (НАЗВАНИЕ ПОЛЯ).

4. 
   для создания % ЗАДОЛЖЕННОСТИ перетащите поле ДОЛГ второй раз в область ЗНАЧЕНИЯ, сделайте это поле активным в сводной таблице и выберите на вкладке ПАРАМЕТРЫ/список АКТИВНОЕ ПОЛЕ/кнопка ПАРАМЕТРЫ ПОЛЯ.
   

В диалоговом окне ПАРАМЕТРЫ ПОЛЯ ЗНАЧЕНИЙ на вкладке ДОПОЛНИТЕЛЬНЫЕ ВЫЧИСЛЕНИЯ выберите в списке вариант ДОЛЯ ОТ СУММЫ ПО СТОЛБЦУ и укажите числовой формат – ПРОЦЕНТНЫЙ, 0 десятичных знаков.

---

В этом же диалоговом окне введите пользовательское имя - % ЗАДОЛЖЕННОСТИ;

5. 
   полям СУММА ОТГРУЗКИ (В РУБ.), ДОЛГ аналогично дайте пользовательские имена ОТГРУЗКА и СУММАРНЫЙ ДОЛГ, укажите числовой формат – ДЕНЕЖНЫЙ, 2 десятичных знака;
   
6. 
   заголовки полей не отображайте в сводной таблице – отожмите аналогичную кнопку на вкладке ПАРАМЕТРЫ/группа ПОКАЗАТЬ ИЛИ СКРЫТЬ.
   
1. 
   Просмотрите с помощью фильтра отчета данные по разным фирмам. Выведите снова данные по всем фирмам.
   
2. 
   Добавьте в сводную таблицу поле расчета количества отгрузок:
   
   1. 
      перетащите поле № ТТН в область ЗНАЧЕНИЯ;
      
   2. 
      в диалоговом окне ПАРАМЕТРЫ ПОЛЯ ЗНАЧЕНИЙ на вкладке ОПЕРАЦИЯ выберите КОЛИЧЕСТВО;
      
   3. 
      в этом же окне укажите пользовательское имя – КОЛИЧЕСТВО ОТГРУЗОК;
      
   4. 
      переместите поле КОЛИЧЕСТВО ОТГРУЗОК на первое место в области ЗНАЧЕНИЯ⁵ – сделайте это поле активным в списке ЗНАЧЕНИЯ в диалоговом окне СПИСОК ПОЛЕЙ СВОДНОЙ ТАБЛИЦЫ и после нажатия на левую кнопку мыши последовательно выберите команду ПЕРЕМЕСТИТЬ ВВЕРХ.
      
3. 
   Уберите общие итоги в сводной таблице (Рис. 30) – при активной ячейке в сводной таблице выберите на вкладке ПАРАМЕТРЫ/список СВОДНАЯ ТАБЛИЦА/кнопка ПАРАМЕТРЫ. В диалоговом окне ПАРАМЕТРЫ СВОДНОЙ ТАБЛИЦЫ на вкладке ИТОГИ И ФИЛЬТРЫ снимите флажок ПОКАЗЫВАТЬ ОБЩИЕ ИТОГИ ДЛЯ СТОЛБЦОВ.
   

При активной ячейке в сводной таблице можно включить итоги, выбрав вкладку КОНСТРУКТОР/группа МАКЕТ/список ОБЩИЕ ИТОГИ/команда ВКЛЮЧИТЬ ТОЛЬКО ДЛЯ СТОЛБЦОВ.

5. 
   Произведите оформление сводной таблицы стилем - при активной ячейке в сводной таблице выберите вкладку КОНСТРУКТОР/группа СТИЛИ СВОДНОЙ ТАБЛИЦЫ.
   

Рис. 30

6. 
   В новом поле сводной таблицы рассчитайте суммарный долг в у.е. Курс будет фиксированным – 40,5:
   
   1. 
      при активной ячейке в сводной таблице активизируйте вкладку ПАРАМЕТРЫ/группа СЕРВИС/список ФОРМУЛЫ/команда ВЫЧИСЛЯЕМОЕ ПОЛЕ;
      
   2. 
      в диалоговом окне ВСТАВКА ВЫЧИСЛЯЕМОГО ПОЛЯ установите курсор в поле ФОРМУЛА и удалите «0»;
      
   3. 
      в списке ПОЛЯ выберите поле ДОЛГ и нажмите кнопку ДОБАВИТЬ ПОЛЕ. Это поле отобразится в поле ФОРМУЛА;
      
   4. 
      далее введите в поле ФОРМУЛА - /40,5
      

---

Т.о. в поле формула будет указано: = Долг/40,5

5. 
   в поле ИМЯ введите имя нового поля – ДОЛГ В У.Е.;
   
6. 
   нажмите кнопки ДОБАВИТЬ, ОК.
   

Для удаления вычисляемого поля из сводной таблицы можно использовать это же диалоговое окно ВСТАВКА ВЫЧИСЛЯЕМОГО ПОЛЯ, где в списке ИМЯ выбрать поле для удаления, нажать кнопку УДАЛИТЬ.

Аналогично редактируется вычисляемое поле.

7. 
   настройте для этого поля числовой формат, 2 десятичных знака (Рис. 31).
   

Рис. 31

7. 
   Постройте на основе прежнего диапазона данных (А1:К6 на листе КОНТРОЛЬ) на новом листе новую сводную таблицу (Рис. 32), в которой по каждому договору помесячно просуммируются все данные по отгрузке в руб. :
   
   1. 
      поле НАЗВАНИЕ ФИРМЫ в области ФИЛЬТР ОТЧЕТА, поле НОМЕР ДОГОВОРА в области НАЗВАНИЯ СТРОК, поле ДАТА ОТГРУЗКИ в области НАЗВАНИЯ СТОЛБЦОВ, поле СУММА ОТГРУЗКИ (В РУБ.) в области ЗНАЧЕНИЯ;
      
   2. 
      для создания группы месяца - при активной ячейке с конкретной датой отгрузки активизируйте на вкладке ПАРАМЕТРЫ/список ГРУППИРОВАТЬ/команда ГРУППИРОВКА ПО ПОЛЮ. В диалоговом окне ГРУППИРОВАНИЕ выберите шаг группировки – МЕСЯЦЫ, период для группирования оставьте выбранный по умолчанию (с 15.01.11. по 28.01.11.);
      
   3. 
      уберите общие итоги;
      
   4. 
      уберите заголовки полей.
      

      Рис. 32	Рис. 33

8. 
   На основе такой сводной таблицы (на экране только необходимое для отображения на диаграмме и нет итогов) можно быстро построить сводную диаграмму (Рис. 33) на текущем или отдельном листе:
   
   1. 
      при активной ячейке в сводной таблице активизируйте вкладку ПАРАМЕТРЫ/группа СЕРВИС/кнопка СВОДНАЯ ДИАГРАММА;
      
   2. 
      в окне ВСТАВКА ДИАГРАММЫ укажите вид желаемой диаграммы – ОБЪЕМНАЯ КРУГОВАЯ;
      
   3. 
      измените название диаграммы – ОТГРУЗКА В РУБ. ПО ДОГОВОРАМ.
      

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

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

---