Как в excel увеличить значения в столбце на одну и ту же константу

Умножение столбца чисел на одно и то же число

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel для Windows Phone 10 Еще. Меньше

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

В нашем примере ниже требуется умножить все числа в столбце A на число 3 в ячейке C2. Формула =A2*C2 даст правильный результат (4500) в ячейке B2. Однако копирование формулы в последующие ячейки столбца B не будет работать, поскольку ссылка на ячейку C2 изменяется на C3, C4 и т. д. Так как в этих ячейках нет данных, результат в ячейках от B3 до B6 будет равен нулю.

Чтобы умножить все числа в столбце A на ячейку C2, добавьте символы $ в ссылку на ячейку следующим образом: $C$2, как показано в следующем примере.

Символ$ $ Excel, что ссылка на ячейку C2 является «абсолютной», поэтому при копировании формулы в другую ячейку она всегда будет ссылаться на ячейку C2. Чтобы создать формулу:

1. В ячейке B2 введите знак равенства (=).
2. Щелкните ячейку A2, чтобы добавить ее в формулу.
3. Введите символ «звездочка» (*).
4. Щелкните ячейку C2, чтобы добавить ее в формулу.
5. Введите символ $ перед C и еще один перед 2: $C$2.
6. нажмите клавишу ВВОД.

Совет: Вместо символа $ можно разместить точку вставки до или после ссылки на ячейку, которую вы хотите сделать «абсолютной», и нажать клавишу F4, которая добавляет символы $.

Теперь вернемся немного назад и рассмотрим простой способ скопировать формулу в последующие ячейки столбца после нажатия клавиши ВВОД в ячейке B2.

1. Выберите ячейку B2.
2. Дважды щелкните маленький зеленый квадрат в правом нижнем углу ячейки.

Формула автоматически копируется на последующие ячейки столбца до ячейки B6.

После копирования формулы в столбце B появляются правильные результаты.

Групповое изменение числовых значений в EXCEL

Быстро сложим / разделим/ умножим числовые значения из диапазона ячеек на заданное пользователем число. Этот подход позволяет уменьшить или увеличить разрядность чисел в выбранном диапазоне, быстро выделить НДС и пр.

В ряде случаев необходимо изменить уже содержащиеся в таблице числовые значения. Например, имеется таблица стоимости товаров, цены которых указаны в рублях (столбец B ):

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

Красивой альтернативой является использование инструмента Специальная вставка ( Главная/ Буфер обмена/ Вставить/ Специальная вставка ). Покажем как быстро разделить на 1000 весь столбец с ценами, используя инструмент Специальная вставка .

• в любой пустой ячейке вводим 1000 (например, в D2 ) и нажимаем ENTER ;
• копируем в Буфер обмена значение ячейки D2 ;
• выделяем диапазон с ценами ( В2:В10 );
• идем в меню Главная/ Буфер обмена/ Вставить/ Специальная вставка ;
• выбираем Разделить в разделе Операция ;
• нажимаем ОК .

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

Этот же подход позволяет, к примеру, убрать НДС (18%) из цен. Для этого нужно в ячейку D2 ввести =100/118 , скопировать это значение в Буфер обмена, выбрать Умножить в разделе Операция инструмента Специальная вставка . Другой пример: изменим валюту цены – переведем рубли в доллары по курсу 30 руб./долл. Для этого нужно в ячейку D 2 ввести курс (30), скопировать это значение в Буфер обмена , выбрать Разделить в разделе Операция инструмента Специальная вставка .

В приведенных выше примерах предполагалось, что ячейки с ценами не содержат формул. Если цены в столбце B являются результатом вычисления формул (например =C1*200 ), то после использования инструмента Специальная вставка , формулы будут модифицированы, например так =(C1*200)/1000 (если выбирали Разделить , а в Буфере обмена содержалось число 1000).

Как в Excel увеличить значения в столбце на одну и ту же константу?

Есть таблица в экселе. В одном столбце более 200 значений. Их всех надо увеличить на одно и то же число. Как это сделать автоматом?

Лучший ответ

Элементарно:
в следующем столбце напротив нужных цифр прописать:
=(слаживаемая ячейка) + (слагаемое)

Дальше Copy > Paste в остальные ячейки ниже.

Павел БуковГуру (2628) 13 лет назад
мне надо прибавить ОДИНАКОВОЕ для всех ячеек столбца число, а не сложить значения двух столбцов

Александр Сашка Мудрец (10764) Павел, а Вы не обратили внимание на то, что колонка С содержит цифры, значения которых больше на ОДНО и то же число, чем в колонке В, на пять то есть?

Остальные ответы

пишете в ячейке вашу константу, копируете ее в буфер обмена, выделяете ваш диапазон правая кнопочка мыши -специальная вставка — галочку около сложить (дополнительно можно и галочку около «значения» поставить, чтобы форматы остались прежними) — ок

Пётр МосковскихМастер (2202) 13 лет назад

Самое быстрое решение и без написания формул.
Задающий вопрос не говорит : нужны ему исходные данные или нет.
А если подсказать им пользоваться Shift при выделении — совсем запутаются
Полезная штука ответ на мейл , есть что вспомнить.

Сергей Мыслитель (8360) толькр во время такого выделения надо быть очень аккуратным, если дважды выделить одну и ту же ячейку, то данные задвоятся (добавление произойдет несколько раз)

Использование формул массива: рекомендации и примеры

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

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

Начиная с обновления для Microsoft 365 за сентябрь 2018 г., любая формула, которая может возвращать несколько результатов, автоматически переносит их вниз или в соседние ячейки. Это изменение поведения также сопровождается несколькими новыми функциями динамического массива. Формулы динамического массива, независимо от того, используют ли они существующие функции или функции динамического массива, необходимо вводить только в одну ячейку, а затем подтверждать, нажав ВВОД. Ранее в устаревших формулах массива сначала требовалось выбрать весь выходной диапазон, а затем подтвердить формулу с помощью CTRL+SHIFT+ВВОД. Их часто называют формулами CSE.

Формулы массива позволяют выполнять сложные задачи, например следующие:

• Быстро создавать образцы наборов данных.
• Подсчитывать числа знаков в диапазоне ячеек.
• Суммировать только те числа, которые отвечают определенным условиям, например наименьших значений в диапазоне чисел, определенном верхней и нижней границами.
• Суммировать всех n-х значения в диапазоне значений.

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

Скачивание примеров

Массивы с несколькими ячейками и одной ячейкой

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

• Формула массива с несколькими ячейками
• Здесь мы вычисляем совокупный объем продаж купе и седанов для каждого продавца, вводя =F10:F19*G10:G19 в ячейку H10. При нажатии клавиши ВВОД вы увидите, как результаты распределяются по ячейкам H10:H19. Обратите внимание, что диапазон переноса выделяется границей при выделении любой ячейки в пределах диапазона. Вы также можете заметить, что формулы в ячейках H10:H19 неактивны. Они нужны только для справки, поэтому, если вы хотите изменить формулу, вам нужно выбрать ячейку H10, в которой находится основная формула.
• Формула массива с одной ячейкойВ ячейке H20 образца книги введите или скопируйте и вставьте =СУММ(F10:F19*G10:G19), а затем нажмите ВВОД. В этом случае будет выполнено перемножение значений массива (диапазон ячеек F10–G19), а затем — при помощи функции СУММ — сложение полученных результатов. Общий итог продаж составит 1 590 000 рублей. В данном примере демонстрируется, какие широкие возможности предоставляет данный тип формул. Например, предположим, что у вас есть 1000 строк с данными. Вы можете частично или полностью сложить их значения, создав формулу массива в отдельной ячейке, а не перетаскивать формулу на 1000 строк ниже. Кроме того, обратите внимание на то, что формула с одной ячейкой в ячейке H20 полностью независима от формулы с несколькими ячейками (формула в ячейках H10–H19). Это указывает на еще одно преимущество использования формул массива — их гибкость. Можно изменить формулы в столбце H, и это не повлияет на формулу в ячейке H20. Также может быть полезно иметь независимые итоги, как это, так как это помогает проверить точность результатов.
• Ниже перечислены дополнительные преимущества, которые обеспечивает использование формул динамического массива.
  • Согласованность Если щелкнуть несколько ячеек ниже ячейки H10, вы увидите, что все они содержат одну и ту же формулу. Такая согласованность помогает обеспечить более высокую точность результатов.
  • Безопасность Компонент формулы массива с несколькими ячейками нельзя переписать. Например, щелкните ячейку H11 и нажмите клавишу DELETE. Excel не будет изменять выходные данные массива. Чтобы их изменить, необходимо выбрать левую верхнюю ячейку массива или ячейку H10.
  • Меньший размер файлов Зачастую вместо нескольких промежуточных формул можно использовать одну формулу массива. Например, в примере с продажей автомобилей для вычисления результатов в столбце E используется одна формула массива. Если бы вы прибегли к обычным формулам (таким как =F10*G10, F11*G11, F12*G12 и т. д.), вам понадобилось бы 11 разных формул для получения таких же результатов. Это не очень важно, но что, если вам необходимо суммировать тысячи строк? Тогда это может иметь большое значение.
  • Эффективность Функции массива могут быть эффективным способом создания сложных формул. Формула массива =СУММ(F10:F19*G10:G19) та же: =СУММ(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).
  • Перенос Динамические формулы массива будут автоматически перенесены в диапазон вывода. Если исходные данные хранятся в таблице Excel, тогда формулы динамического массива будут автоматически изменять размер при добавлении и удалении данных.
  • Ошибка #ПЕРЕНОС! Динамические массивы ввели Ошибка #ПЕРЕНОС!, что означает, что предполагаемый диапазон переноса по какой-либо причине заблокирован. При устранении блока формула будет автоматически перенесены.

Создание одной и двухмерных констант массива

Константы массива являются компонентами формул массива. Константы массива создаются путем ввода списка элементов с последующей вставкой фигурных скобок (< >) вокруг списка, следующим образом:

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

В описанных ниже процедурах вы потренируетесь создавать горизонтальные, вертикальные и двумерные константы. Мы покажем примеры использования функции ПОСЛЕД для автоматического создания констант массива, а также для введенных вручную констант массива.

• Создание горизонтальной константы Воспользуйтесь книгой из предыдущих примеров или создайте новую книгу. Выберете любую пустую ячейку и введите =ПОСЛЕД(1,5). Функция ПОСЛЕД создает массив из 1 строки на 5 столбцов, как = . Будет отображен следующий результат: Вы также можете ввести: или=, но обратите внимание, где вы ставите точки с запятой вместо запятых. Как видите, параметр ПОСЛЕД имеет значительные преимущества по сравнению с вводом значений констант массива вручную. В основном это экономит время, но также помогает сократить количество ошибок, возникающих при вводе вручную. Его также проще читать, особенно потому, что точки с запятой трудно отличить от разделителей запятой.

Синтаксис константы массива

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

В ячейке D9 мы ввели =ПОСЛЕД(1,5,3,1), но вы также можете ввести 3, 4, 5, 6 и 7 в ячейки A9:H9. В этом конкретном выборе числа нет ничего особенного, мы просто выбрали что-то другое, кроме 1-5.

В ячейке E11 введите =СУММ(D9:H9*ПОСЛЕДОВАТЕЛЬНОСТЬ(1,5)) или =СУММ(D9:H9*). Формулы возвращают 85.

Функция ПОСЛЕД создает эквивалент константы массива . Поскольку Excel в первую очередь выполняется операции с элементами, заключенными в скобки, далее будут использоваться значения ячеек в D9: H9 и оператор умножения (*). На этом этапе формула выполнит умножение значений в сохраненном массиве на соответствующие значения в константе. Это эквивалент следующей формулы:

=СУММ(D9*1;E9*2,F9*3,G9*4;H9*5) или =СУММ(3*1,4*2,5*3,6*4,7*5)

В последнюю очередь выполняется сложение значений при помощи функции СУММ и возвращается 85.

Чтобы избежать использования сохраненного массива и полностью сохранить операцию в памяти, замените сохраненный массив другой константой массива:

Элементы, которые можно использовать в константах массива

• Константы массива могут содержать числа, текст, логические значения (например, ИСТИНА и ЛОЖЬ) и значения ошибок, такие как #N/A. Можно использовать числа в целочисленном, десятичном или экспоненциальном формате. При добавлении текста требуется заключить его в кавычки («текст»).
• Константы массива не могут содержать дополнительные массивы, формулы или функции. Другими словами, они могут содержать только текст или числа, разделенные запятыми или точками с запятой. При вводе такой формулы, как или , выводится предупреждение. Кроме того, числовые значения не могут содержать знаки процента, знаки валюты, запятые или кавычки.

Именование констант массива

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

Выберите Формулы > Присвоить имена > Определить имена. В поле Имя введите Квартал 1. В поле Диапазон введите следующую константу (не забудьте ввести скобки вручную):

Диалоговое окно должно выглядеть следующим образом:

Нажмите кнопку ОК, выберите любую строку с тремя пустыми ячейками и введите =Квартал1.

Будет отображен следующий результат:

» />

Если вы хотите, чтобы результаты переносились по вертикали, а не по горизонтали, можно использовать =ТРАНСП(Квартал1).

Если вы хотите отобразить список из 12 месяцев, например, который вы можете использовать при создании финансового отчета, вы можете использовать функцию ПОСЛЕД в качестве основы для текущего года. Отличительной особенностью этой функции является то, что, несмотря на то, что отображается только месяц, за ним стоит допустимая дата, которую можно использовать в других вычислениях. Эти примеры можно найти на листах Константа именованного массива и Быстрый образец набора данных в книге примера.

=ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()),ПОСЛЕД(1,12),1),»ммм»)

При этом функция ДАТА используется для создания даты на основе текущего года, функция ПОСЛЕД создает константу массива от 1 до 12 с января по декабрь, затем функция ТЕКСТ преобразует формат отображения в «ммм» (январь, февраль, март и т..д.). Если вы хотите отобразить полное название месяца, например «Январь», используйте «мммм».

При использовании именованной константы в качестве формулы массива не забудьте ввести знак равенства, например, =Квартал1, а не только в Квартал1. Если не сделать этого, массив будет интерпретирован как строка текста и формула не будет работать должным образом. Кроме того, помните, что можно сочетать функции, текст и числа. Все зависит от того, насколько креативно вы хотите все сделать.

Использование констант массива

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

• Умножение каждого из элементов массива Введите =ПОСЛЕД(1,12)*2 или =*2 Вы также можете выполнить деление с помощью (/), сложить с помощью (+) и вычесть с помощью (—).
• Возведение в квадрат элементов массива Введите =ПОСЛЕД(1,12)^2 или =^2
• Поиск квадратного корня из квадрата элементов массива Введите =КОРЕНЬ(ПОСЛЕД(1,12)^2) или =КОРЕНЬ(^2)
• Транспонирование одномерной строки Введите =ТРАНСП(ПОСЛЕД(1,5)) или =ТРАНСП() Даже если была введена горизонтальная константа массива, функция ТРАНСП преобразует константу массива в столбец.
• Транспонирование одномерного столбца Введите =ТРАНСП(ПОСЛЕД(5,1)) или =ТРАНСП() Даже если была введена вертикальная константа массива, функция ТРАНСП преобразует константу массива в строку.
• Транспонирование двумерного массива Введите =ТРАНСП(ПОСЛЕД(3,4)) или =ТРАНСП() Функция ТРАНСП преобразует каждую из строк в последовательность столбцов.

Применение основных формул массива

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

• Создание массива на основе существующих значений В следующем примере объясняется, как использовать формулы массива для создания нового массива из существующего массива. Введите =ПОСЛЕД(3,6,10,10) или = Не забудьте ввести < (открывающую фигурную скобку) перед числом 10 и>(закрывающую фигурную скобку) после числа 180, так как вы создаете массив чисел. Затем введите =D9# или =D9:I11 в пустую ячейку. Появится массив ячеек 3 x 6 с одинаковыми значениями, которые отображаются в D9:D11. Знак # называется оператором диапазона переноса, и это способ ссылки Excel на весь диапазон массива вместо того, чтобы вводить его.
• Создание константы массива на основе существующих значений Вы можете получить результаты формулы перенесенного массива и преобразовать его в компоненты. Выберите ячейку D9 и нажмите клавишу F2, чтобы переключиться в режим правки. Затем нажмите клавишу F9, чтобы преобразовать ссылки на ячейки в значения, которые Excel преобразует в константу массива. При нажатии клавиши ВВОД формула =D9# теперь должна быть =.
• Подсчет знаков в диапазоне ячеек В примере ниже демонстрируется, как подсчитать число знаков в диапазоне ячеек. К ним относятся пробелы. =СУММ(LEN(C9:C13)) В данном случае функция ДЛСТР возвращает длину каждой текстовой строки в каждой из ячеек диапазона. Затем функция СУММ складывает эти значения и выводит полученный результат (66). Если вы хотите получить среднее количество символов, можно использовать: =СРЗНАЧ(ДЛСТР(C9:C13))
• Содержимое самой длинной ячейки в диапазоне C9:C13=ИНДЕКС(C9:C13;ПОИСКПОЗ(МАКС(ДЛСТР(C9:C13)),ДЛСТР(C9:C13);0);1) Эта формула работает только в том случае, если диапазон данных содержит один столбец ячеек. Рассмотрим эту формулу подробнее, начиная с элементов в скобках. Функция ДЛСТР возвращает длину каждого элемента в диапазон ячеек D2:D6. Функция МАКС вычисляет наибольшее значение среди этих элементов, соответствующее самой длинной текстовой строке, которая находится в ячейке D3. Следующие функции немного сложнее. Функция ПОИСКПОЗ вычисляет смещение (относительную позицию) ячейки, которая содержит строку текста максимальной длины. Для этого требуется три аргумента: искомое значение, массив для поиска и тип соответствия. Функция ПОИСКПОЗ выполняет поиск указанного искомого значения в массиве поиска. В данном случае искомое значение представляет собой строку максимальной длины: МАКС(ДЛСТР(C9:C13) из следующего массива: ДЛСТР(C9:C13) Аргумент типа соответствия в этом случае равен 0. Тип соответствия может иметь значение 1, 0 или -1.
  • Значение 1 возвращает наибольшее значение, которое не превышает искомое значение.
  • Значение 0 возвращает первое значение, равное искомому.
  • Значение -1 возвращает наименьшее из значений, которые превышают искомое значение или равно ему
  • Если тип соответствия не указан, предполагается, что он равен 1.

  Наконец, функция ИНДЕКС имеет следующие аргументы: массив, номер строки и номер столбца в этом массиве. Массив образуется диапазоном ячеек C9:C13, функция ПОИСКПОЗ задает адрес ячейки, а последний аргумент (1) указывает, что значение должно быть найдено в первом столбце массива.

  Если вы хотите получить содержимое из наименьшей текстовой строки, замените значение МАКС в примере выше на МИН.

  

• Поиск n наименьших значений в диапазоне В этом примере показано, как найти три наименьших значения в диапазоне ячеек, где массив образцов данных в ячейках B9:B18 был создан с помощью: =ЦЕЛОЕ(СЛМАССИВ(10,1)*100). Обратите внимание, что ФУНКЦИЯ СЛМАСИМВ — это переменная функция, поэтому вы будете получать новый набор случайных чисел при каждом вычислении в Excel. Введите =НАИМЕНЬШИЙ(B9#,ПОСЛЕД(D9), =НАИМЕНЬШИЙ(B9:B18,) В этой формуле константа массива используется для трехкратной оценки функции НАИМЕНЬШИЙ и возврата 3 наименьших членов массива, содержащихся в ячейках B9:B18, где 3 — это переменное значение в ячейке D9. Чтобы найти дополнительные значения, можно увеличить значение в функции ПОСЛЕД или добавить дополнительные аргументы в константу. С этой формулой также используются дополнительные функции, например СУММ или СРЗНАЧ. Например: =СУММ(НАИМЕНЬШИЙ(B9#;ПОСЛЕД(D9))=СРЕДНИЙ(НАИМЕНЬШИЙ(B9#;ПОСЛЕД(D9))
• Поиск n наибольших значений в диапазоне Чтобы найти наибольшие значения в диапазоне, следует заменить функцию НАИМЕНЬШИЙ функцией НАИБОЛЬШИЙ. Кроме того, в следующем примере используются функции СТРОКА и ДВССЫЛ. Введите =НАИБОЛЬШИЙ(B9#,СТРОКА(ДВССЫЛ(«1:3»))) или =НАИБОЛЬШИЙ(B9:B18,СТРОКА(ДВССЫЛ(«1:3»))) На этом этапе может быть полезно ознакомиться с функциями СТРОКА и ДВССЫЛ. При помощи функции СТРОКА можно создать массив последовательных целых чисел. Например, выберите пустое и введите: =СТРОКА(1:10) В результате выполнения этой формулы будет создан столбец, содержащий 10 последовательных целочисленных значений. Чтобы понять, где может возникнуть проблема, вставьте строку над диапазоном, содержащим формулу массива (то есть над строкой 1). Ссылки на строки будут изменены и в результате выполнения формулы теперь вы получите числа от 2 до 11. Чтобы избежать возникновения этой проблемы, следует добавить в формулу функцию ДВССЫЛ. =СТРОКА(ДВССЫЛ(«1:10»)) В функции ДВССЫЛ в качестве аргументов используются текстовые строки (именно поэтому диапазон 1:10 заключен в кавычки). Текстовые значения не изменяются при вставке строк или перемещении формулы массива. В результате при выполнении функции СТРОКА всегда будет создаваться нужный массив целочисленных значений. Вы можете так же легко использовать функцию ПОСЛЕД: =ПОСЛЕД(10) Рассмотрим формулу, которую вы использовали ранее — =НАИБОЛЬШИЙ(B9#,СТРОКА(ДВССЫЛ(«1:3»))), начиная с внутренних скобок и работая наружу: функция ДВССЫЛ возвращает набор текстовых значений, в данном случае значения от 1 до 3. Функция СТРОКА, в свою очередь, создает массив столбцов из трех ячеек. Функция НАИБОЛЬШИЙ использует значения в диапазоне ячеей B9:B18 и вычисляется три раза для каждой ссылки, возвращаемой функцией СТРОКА. Если вы хотите найти дополнительные значения, добавьте к функции ДВССЫЛ более широкий диапазон ячеек. Как и в примерах с функцией НАИМЕНЬШИЙ, эту формулу можно использовать и с другими функциями, такими как СУММ и СРЗНАЧ.

Работа с ошибками

• Суммирование диапазона, который содержит значения ошибки При попытке просуммировать диапазон, содержащий значения ошибки (например, #ЗНАЧЕН), функция СУММ в Excel не работает. или #Н/Д. В приведенном ниже примере демонстрируется, как просуммировать значения в диапазоне «Данные», который содержит ошибки:
• =СУММ(ЕСЛИ(ЕОШИБКА(Данные);»»;Данные)) Формула создает новый массив, содержащий исходные значения за вычетом любых значений ошибок. Начиная с внутренних функций и работы наружу, функция ЕОШИБКА выполняет поиск ошибок в диапазоне ячеей (Данные). Функция ЕСЛИ возвращает определенное значение, если указанное условие дает в результате значение ИСТИНА, и другое значение, если условие дает в результате значение ЛОЖЬ. В этом случае он возвращает пустые строки («») для всех значений ошибок, так как они оцениваются как ИСТИНА, и возвращает оставшиеся значения из диапазона (Данные), так как они оцениваются как ЛОЖЬ, то есть они не содержат значений ошибок. Функция СУММ затем вычисляет итог для отфильтрованного массива.
• Подсчет количества значений ошибки в диапазоне Формула в этом примере похожа на формулу предыдущего примера, однако она возвращает количество значений ошибки в диапазоне с именем «Данные» вместо исключения таких значений. =СУММ(ЕСЛИ(ЕОШИБКА(Данные);1;0)) В результате выполнения этой формулы создается массив, в котором для ячеек со значениями ошибки указывается значение 1, а для других ячеек — значение 0. Для получения аналогичного результата указанную формулу можно упростить, удалив третий аргумент функции ЕСЛИ следующим образом. =СУММ(ЕСЛИ(ЕОШИБКА(Данные);1)) Если этот аргумент не задан и ячейка не содержит значения ошибки, функция ЕСЛИ возвращает значение ЛОЖЬ. Формулу можно упростить еще больше: =СУММ(ЕСЛИ(ЕОШИБКА(Данные)*1)) Такой вариант формулы допустим, поскольку ИСТИНА*1=1 и ЛОЖЬ*1=0.

Суммирование значений с учетом условий

При выполнении определенных условий может потребоваться вложение значений.

Например, в указанной ниже формуле массива суммируются только положительные целые числа в диапазоне с именем «Продажи», который представляет ячейки E9:E24 в приведенном выше примере:

=СУММ(ЕСЛИ(Продажи>0;Продажи))

В результате выполнения функции ЕСЛИ создается массив положительных и ложных значений. Функция СУММ будет игнорировать ложные значения, поскольку 0+0=0. Используемый в этой формуле диапазон ячеек может включать любое количество строк и столбцов.

Можно также выполнить суммирование значений с учетом нескольких условий. Например, эта формула массива вычисляет значения больше 0 И меньше 2500:

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

Доступно создание формул массива с использованием условия типа ИЛИ. Например, можно суммировать значения, которые больше 0 ИЛИ меньше 2500:

В формулах массива нельзя использовать непосредственно функции И или ИЛИ, поскольку эти функции возвращают отдельный результат, ИСТИНА или ЛОЖЬ, а для функций массива требуется массив результатов. Чтобы разрешить эту проблему, воспользуйтесь логикой, показанной в предыдущей формуле. Другими словами, математические операции, такие как сложение и умножение, выполняются в отношении значений, которые отвечают условию ИЛИ или И.

В этом примере демонстрируется, как удалить нулевые значения из диапазона, чтобы затем вычислить для него среднее значение. В формуле используется диапазон данных с именем «Продажи».

=СРЗНАЧ(ЕСЛИ(Продажи<>0,Продажи))

В результате выполнения функции ЕСЛИ создается массив значений, не равных 0, которые затем передаются в функцию СРЗНАЧ.

Подсчет количества различающихся ячеек в двух диапазонах

В данной формуле массива сравниваются значения в двух диапазонах ячеек с именами МоиДанные и ДругиеДанные, в результате чего возвращается количество различающихся ячеек между этими двумя диапазонами. Если содержимое двух диапазонов идентично, формула возвращает 0. Эту формулу можно использовать только для диапазонов ячеек с одинаковым размером и одинаковой размерностью. Например, если МоиДанные — это диапазон из 3 строк и 5 столбцов, то диапазон ДругиеДанные тоже должен состоять из 3 строк и 5 столбцов:

=СУММ(ЕСЛИ(МоиДанные=ДругиеДанные;0;1))

В результате выполнения формулы создается массив, имеющий такой же размер, что и сравниваемые диапазоны. Функция ЕСЛИ заполняет массив значениями 0 и 1 (0 для ячеек с различными значениями, 1 — для ячеек с одинаковыми значениями). Затем функция СУММ возвращает сумму значений в массиве.

Эту формулу можно упростить следующим образом:

=СУММ(1*(МоиДанные<>ДругиеДанные))

Подобно формуле, подсчитывающей количество значений ошибки в диапазоне, эта формула работает благодаря тому, что ИСТИНА*1=1, а ЛОЖЬ*1=0.

Следующая формула массива возвращает номер строки максимального значения в диапазоне с именем «Данные», состоящем из одного столбца:

=МИН(ЕСЛИ(Данные=МАКС(Данные);СТРОКА(Данные);»»))

В результате выполнения функции ЕСЛИ создается новый массив, соответствующий диапазону с именем «Данные». Если соответствующая ячейка содержит максимальное значение в диапазоне, массив будет содержать номер этой строки. В обратном случае массив содержит пустую строку («»). Функция МИН использует полученный массив в качестве своего второго аргумента и возвращает наименьшее значение, соответствующее номеру строки с максимальным значением в диапазоне с именем «Данные». Если диапазон «Данные» содержит одинаковые максимальные значения, формула возвращает строку первого значения.

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

=АДРЕС(МИН(ЕСЛИ(Данные=МАКС(Данные);СТРОКА(Данные);»»));СТОЛБЕЦ(Данные))

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

Формулы массива с несколькими ячейками и одной ячейкой

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

• Формула массива с несколькими ячейками

Скопируйте всю таблицу, приведенную ниже, и вставьте ее на пустой лист книги, начиная с ячейки A1.

Тип автомобиля

Число проданных единиц

Цена за единицу

Итоги продаж

Похожие публикации:

1. Appidsvc что это за служба
2. Trojan malware 300983 susgen что это за вирус
3. Платформа для сборки пк что это
4. Сколько миллиампер в 0 25 а