Консолидация при помощи команды Специальная вставка

Связи консолидированных данных и исходных данных уст. если они помещены на разных листах.

Таблица 29.1. Функции консолидации Excel

Функция	Описание
Сумма (Sum)	Вычисление суммы значений исходных ячеек
Количество значений (Count)	Вычисление количества непустых исходных ячеек
Среднее (Average)	Вычисление среднего значения исходных ячеек
Максимум (Мах)	Определение максимального среди значений исходных ячеек
Минимум (Min)	Определение минимального среди значений исходных ячеек
Произведение (Product)	Вычисление произведения значений исходных ячеек
Количество чисел {Count Nums)	Вычисление количества исходных ячеек, содержащих числа
Смещенное отклонение (StdDev)	Оценка стандартного отклонения генеральной совокупности в предположении, что в исходных ячейках находится только выборка из генеральной совокупности
Несмещенное отклонение (StdDevp)	Вычисление стандартного отклонения генеральной совокупности, которая находится в исходных ячейках
Смещенная дисперсия (Var)	Оценка дисперсии генеральной совокупности по выборке
Несмещенная дисперсия (Varp)	Вычисление дисперсии генеральной совокупности

Исходные диапазоны могут быть разных размеров. В этом случае одни ячейки будут иметь больше исходных ячеек, чем другие. Например, если указаны два исходных диапазона B2:D4 и B6:Е8, то диапазон консолидации будет содержать три строки и четыре столбца. Значения первых трех ячеек каждой строки будут сформированы на основе данных из обоих диапазонов, значения последних ячеек каждой строки будут сформированы на основе ячеек столбца E второго диапазона.

Простую консолидацию можно провести без использования команды Консолидация (Consolidate) или формул со ссылками. Если необходимо посчитать сумму, разность, произведение или частное каждой пары ячеек из двух диапазонов, используйте команду меню Правка | Специальная вставка (Edit Paste Special).

Скопируйте один из исходных диапазонов в буфер обмена. Для этого выделите его и выполните команду меню Правка | Копировать (Edit Copy).

Далее:

1. Выделите второй диапазон.

2. Выполните команду Правка | Специальная вставка (Edit Paste Special). Откроется диалоговое окно Специальная вставка (Paste Special).

3. Установите переключатель Вставить (Paste) в положение значения (Values).

4. Установите переключатель Операция (Operation) в положение, соответствующее нужной операции: сложить (Add), вычесть (Subtract), умножить (Multiply) или разделить (Divide).

5. Если необходимо, чтобы пустые ячейки не участвовали в консолидации, установите флажок пропускать пустые ячейки (Slap blanks).

6. Если вставляемый диапазон должен быть транспонирован при вставке, установите флажок транспонировать (Transpose). В результате транспонирования диапазона его строки и столбцы меняются местами: первая строка становится первым столбцом, вторая строка — вторым столбцом и т. д. Например, на рис. 29.17 диапазон справа был получен путем транспонирования диапазона, выделенного пунктиром.

7. После того как все установки сделаны, нажмите кнопку ОК. На месте второго диапазона появятся консолидированные данные.

Необходимо учитывать, что при использовании команды Специальная вставка

(Paste Special) содержимое второго исходного диапазона теряется — оно заменяется на результат. Если необходимо, чтобы содержимое обоих исходных диапазонов сохранилось, используйте другие способы консолидации.

С помощью команды Специальная вставка (Paste Special) можно консолидировать не только значения ячеек, но и формулы, которые хранятся в ячейках. Для этого в окне Специальная вставка (Paste Special) установите переключатель Вставить (Paste) в положение формулы (Formulas). В результате после вставки формулы в каждой из ячеек будут заменены на сумму, разность, произведение или частное формул, содержащихся в исходных ячейках. Например, если одна ячейка содержит формулу =В2^{^}2, а другая — формулу =корень(В2), то в результате получится формула ^В2^Л2+КОРЕНЬ(В2) (если переключатель Операция (Operation) в окне Специальная вставка (Paste Special) установлен в положение сложить (Add)).

Дата добавления: 2014-01-15