Svinkovod.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как вводить массив в excel

Как вводить массив в excel

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

То, к чему мы привыкли

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

Например, формула модуля числа =ABS(A2:A3) вернёт ошибку #ЗНАЧ! , так как ожидает в параметрах увидеть только одно число, а не массив чисел.

Предположим нам надо подсчитать сумму чисел, взятых по модулю, в каком-то диапазоне ячеек. Что в таких случаях все обычно делают? Ясное дело — добавляют к таблице колонку, в которой будут вычисляться модули чисел, а потом суммируют значения этой колонки в отдельной итоговой ячейке. Это стандартный, всем понятный подход — если задача сложная, то надо разбить её на части и решить по частям.

Альтернатива

Так вот формулы массивов часто позволяют избежать промежуточных расчётов и получать результат сразу при помощи только ОДНОЙ формулы. Посмотрите на этот экран:

Вы видите буквальто то, что вызывало ошибку абзацем выше в обычной формуле! Но в том то и дело, что сейчас перед нами в ячейке D2 не обычная формула, а формула массива. Обратите внимание на характерные фигурные скобки, обрамляющие такую формулу. Только не пытайтесь вводить эти скобки с клавиатуры — Excel примет это за текстовую строку. Чтобы ввести в ячейку формулу массива, надо в конце нажимать не Enter , а Ctrl + Shift + Enter . Только в этом случае Excel вас поймёт правильно.

Как это работает?

Как же работает <=СУММ(ABS(B2:B10))>? Дело в том, что Excel вычисляет эту формулу хитро. Видя, что в параметрах стоит диапазон ячеек B2:B10 (а диапазон — есть массив), Excel вычисляет значение функции ABS для каждой ячейки диапазона (элемента массива). То есть он вычисляет ABS(B2) , затем ABS(B3) , . ABS(B10) , далее он снова объединяет это всё в единый массив и передаёт его в функцию СУММ , которая и возвращает нам итог. Всё это происходит в памяти компьютера автоматически, а мы видим уже только конечный результат, удивляясь откуда, что берется.

Читайте так же:
Как ворд перевести в jpeg

Более сложный пример

Представим, что нам с вами надо контролировать содержимое некоего диапазона. Мы хотим знать, сколько в нём числовых значений, текстовых строк, логических значений, пустых ячеек и сколько ячеек в статусе ошибка (в данном случае я воспользовался операцией деления на ноль, вызывающую ошибку #ДЕЛ/0! ). Я не знаю простых и изящных способов, как это можно было бы сделать без формул-массивов. Но зато с последними задача решается «без шума и пыли».

Тут надо кое-что пояснить:

Зачем я использую функции ЕЛОГИЧ (ISLOGICAL), ЕТЕКСТ (ISTEXT), ЕОШ (ISERR), ЕПУСТО (ISBLANK), ЕЧИСЛО (ISNUMBER), думаю, понятно — я выясняю таким образом, что в ячейке.

Непонятно, наверное, зачем я прибавляю к результатам ноль? Дело в том, что вышеуказанные функции возвращают не числовые, а логические значения (ИСТИНА/ЛОЖЬ), которые, таким образом (прибавлением ноля), конвертируются в свои числовые эквиваленты (ИСТИНА=1, ЛОЖЬ=0). В результате функция СУММ (SUM) имеет уже дело с нулями и единицами, а не с ИСТИНА и ЛОЖЬ, с которыми она бы работать не стала. Аналогичного результата можно добиться не прибавляя ноль ( +0 ), а умножая на единицу ( *1 ). Выбор за вами.

Скачать файл примера

Добавим красоты

А теперь за счёт умных таблиц я продемонстрирую, как формулы массивов изящно избавляются от абсолютных координат диапазона $A$2:$C$21 . Последний заменяется на лаконичное Таблица2 , а формулы после этого не надо менять при добавлении/удалении строк из нашего диапазона! Разве это не круто?

Дальше что?

Я вам много-чего не договорил про формулы массивов, но вы поняли суть и теперь можете пробовать двигаться самостоятельно.

Работа с массивами функций в Excel

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

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

Виды массивов функций Excel

Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:

Пример массивов.

В зависимости от расположения элементов различают массивы:

  • одномерные (данные находятся в ОДНОЙ строке или в ОДНОМ столбце);
  • двумерные (НЕСКОЛЬКО строк и столбцов, матрица).

Одномерные массивы бывают:

  • горизонтальными (данные – в строке);
  • вертикальными (данные – в столбце).

Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).

Примеры двумерных массивов.

Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.

Читайте так же:
Можно ли запустить видеокарту без дополнительного питания

С помощью формул массива реально:

  • подсчитать количество знаков в определенном диапазоне;
  • суммировать только те числа, которые соответствуют заданному условию;
  • суммировать все n-ные значения в определенном диапазоне.

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

Синтаксис формулы массива

Используем формулу массива с диапазоном ячеек и с отдельной ячейкой. В первом случае найдем промежуточные итоги для столбца «К оплате». Во втором – итоговую сумму коммунальных платежей.

  1. Выделяем диапазон Е3:Е8.
  2. В строку формул вводим следующую формулу: =C3:C8*D3:D8. Формула промежуточных итогов.
  3. Нажимаем одновременно клавиши: Ctrl + Shift + Enter. Промежуточные итоги посчитаны: Результат вычисления промежуточных итогов.

Формула после нажатия Ctrl + Shift + Enter оказалась в фигурных скобках. Она подставилась автоматически в каждую ячейку выделенного диапазона.

Если попытаться изменить данные в какой-либо ячейке столбца «К оплате» — ничего не выйдет. Формула в массиве защищает значения диапазона от изменений. На экране появляется соответствующая запись:

Ошибка.

Рассмотрим другие примеры использования функций массива Excel – рассчитаем итоговую сумму коммунальных платежей с помощью одной формулы.

  1. Выделяем ячейку Е9 (напротив «Итого»).
  2. Вводим формулу вида: =СУММ(C3:C8*D3:D8).
  3. Нажимаем сочетание клавиш: Ctrl + Shift + Enter. Результат: Итог одной формулой.

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

Аргументы для функции – одномерные массивы. Формула просматривает каждый из них по отдельности, совершает заданные пользователем операции и генерирует единый результат.

Рассмотрим ее синтаксис:

Синтаксис массива функций.

Функции работы с массивами Excel

Предположим, в следующем месяце планируется увеличение коммунальных платежей на 10%. Если мы введем обычную формулу для итога =СУММ((C3:C8*D3:D8)+10%), то вряд ли получим ожидаемый результат. Нам нужно, чтобы каждый аргумент увеличился на 10%. Чтобы программа поняла это, мы используем функцию как массив.

  1. Посмотрим, как работает оператор «И» в функции массива . Нам нужно узнать, сколько мы платим за воду, горячую и холодную. Функция: . Итого – 346 руб. Выборочный итог.
  2. Функция «Сортировки» в формуле массива. Отсортируем суммы к оплате в порядке возрастания. Для списка отсортированных данных создадим диапазон. Выделим его. В строке формул вводим . Жмем сочетание Ctrl + Shift + Enter. Сортировка итогов.
  3. Транспонированная матрица. Специальная функция Excel для работы с двумерными массивами. Функция «ТРАНСП» возвращает сразу несколько значений. Преобразует горизонтальную матрицу в вертикальную и наоборот. Выделяем диапазон ячеек, где количество строк = числу столбцов в таблице с исходными данными. А количество столбцов = числу строк в исходном массиве. Вводим формулу: . Получается «перевернутый» массив данных. Перевернутый массив данных.
  4. Поиск среднего значения без учета нулей. Если мы воспользуемся стандартной функцией «СРЗНАЧ», то получим в результате «0». И это будет правильно. Поэтому вставляем в формулу дополнительное условие: 0;A1:A8))’ >. Получаем: Условие в массиве.
Читайте так же:
Можно ли зайти в телеграм через комп

Распространенная ошибка при работе с массивами функций – НЕ нажатие кодового сочетания «Ctrl + Shift + Enter» (никогда не забывайте эту комбинацию клавиш). Это самое главное, что нужно запомнить при обработке больших объемов информации. Правильно введенная функция выполняет сложнейшие задачи.

Формула массива на Excel для Mac

Мне нужно позвонить LINEST в Excel 2011 для Mac, я бы сказал — Это была детективная история. Моя лень, мое любопытство — все было здесь! У меня было домашнее задание из университетского курса по использованию LINEST.

Я даже перестал пытаться сделать это на MAC, потому что после гугла я не мог найти никакого решения, как это сделать. (Я перепробовал все комбинации Fn Ctrl Alt Cmd Return !)

Наконец-то я нашел решение здесь, на stack overflow! Я хочу упомянуть об этом еще раз, так как это было долгое время, которое я потратил на поиски решения. Я провел 4 дня вне дома, чтобы использовать только windows PC, где это легко, как пирог- F2 , CTRL + SHIFT + ENTER .

Если вы хотите проверить, говорю ли я правду — перейдите к HELP в Excel для Mac, введите формулу массива, найдите LINEST и посмотрите на комбинацию решений!

Решение: выберите диапазон, нажмите CONTROL + U , а затем нажмите ⌘ + RETURN .

5 ответов

  • Excel формула массива с подстановочным знаком

У меня есть формула массива, которая ссылается на выпадающую ячейку ($AG$7), чтобы определить, какие ячейки следует оценивать. Это хорошо работает, однако мне нужно включить дополнительный элемент в выпадающий список, который является All. Когда этот параметр выбран, я хочу, чтобы формула массива.

Я пытаюсь решить ошибку #DIV/0! , возникающую, когда формула дает результат 0 . Я использую Excel 2011 для Mac. Вот оригинальная формула: =SUM((AX11025*1)/AV11025) А вот модифицированная формула для устранения ошибки #DIV/0 : =IFERROR((SUM(AX11025*1)/AV11025),SUM(AX425*1), 0)

Похоже, это не работает в Mac Excel 2016. После небольшого копания, похоже, что комбинация клавиш для ввода формулы массива изменилась с ⌘ + RETURN на CTRL + SHIFT + RETURN .

Выберите диапазон, нажмите CONTROL + U , а затем нажмите ⌘ + RETURN .

Найдено решение для Excel Mac2016 как необходимость вставить код в соответствующую ячейку, введите, затем перейдите к концу формулы в строке заголовка и введите следующее:

Введите формулу в виде формулы массива Изображение + SHIFT + RETURN или CONTROL + SHIFT + RETURN

CTRL+SHIFT+ENTER, ФОРМУЛА МАССИВА EXCEL 2016 MAC. Так что я прибываю в игру поздно, но, может быть, кто-то еще придет. Это почти свело меня с ума. Независимо от того, что я искал в Google, я ничего не нашел. Что бы я ни пытался сделать, никакого решения, казалось, не предвиделось. Довольно давно я переключился на Excel 2016, и сегодня мне нужно было сделать несколько формул массива. Также сидя на MacBook Pro 15 Touch Bar 2016. Не то чтобы это действительно имело значение, но все же, поскольку решение было опубликовано на Youtube в 2013 году. Причина, по которой, во всяком случае для меня, ничего не работало, заключается в Mac OS, клавиша управления по умолчанию, во всяком случае для меня, настроена на управление управлением полетом, что, по крайней мере для меня, отключило кнопку управления в Excel. Чтобы включить ключ для фактического управления функциями в Excel, вам нужно перейти в Системные настройки > управление полетом и отключить ярлыки для управления полетом. Итак, давайте посмотрим, как долго продлится это решение. Наверное, вернусь к началу после перерыва на кофе. Всего хорошего!

  1. Выберите нужный диапазон ячеек
  2. Нажмите Fn + F2 или CONTROL + U
  3. Вставьте значение массива
  4. Нажмите COMMAND (⌘) + SHIFT + RETURN
Читайте так же:
Меньше либо равно в excel
Похожие вопросы:

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

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

У меня есть файл XLS с несколькими листами с именами 1, 2, 3, 4 и т. д. Теперь мне нужна формула, которая приведет, например, к =’1′!M16, где имя листа, на который я ссылаюсь, основано на текущей.

У меня есть формула массива, которая ссылается на выпадающую ячейку ($AG$7), чтобы определить, какие ячейки следует оценивать. Это хорошо работает, однако мне нужно включить дополнительный элемент в.

Я пытаюсь решить ошибку #DIV/0! , возникающую, когда формула дает результат 0 . Я использую Excel 2011 для Mac. Вот оригинальная формула: =SUM((AX11025*1)/AV11025) А вот модифицированная формула для.

Существует ли формула в Excel, которая будет выполнять поиск массива 2-D в отдельных листах внутри одной книги? У меня есть три таблицы, отформатированные одинаково с разными данными в таблицах.

У меня есть формула массива следующим образом: Public Function fafsa(inputRange As range, desks As range) As Integer Dim vArr() As Integer Dim i As Long ReDim vArr(1 To inputRange.Cells.Count, 1 To.

Ранее я создал формулу массива Excel, такую как: X13: =SUM(IF($F13>H13:R13,1,0))+IF(F13>F9,1,0)+SUM(IF($F13>T13:U13,1,0))+IF(T13>U13,1,0) При применении этой формулы вниз по столбцу и.

Может ли существовать одна формула (возможно, формула массива), которая может указать, делится ли число в F1 на любое из чисел в ячейках (A1:D9)? С уважением.

есть ли формула в Excel, которая может преобразовать адрес MAC из формата 11:22:33:44:55:ab в 1122-3344-55ab ? Спасибо!

Читайте так же:
Как в фотошопе написать текст под наклоном

Как в Excel ввести формулу массива?

=ТРАНСП() — для того чтобы вертикальный диапазон ячеек сделать горизонтальным и наоборот.

Из данной статьи вы узнаете, как в Excel ввести формулу массива.

Принцип ввода формулы массива расскажу на примере 2-х формул =ЛИНЕЙН() и =ТРАНСП().

Для того, чтобы с помощью формулы =ЛИНЕЙН() рассчитать коэффициенты линейного тренда y=a+bx (a) и (b), необходимо:

1. Ввести в формулу данные =ЛИНЕЙН(известные значения y (например, объём продаж по месяцам), известные значения x (номера периодов), константа (коэффициент (a) в формуле y=a+bx, для его расчета ставим «1»), статистика (вводим «0»)) (см. файл с примером).

линейн

2. Установить курсор в ячейку с формулой и выделить соседнюю справа, как на рисунке:

формула линейн

3. Для ввода формулы массива нажимаем клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

формула массива Линейн

Коэффициенты линейного тренда y=a+bx (a) и (b) рассчитаны.

2-й пример (см. вложенный файл), в нём мы рассмотрим, как перевернуть диапазон и сделать из горизонтального вертикальный. Для этого воспользуемся функцией =ТРАНСП().

Как она работает:

1. В формулу вводим горизонтальный диапазон, который хотим сделать вертикальным:

трансп

2. Выделяем вертикальный диапазон, равный по количеству ячеек выделенному горизонтальному, вверху диапазона должна быть введена формула =ТРАНСП();

формула массива трансп

3. Для ввода формулы массива нажимаем клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

формула массива трансп

Горизонтальный диапазон стал вертикальным. Теперь, если мы внёсем изменения в горизонтальный диапазон, они тут же отобразятся в вертикальном диапазоне.

Для ввода формулы массива необходимо

  1. выделить массив — это диапазон ячеек, в которые Excel выведет данные,
  2. и нажать чудо комбинацию клавиш — F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

Всё просто, попробуйте, и если остались вопросы, задавайте их в комментариях.

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector