Пример 4. Именованные массивы

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

Пусть нам известна сумма прихода и сумма расхода. Необходимо вычислить доход как разность этих величин. Введите исходные данные (рис.93).

Рис.93. Исходные данные

В ячейку D2 можно набрать формулу: =В2-С2, а затем скопировать ее в D3: D5. В этих ячейках появятся формулы =В3-С3 и т.д.

Однако здесь фактически из вектор-столбца В2: В5 вычитается вектор-столбец С2: С5. Поэтому можно непосредственно вычесть из вектора вектор одной формулой, а не создавать отдельные формулы для компонент вектора.

Создание имен.Для наглядности дадим векторам имена. Выделите диапазон со вторым (В2: В5) и третьим (С2: С5) столбцами таблицы последовательно и дайте команду во вкладке Формулы ð Присвоить Имя. Диапазону В2: В5 присвойте имя Приход, а диапазону С2: С5 - имя Расход.

Ввод табличной формулы с использованием имен диапазонов.

Как было показано в примере 1, вводим в диапазон D1: D5 формулу массива,

· Выделим блок D2: D5. В этом блоке активна ячейка D2.

· Наберем знак равенства =.

· Нажмем функциональную клавишу F3. Появится диалоговое окно Вставка имени. Выберем имя Приход и щелкнем Оk. Формула примет вид: =Приход.

· Наберем знак минус (-).

· Вновь нажмем клавишу F3. В диалоговом окне Вставка имени выберем имя Расход и щелкнем Ok. Формула примет вид: =Приход-Расход.

· Нажмем сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=Приход-Расход}.

Прокомментируем шаги. На третьем и пятом шаге мы выбирали имя из списка имен. Можно было ввести имя непосредственно с клавиатуры, но предложенный метод проще, и нет риска ошибиться в наборе имени.

Ввод формулы массива. Разумеется, формулу массива можно вводить и без использования имен. Скопируйте блок А1: С5 в А8: С12. Повторите все шаги. Выделите блок D9: D12. В этом блоке активной ячейкой является D12. Наберите знак равенства =. Выделите блок В9: В12, наберите знак минус -, выделите блок С9: С12, нажмите сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=В9: В12-С9: С12}. Мы получили две идентичные таблицы.

Рис.94. Сообщение

Изменение формулы массива.Попытайтесь очистить одну из ячеек, занятую формулой. Например, выделите ячейку D9 и нажмите клавишу Del. В этом случае должно появится сообщение «Нельзя изменять часть массива» (рис.94). Удалить блок можно только целиком.

Чтобы отредактировать формулу массива, выделите все ячейки массива и активизируйте строку формул (щелкните на ней или нажмите клавишу F2). При редактировании формулы Ехсе1 удаляет фигурные скобки. Закончив редактирование формулы, нажмите комбинацию клавиш Shift+Ctrl+Enter, чтобы внести изменения. Теперь содержимое всех ячеек массива изменится в соответствии с внесенными изменениями. (Попробуйте, например, ввести формулу {=Приход-Расход-1}, потом отмените это.)

Расширение и сокращение диапазона, содержащего формулу массива

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

1. Выделите весь диапазон, содержащий формулу массива.

2. Нажмите клавишу F2, чтобы перейти в режим редактирования.

3. Нажмите комбинацию клавиш Сtrl+Еnter. Это действие вводит отдельные формулы (не формулу массива) в каждую ячейку выделенного диапазона.

4. Измените выделение диапазона так, чтобы он включал новые ячейки (или исключал лишние).

5. Нажмите клавишу F2.

6. Нажмите комбинацию клавиш Shift+Ctrl+Enter.

Коррекция формулы при увеличении блока.Добавьте в обе таблицы на рабочем листе строку с данными: Год - 1996, приход - 240, расход – 200. Необходимо посчитать прибыль за 1996. Раньше, когда формулы записывались в отдельные ячейки, мы бы поступили просто: скопировали бы формулу из ячейки D5 в D6. Проделаем это для первой таблицы. Вместо ожидаемого 40 получим результат #ЗНАЧ!. Та же операция для второй таблицы даст правильный результат 40, но в строке формул мы увидим {=В13: В16-С13: С16} - образовался второй блок, что не является корректным решением нашей задачи. Отменим последнее действие.

Для первого и второго случая подходы к решению будут разные. Для первого случая необходимо изменить именованные блоки.

· Во вкладке Формулы ð Диспетчер имен выделим Приход, и внизу диалогового окна в окне Диапазон изменим диапазон с В1: С5 на В1: С6. Для изменения диапазона поставьте курсор в окно Диапазон и, перейдя на лист, выделите новый диапазон В1: С6. Точно также изменяем диапазон для Расхода. После изменений диалоговое окно закрываем.

· Выделяем D2: D6, нажимаем клавишу F2 (редактирование) и, ничего не изменяя в формуле, нажимаем клавиши Shift+ Ctrl+Enter.

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

· Выделяем D8: D13, нажимаем клавишу F2 для редактирования формулы.

· Изменим в формуле ссылки на диапазоны ячеек с В8: В12 на В8: В13 и с С8: С12 на С8: С13. Нажимаем сочетание клавиш Shift+Ctrl+Enter.

Коррекция табличной формулы при уменьшении блока.Теперь мы хотим удалить в каждой из таблиц строку для 1996 г. Для первой таблицы вновь изменяем поименованные блоки (в результате в ячейке D6 результат отображается как #Н/Д — недоступно). Выделяем блок с формулой массива, нажимаем клавишу F2. Ввод заканчиваем клавишами Ctrl+Enter.

Если вы нажимаете комбинацию клавиш Ctrl+Enter, то формула будет скопирована во все ячейки выделенного диапазона, но не будет формулой массива.

Очищаем последнюю строку таблицы. Выделяем блок D2: D5, нажимаем клавишу F2, нажимаем клавиши Shift+Ctrl+Enter.

Аналогично поступаем со второй таблицей:

· Выделяете диапазон с формулой массива.

· Нажимаете комбинацию клавиш Ctrl+Enter, чтобы преобразовать формулу массива в обычную.

· Удаляете последнюю строчку таблицы.

· Выделяете диапазон, в которую будете вставлять формулу массива, D9: D12.

· Нажимаете F2 для ее редактирования. Используемые в формуле ссылки на диапазоны выделяются рамочками, закрашенными в разные цвета. Видно, что рамочки захватывают области удаленной строки. Для корректировки ссылок в формуле можно уменьшить рамочки, схватив за любой нижний угол и подтянув к верху до последней используемой строки. Ссылки в формуле изменяться в соответствии с выделением. Но можно просто изменить в ссылках цифру 3 на 2 (В13 изменить на В12 и С13 на С12).

·

Рис.95. Рабочий лист расчета

Пример 5. Создание массивов на основе значений ячеек диапазона

На рис.95 показан рабочий лист, содержащий данные в диапазоне А1: С4. Диапазон С8: F11 содержит массив, созданный на основе этих данных с помощью формулы {=А1: С4}

Массив в диапазоне С8: F11 связан со значениями диапазона А1: С4. Если изменить какое-либо значение в последнем диапазоне, то автоматически изменится соответству­ющее значение в массиве.

Рис.96. Исходные данные

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП это делается на раз.

Допустим, имеем следующий двумерный массив ячеек (рис.96), который хотим транспонировать:

Рис.97. Выделение диапазона

Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов (рис.97).

· вводим функцию транспонирования =ТРАНСП(

· в качестве аргумента функции выделяем наш массив ячеек A1: B8 жмем Ctrl+Shift+Enter и получаем " перевернутый массив" (рис.98) в качестве результата:

Рис.98. Результат вычисления

Пример 7. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике... На обороте тетради на обложке было что? Таблица умножения вот такого вида (рис.99):

Рис.99. Таблица умножения

· введем два диапазона чисел от 1 до 10 в строке 1 и столбце А;

· выделяем диапазон B2: K11;

· вводим формулу =A2: A11*B1: K1;

· нажимаем Ctrl+Shift+Enter, чтобы Excel воспринял ее как формулу массива.

Результат представлен ниже.

Рис.100. Ввод формулы Рис.101. Результат вычисления

Пример 8. Выборочное суммирование

Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:

В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3: C21 и B3: B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом, суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы (рис.102).

Можно применить и другой способ:

={СУММ(ЕСЛИ(G8=C3: C21; ЕСЛИ(G9=B3: B21; D3: D21)))}

Выбираем функцию СУММ, в открывшемся диалогом окне ставим курсор в окне Число 1, выбираем в окне формул на активном листе функцию ЕСЛИ, в диалоговом окне ЕСЛИ вводим первое условие (С3: С21=G4), в окне ЕСЛИ_ИСТИНА вводим снова функцию ЕСЛИ, вводим второе условие (B3: B21=G5), в окне ЕСЛИ_ИСТИНА вводим диапазон для вычисления суммы D3: D21, в окне ЕСЛИ_ЛОЖЬ ставим 0, после этого в строке формул щелкаем по первому условию (ЕСЛИ) и в открывшемся окне при значении ЕСЛИ_ЛОЖЬ ставим 0. Также для решения этой задачи можно применить функцию СУММЕСЛИМН, которая позволяет суммировать при выполнении нескольких критериев. Попробуйте решить эту задачу с использованием этой функции.

Рис.102. Результат вычислений

Вернемся к задаче обработки данных метеостанции (Задача 5, 5). Для расчета количества засушливых месяцев, т.е. месяцев, когда выпало менее 10 мм осадков, очень удобно использовать функцию СЧЕТЕСЛИ. Однако с ее помощью нельзя получить количество месяцев, на протяжении которых количество осадков лежало бы в диапазоне от 20 до 80 (назовем такие месяцы нормальными). Для этого необходимо использовать дистрибутивные функции. Функции, которые можно применять к списку, называются дистрибутивными.

Скопируйте текст из ячейки А22 в ячейку А25 и откорректируйте его: «Количество нормальных месяцев». Сначала подсчитаем месяцев с нормальным количеством осадков. Будем использовать вспомогательный блок F3: H14 тех же размеров, что и блок сисходными данными. В ячейку F3 вводим формулу =ЕСЛИ(И(В3> 20; В3< 80); 1; 0) и копируем ее в остальные ячейки блока F3: H14. В блоке выводятся нули и единицы.

Введенная формула является индикаторной функцией множества нормальных месяцев, т.е. 1 – выводится, когда количество осадков лежит в пределах между 20 и 80 мм и 0 – в противном случае. Остается подсчитать сумму таких месяцев. Введем в ячейку F25 формулу =СУММ(F3: F14) и скопируем ее в блок G25: H25. Количество нормальных месяцев посчитано.

А теперь решим эту же задачу без использования вспомогательного блока. Введем в В25 табличную формулу {=СУММ(ЕСЛИ(В4: В15> 20; ЕСЛИ(В4: В15< 80; 1; 0); 0))} и скопируем ее в C25: D25. (Таким образом, машина, перебирая значения в указанном блоке, проверяет больше или меньше текущее число 20 и, если оно меньше, прибавляет к исходному число 0. В противном случае – проверяет выполнение второго условия (менее 80). Если и второе условие выполняется, то к исходному числу прибавляет 1. В противном случае – 0). Формула {=СУММ(ЕСЛИ(И(В4> 20; В4< 80); 1; 0))} не приведет к успеху, так как функция И не является дистрибутивной.

Этот пример очень важен. Он дает рецепт, как подсчитать в множестве количество элементов, удовлетворяющих определенному критерию. Нужно составить на основе вложенных функций ЕСЛИ индикаторную функцию подмножества и применить к ней итоговую функцию СУММ, введя формулу как табличную.

Рис.103. Результат вычислений

Теперь вычислим суммарное количество осадков, выпавших и эти месяцы Введите в А26 текст «Осадки в нормальные месяцы», в В26 – табличную формулу {=СУММ(ЕСЛИ(В3: В14> 20; ЕСЛИ (В3: В14< 80; В3: В14; 0); 0))} и скопируйте ее в C26: D26. В E25 и Е26 введите формулы для суммирования значений в строках (выделите блок В25: Е26 и щелкните кнопку «Автосумма»). Вы получите блок, показанный на рис.103.

Пример 10.

В блоке А1: А10 записана числовая последовательность. Проверьте, является ли она возрастающей.

Решение. Перейдите на новый лист. Запишите любую последовательность чисел в блок А1: А10.

Окончательное решение можно записать одной формулой (поместите его в ячейку A12).

{=ЕСЛИ(СУММ(ЕСЛИ(А2: А10-А1: А9> 0; 1; 0))=СЧЕТ(А1: А10)-1; " возрастающая"; " не является возрастающей")}

Разберем теперь эту формулу:

ü А2: А10-А1: А9 (т.е., из А10 вычитается А9, из А9 вычитается А8 и т.д.) – образует блок, состоящий из первых разностей элементов исходного блока;

ü ЕСЛИ(А2: А10-А1: А9> 0; 1; 0) – составляет блок из индикаторов положительных первых разностей;

ü СУММ(ЕСЛИ(А2: А10-А1: А9> 0; 1; 0)) – считает количество ненулевых элементов в блоке индикаторов;

ü СЧЕТ(А1: А10)-1 – рассчитывает размер блока индикаторов, равный уменьшенному на 1 размеру исходною блока;

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

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