КОНСОЛИДАЦИЯ ДАННЫХ

Консолидация - это объединение данных из одной или нескольких таблиц, однотипных по структуре, с одновременным выполнением действий над данными.

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

Для выполнения консолидации в Excel имеются два способа:

  • 1) с помощью команды Консолидация;
  • 2) с помощью команды Сводная таблица.

Условиями для консолидации являются:

  • • одинаковые имена полей;
  • • одинаковый тип данных в левом столбце таблицы.

Консолидация с помощью команды «Консолидация» из меню «Данные»

  • 1. Выбрать меню Данные - Консолидация.
  • 2. В диалоговом окне (рис. 4.1) в поле Функция выберите функцию для подведения итогов.
  • 3. В диалоговом окне (см. рис. 4.1) в поле Ссылка укажите диапазоны данных для консолидации и с помощью кнопки [Добавить] добавьте выбранные диапазоны в Список диапазонов.
  • 4. Укажите имена полей и заголовки строк и создайте связи с исходными данными.

Консолидация с помощью сводных таблиц

  • 1. Выбрать меню Данные - Сводная таблица.
  • 2. В диалоговом окне Мастер сводных таблиц и диаграмм -шаг 1 из 3 выбрать В нескольких диапазонах консолидации -кнопка [Далее] (рис. 4.2).

Рис. 4.1

3. В диалоговом окне Мастер сводных таблиц и диаграмм шаг 2а из 3 (рис. 4.3) выбрать Создать поля страницы - [Далед.

Мастер сводных таблиц и диаграмм - шаг 2а из 3

Сводную таблицу можно создать на основе диапазонов из одного или нескольких листов. Каждый диапазон должен иметь подписи строк и столбцов; данные с одинаковыми названиями будут просуммированы.

Укажите, как следует создавать поля страницы, затем нажмите кнопку "Далее >".

С Создать одно поле страницы ^Создать поля страницы]

Отмена

< Назад

Далее >

Готово

Рис. 4.3

4. Добавить диапазон данных и задать имена полей (рис. 4.4).

Мастер сводных таблиц и диаграмм - шаг 26 из 3

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

Диапазон:

|,2006*!$A$4:$G$12 51

Удалить | Обзор...

Добавить |

Список диапазонов:

,20Q6,!$A$4:$G$12

’2OO7’!$A$4:$G$12

’2ОО8’!$А$4:$С$12

Во-первых, укажите количество полей страницы сводной таблицы.

2 <3

Второе поле:

Го

Первое поле:

Затем выберите диапазон в списке и

|2006

d 1

укажите метку элемента в каждом из доступных окон полей. Повторите

Третье поле:

Четвертое поле:

операцию для каждого диапазона.

Отмена

< Назад

Далее >

Готово

5. Указать место размещения таблицы, |Готово| (рис. 4.5).

Рис. 4.5

6. Переименовать поля сводной таблицы (по необходимости). Результатом консолидации будет сводная таблица (рис. 4.6).

?

4 Годы |(Все) ?

5

6 Сумма по полю Значение

Месяц ?

7 Фирма ?»

Январь

Февраль

Март

Апрель

Май Июнь

Общий итог

8

БизнесБук

348,6

3582

302,4

661,8

1051,2 366,9

3039,1

9

Информ 77

279,6

310,8

533,4

656,1

561,6 725,7

3067,2

10

Источник

351,9

335,7

419,7

849

597,3 841,5

3395,1

11

М-ПРЕСС

397,8

462,3

575,7

739,8

703,2 899,4

3778,2

12

Неваслог

183

498,9

571,5

720,6

236,7 749,1

2959,8

13 ОББО

317,7

387,9

653,4

501

1013,4 872,1

3745,5

14 Римм

304,5

3042

501,3

705,9

452,1 791,1

3059,1

15 Смола

233,4

2715

650,1

439,8

982,5 964,2

3541,5

16 Общий итог

2416,5

2929,5

4207,5

5274

5598 6210

26635,5

Рис. 4.6

Сводная таблица может размещаться на текущем листе или на новом.

Упражнения

Упражнение 4.1

I. Создайте новую книгу Excel и переименуйте листы на Январь и Февраль. На этих рабочих листах создайте два списка (табл. 4.1. 4.2).

Таблица 4.1

Дата

Название тура

Стоимость тура, у. е.

Комиссия, у. е. (И %)

Себестоимость, у. е.

17.01.

«Париж экскурсионный»

500

10.01.

«Горные лыжи в Болгарии»

400

14.01.

Турция

300

25.01.

«Великолепная неделя в Праге»

800

15.01.

«Австрийские Альпы»

600

15.01.

Турция

400

16.01.

Турция

450

Таблица 4.2

Дата

Название тура

Стоимость тура, у. е.

Комиссия, у. е. (11 %)

Себестоимость, У- е.

05.02.

«Париж экскурсионный»

600

10.02.

«Горные лыжи в Болгарии»

300

12.02.

Турция

450

15.02.

«Великолепная неделя в Праге»

750

17.02.

«Австрийские Альпы»

500

20.02.

Турция

450

25.02.

Анталья

500

2. Рассчитайте значения в поле Комиссия, у. е., вычислив их по формуле

Стоимость тура, у. с.*11%,

в поле Себестоимость, у. е. - по формуле

Стоимость тура, у. е. - Комиссия, у. е.

3. Создайте на основе этих таблиц консолидированную сводную таблицу (рис. 4.7).

31

32

Месяц (февраль ?

::

34

Сумма по полю Значение

Денежные суммы ?

35

Дата

Стоимость тура, у.е.

Себестоимость, у.е

Комиссия (11%)

Общий итог

36

02.5.05

600

534

66

1200

37

02 10.05

300

267

33

600

38

0212 05

450

400,5

49,5

900

39

02.15.05

750

667,5

82,5

1500

40

02 17.05

500 445

55

1000

41

02.20.05

450 400,5

49,5

900

42

02.25.05

500 445

55

1000

43

Общий итог

3550 3159,5

390,5

7100

Рис. 4.7

— •

32

Месяц |(Все) ?

33

34

Денежные суммы Н Дата Я Значение

35

Стоимость тура, у.е.

36

02.5.05

600

37

02.10.05

300

38

Стоимость тура, у.е. И jor

900

39

40

Себестоимость, у.е.

41

02.5.05

534

42

02.10.05

267

43

Себестоимость, у.е. Итог

801

44

45

Комиссия (11%)

46

02.5.05

66

47

02.10.05

33

48

Комиссия (11%) Итог

99

49

50

Общий итог

1800

Рис. 4.8

  • 4. Создайте поля страниц (Январь привязать к диапазону данных за январь и Февраль - к диапазону данных за февраль).
  • 5. Расположите сводную таблицу на новом рабочем листе.
  • 6. Измените заголовки областей в сводной таблице:
    • Страница! - на Месяц;
    • Строка - на Дата;
    • Столбец - на Денежные суммы.
  • 7. Уберите из поля Денежные суммы поле Название тура.
  • 8. Примените один из видов Автоформата к созданной сводной таблице (рис. 4.8).

Упражнение 4.2

  • 1. Создайте новую рабочую книгу, состоящую из трех рабочих листов.
  • 2. Переименуйте листы: Лист! - в Перечисления в январе, Лист2 - в Перечисления в феврале, ЛистЗ - в Сводка по перечислениям.
  • 3. На листах Перечисления в январе и Перечисления в феврале введите соответственно две таблицы с данными (табл. 4.3, 4.4).

Таблица 4.3

Дата

Сумма, руб.

Номер платежного поручения

04.01.

12500

80

07.01.

25000

83

08.01.

30000

87

15.01.

45000

91

16.01.

27000

95

20.01.

25000

97

24.01.

18500

102

27.01.

15500

108

Таблица 4.4

Дата

Сумма, руб.

Номер платежного поручения

03.02.

15500

но

07.02.

16500

112

10.02.

18000

118

15.02.

32000

120

16.02.

30500

121

20.02.

35000

124

22.02.

27000

125

27.02.

20500

130

4. Создайте на основе этих данных сводную консолидированную таблицу (рис. 4.9) на листе Сводка по перечислениям:

А

в

С

34

Месяц

(Все) ?

35

36

Сумма по полю Значение

Перечисленная сумма ?

37

Дата

Сумма, руб.

Общий итог

38

01.4.05

12500

12500

39

01.7.05

25000

25000

40

01.8.05

30000

30000

41

01.15.05

45000

45000

42

01.16.05

27000

27000

43

01.20.05

25000

25000

44

01.24.05

18500

18500

45

01.27.05

15500

15500

46

02.3.05

15500

15500

47

02.7.05

16500

16500

48

02.10.05

18000

18000

49

02.15.05

32000

32000

50

02.16.05

30500

30500

51

02.20.05

35000

35000

52

02.22.05

27000

27000

53

02.27.05

20500

20500

54

Общий итог

393500

393500

н < ? н сводка попеиечислениям / Февоаль2 / янваоь2 / Диагоаг

Рис. 4.9

  • • в Мастере сводных таблиц установите одно поле страницы;
  • Январь привязать к диапазону данных за январь;
  • Февраль - к диапазону данных за февраль.
  • 5. Измените заголовки областей в таблице:
    • Страница! - на Месяц;
    • Строка - на Дата;
    • Столбец - на Перечисленная сумма.
  • 6. Уберите из поля Перечисленная сумма поле Номер платежного поручения.
  • 7. Примените один из видов Автоформата для сводной таблицы.
 
Посмотреть оригинал
< Пред   СОДЕРЖАНИЕ ОРИГИНАЛ   След >