Mini-ats102.ru

ООО “Мультилайн”
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция INDEX (ИНДЕКС) в Excel. Как использовать

Функция INDEX (ИНДЕКС) в Excel. Как использовать?

Функция INDEX (ИНДЕКС) в Excel используется для получения данных из таблицы, при условии что вы знаете номер строки и столбца, в котором эти данные находятся.

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

Функция INDEX в Excel - 1

Excel works!

menu

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

Формула массива Excel

Какие преимущества можно получить при использовании формул массива?

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

Итак, начнем с самих массивов. Объясняя простым языком, массив представляет собой набор данных, обрабатываемых в Excel одним параметром (аргументом).

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

Пример 1. Формула массива Excel

Имеются два диапазона чисел. Нам требуется получить сумму произведений диапазонов. Обычный пример можно реализовать так: добавляется дополнительный, третий по счету столбец (либо строчка), после чего получается произведение и делается суммирование всего столбца.

Формула массива Excel

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

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

В завершении нажимаем одновременно клавиши в следующей комбинации: CTRL + Shift + Enter

Нажатие этого сочетания и означает запуск Формулы массива! Конечная формула будет заключена в фигурные скобки <>

Нюанс. Имеется специальная функция, =СУММПРОИЗВ(), предназначенная для получения суммы сразу всех произведений в массиве.

Пример №2. Практическое применение

Требуется определить сумму 1-го, 3-го и 10-го наибольших элементов из набора данных (диапазона B1:B10). Не прибегая к ФМ это делается так: сортируем по убыванию таблицу, воспользуемся дополнительным столбцом, проставляем в нем порядковые номера и извлекаем сумму при помощи функции =СУММЕСЛИ() .

Но формулой массива все делается проще:

1. Здесь функция =НАИБОЛЬШИЙ() осуществляет поиск по убыванию элементов, которые были заданы (в нашем случае это 10, 8 и 1) в диапазоне данных B1:B10

2. Суммирование данного массива происходит с использованием известной функции =СУММ().

3. CTRL + Shift + Enter, не забываем

И напоследок еще раз обозначу условия использования формул массива

— завершаем ввод формулы комбинацией клавиш вида Ctrl + Shift + Enter ;

— если произведен ввод формулы массива, нельзя вносить изменения в ячейки диапазона по отдельности. Делать это можно только одновременно со всеми ячейками, иначе получим уведомление вида «Нельзя изменить эту часть массива». Для примера с транспонирование формулой

Excel works!

Excel работает за вас

  • Залогиниться
  • Зарегистрироваться

    logo

Excel works!

  • Главная
  • Excel
  • Управление проектами
  • Книги
  • Прочее
  • Аналитика

Thanks for Visiting

  • Главная
  • Excel
  • Управление проектами
  • Книги
  • Прочее
  • Аналитика

Нельзя изменять часть массива. Ошибка Excel

Встречали такую ошибку «Нельзя изменять часть массива»? Мне вот за последнее время несколько раз попадался файл, в котором нельзя было удалить определенные строки или столбцы, взамен выползает ошибка как на рисунке

Нельзя изменить часть массива

Нельзя изменять часть массива — уберите все объединение ячеек в файле

Как мы уже не раз обсуждали, объединение ячеек — это локальное зло Excel. Если вам нужно сделать красивое оформление текста, выровнять его по середине, то можно воспользоваться специальной возможностью Excel.

Читайте так же:
Можно ли вводить номер карты в интернете

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

Нельзя изменить часть массива 2

Рекомендую выбирать именно эту кнопку, а не выделять вручную, т.к. возможно у вас есть объединения в невидимой части таблицы.

Затем переходим Главная — блок Выравнивание — Кнопку объединить ячейки, если она активна как на картинке, значит объединения есть — жмем на нее, должно помочь.

Нельзя изменить часть массива 3

2. Используется формула массива

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

Включаем в Меню формулы видимость всех формул в модуле зависимости формул

Нельзя изменить часть массива 4

И ищите, где формулы помещены в фигурные скобки <>. Как например:

Нельзя изменить часть массива 5

3. Ячейка ссылает на группу ячеек с формулой массива

Скорее всего это глюк Excel 2007 и версий ниже. Т.е. есть формула массива, она корректно работает и так далее. Но вы ссылаетесь на 2 и более ячеек из этого массива, чтобы посчитать например сумму.

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

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

Я же могу предложить поискать самим такое условие или, если файл огромный, перенести данные на новый чистый лист как значение — и перезаписать формулы заново.

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

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

Обычно вы можете легко узнать значение с помощью Найдите функция в Excel. Но что, если найти значение по критериям? А с двумя или более критериями? Эта статья представит вам несколько решений.

  • Найти значение с двумя или несколькими критериями с помощью формулы массива
  • Найдите значение по двум или нескольким критериям с помощью расширенного фильтра

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

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

Формула массива 1: найти значение с двумя или несколькими критериями в Excel

Основное выражение этой формулы массива отображается следующим образом:

Допустим, вы хотите узнать объем продаж манго происходит на 9/3/2019, вы можете ввести приведенную ниже формулу массива в пустую ячейку и затем нажать Ctrl + Shift + Enter вместе.

Внимание: В приведенной выше формуле F3: F22 — это столбец суммы, в котором вы найдете значение, B3: B22 — столбец даты, C3: C22 — столбец фруктов, J3 — дата, указанная в качестве первого критерия, J4 — это имя фрукта, указанное как второй критерий.

В соответствии с выражением формулы массива вы можете легко добавить необходимые критерии. Например, теперь вы ищете сумму продаж манго происходит на 9/3/2019, а вес манго 211, вы можете добавить критерии и lookup_array в разделе MATCH следующим образом:

Читайте так же:
Выпадающий список в excel из другого листа

=INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22)*(J5 = E3: E22), 0))

И нажмите Ctrl + Shift + Enter ключи, чтобы узнать сумму продажи.

Формула массива 2: найти значение с двумя или несколькими критериями в Excel

Основное выражение этой формулы массива отображается следующим образом:

= ИНДЕКС (массив; ПОИСКПОЗ (критерий1 и критерий2… & критерийN, поиск_массив1 и поиск_массив2… & поиск_массивN, 0), 0)

Например, вы хотите узнать объем продаж фрукта, вес которого 242 и происходит на 9/1/2019, вы можете ввести формулу ниже в пустую ячейку и нажать Ctrl + Shift + Enter ключи вместе.

Внимание: В приведенной выше формуле F3: F22 — это столбец суммы, в котором вы найдете значение, B3: B22 — столбец даты, E3: E22 — столбец веса, J3 — дата, указанная в качестве первого критерия, J5 — значение веса, указанное как второй критерий.

Если вы хотите найти значение на основе трех или более критериев, вы можете легко добавить свои критерии и lookup_array в раздел MATCH. Обратите внимание, что критерии и lookup_array должны быть в одном порядке.

Например, вы хотите узнать объем продаж груши с весом 242, который произошел 9, вы можете добавить критерии и lookup_array следующим образом:

= ИНДЕКС (F3: F22; ПОИСКПОЗ (J3 &J4& J5, B3: B22 &C3: C22& E3: E22,0), 0)

И нажмите Ctrl + Shift + Enter ключи для определения суммы продаж.

Найдите значение по двум или нескольким критериям с помощью расширенного фильтра

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

Kutools for Excel— Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 60-дневная пробная версия, кредитная карта не требуется! Get It Now

1. Нажмите Данные > Дополнительно для включения функции расширенного фильтра.

2. В диалоговом окне Advanced Filter сделайте следующее:
(1) Проверьте Скопировать в другое место вариант в Действие раздел;
(2) В Диапазон списка поле, выберите диапазон, в котором вы найдете значения (A1: E21 в моем случае);
(3) В Диапазон критериев поле, выберите диапазон, в котором вы найдете значения (H1: J2 в моем случае);
(4) В Скопировать в выберите первую ячейку целевого диапазона, в который вы поместите отфильтрованные строки (H9 в моем случае).

3, Нажмите OK кнопку.

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

Статьи по теме:

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

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

Countif с несколькими критериями в Excel
Используйте функцию СЧЁТЕСЛИ для подсчета ячеек с одним критерием, используйте функцию СЧЁТЕСЛИ для подсчета ячеек с двумя или более критериями и используйте другие методы для подсчета ячеек, удовлетворяющих нескольким критериям одновременно в Excel.

Возвращает значение ячейки на основе нескольких критериев в Excel
Используйте формулу массива для возврата значения на основе нескольких критериев в Excel

Встречаем новый Excel, Microsoft представила динамические массивы (Dynamic Array Functions)

24 сентября 2018 года Microsoft представила одно из самых существенных изменений в Excel за последнее десятилетие — Dynamic Array Functions, если вообще не с момента выхода электронного процессора. Революционность изменений косвенно подтверждается и периодом предварительного тестирования, по состоянию на конец лета 2019 года, воспользоваться нововведением могут только участники программы Office Insider.

Читайте так же:
Где находятся расширения в гугл хром

Dynamic Array Functions (DAF) или в переводе функции динамических массивов. На первый взгляд может показаться, что достаточно тривиальное нововведение — 7 дополнительных функций (5 из группы «Ссылки и массивы» и 2 из группы «Математические»):

Также табличный процессор пополнился дополнительной ошибкой #ПЕРЕНОС! или #SPILL! в англоязычной версии программы.

В чем же революционность обновления?

Дело в том, что для работы новых функций пришлось изменить принцип функционирования Excel. До появления Функций динамических массивов (DAF) пользователь по сути не мог в одной ячейке ссылаться на диапазон ячеек. Вернее ссылаться мог, но, скажем так, такие ссылки работали в очень ограниченных случаях:

  • в специальных функциях, например, можно посчитать сумму, в таком случае в качестве аргумента функции будет передаваться ссылка на диапазон;
  • некоторые функции и формулы, которые работали как формулы массива (т.е. с применением нажатия Ctrl + Shift + Enter );
  • можно сослаться на диапазон в одной ячейке и, если данная ячейка находиться в строке, которая является частью диапазона, то будет возвращено одно значение из диапазона.

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

Да введения DAF, Excel не мог расширять возвращаемый диапазон на несколько ячеек. Опытный пользователь подметит «А как же формулы массива, введенные через сочетание Ctrl + Shift + Enter ?». Это не динамические массивы, т.к. область возвращаемого диапазона должна быть заранее очерчена пользователем.

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

На рисунке выше, представлен вариант из третьего пункта, поскольку ячейка с формулой находится на одной строке с массивом, на который ссылается, то возвращается значение из диапазона, если бы строки введенной формулы и исходного массива не пересеклись, мы бы получили сообщение об ошибке #ЗНАЧ!. Такая возможность называется пересечением и интересный факт, по сути 99% пользователей не использовали эту возможность, либо использовали не осознанно.

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

Почему так долго тестируют?

Окей, ну ввели эти динамические массивы, добавили новые формулы, которые используют возможности DAF, в чем заминка с тестированием?

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

А, поскольку, изменения коснулись автозаполнения, то использование различных типов ссылок тоже следует пересмотреть — а это, на минуточку, основа основ.

Кто сможет пользоваться?

Самое интересное — это для каких версий будет доступно следующее нововведение. После официального выхода DAF, возможности будут доступны подписчикам Office 365, если хотите раньше то можно подписаться на программу Office Insider и получить обновление уже сейчас. Сборки медленного круга достаточно стабильны. Ну а пользователи, которые приобрели Office 2019, не говоря уже о более ранних версиях, похоже не смогут воспользоваться динамическими массивами.

Массивы в Excel можно назвать формулами массива в Excel. Массив в Excel — это очень мощные формулы, которые позволяют нам выполнять сложные вычисления.

Читайте так же:
Метод крамера в excel

Перво-наперво, что такое массив? Массив — это набор значений или переменных в наборе данных, например — это массив, который имеет значения от «a» до «d». Точно так же в массиве excel — это диапазон ячеек значений,

Массивы в Excel, пример 1

На приведенном выше снимке экрана ячейки от B2 до ячейки G2 представляют собой массив или диапазон ячеек.

Они также известны как «формулы CSE» или «Control Shift Enter Formulas», поскольку нам нужно нажимать Ctrl + Shift + Enter для создания формулы массива в Excel.

В Excel у нас есть два типа формул массива:

  1. Тот, который дает нам единственный результат.
  2. Другой, который дает нам несколько результатов.

Мы изучим оба типа формул массива по этой теме.

Объяснение формул массива в Excel

Как объяснялось, формулы массива в Excel — это мощные формулы, которые помогают нам выполнять очень сложные вычисления.

Из приведенных выше примеров мы узнали, что эти формулы в Excel действительно упрощают сложные и длительные вычисления. Однако нужно помнить одну вещь в примере 2, где мы вернули несколько значений, используя эти формулы в Excel. Мы не можем изменить или сократить значение ячейки, так как это часть массива.

Предположим, мы хотели удалить ячейку G6. Excel выдаст нам ошибку, или если мы изменим значение в типе ячейки, любое случайное значение в ячейке excel выдаст ошибку.

Например, введите случайное число в ячейку G6 и нажмите клавишу ВВОД, это приведет к следующей ошибке:

При вводе случайного значения 56 в ячейку G6 выдает ошибку, что мы не можем изменить часть массива.

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

Как использовать формулы массива в Excel?

Давайте изучим формулу массива на нескольких примерах:

Формулы массива могут использоваться двух типов:

  1. Если мы хотим вернуть одно значение, используйте эти формулы в одной ячейке, как в примере 1.
  2. Если мы хотим вернуть более одного значения, используйте эти формулы в Excel, выбрав диапазон ячеек, как в примере 2.
  3. Нажмите CTRL + Shift + Enter, чтобы создать формулу массива.

Пример # 1

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

Владелец хочет подсчитать общий объем продаж этих продуктов.

Формула массивов в Excel, пример 1

Владелец может продолжить и умножить количество проданных единиц каждого продукта, а затем просуммировать их, как в приведенном ниже уравнении:

Формула массивов в Excel, пример 1-1

И он получит общий объем продаж,

Формула массивов в Excel, пример 1-2

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

Формула массивов в Excel, пример 1-3

Мы создадим нашу первую формулу массива в ячейке B7.

# 1 — В ячейке B7 введите = sum и нажмите кнопку табуляции на клавиатуре. Это открывает формулу суммы.

Формула массивов в Excel, пример 1-4

# 2 — Выберите диапазон ячеек от B2 до G2.

Формула массивов в Excel, пример 1-5

# 3 — Теперь поставьте после этого знак звездочки «*» для умножения.

Формула массивов в Excel, пример 1-6

# 4 — Выберите диапазон ячеек от B3 до G3.

Формула массивов в Excel, пример 1-7

# 5 — Вместо нажатия клавиши ввода нажмите CTRL + SHIFT + ВВОД.

Формула массивов в Excel, пример 1-8

Excel вычислил общий объем продаж, умножив количество продуктов на цену в каждом столбце и просуммировав их. В выделенном разделе мы видим, что Excel создал массив для диапазонов ячеек от B2 до G2 и от B3 до G3.

Формула массивов в Excel, пример 1-9

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

Пример # 2

Продолжая сейчас с теми же данными, что, если владелец хочет знать продажи для каждого продукта отдельно, то есть продажи продукта 1 и продукта 2 и так далее.

Читайте так же:
Заполнение пустых ячеек в excel предыдущим значением

Формула массивов в Excel, пример 2

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

Формула массивов в Excel, пример 2-1

Уравнение в ячейке B5, он должен повторить его для ячейки c4, d4 и так далее.

Опять же, если бы это были большие данные, это было бы утомительной и утомительной задачей.

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

Пример 2-2

# 1 — Выберите ячейки, в которых мы хотим получить промежуточные итоги, т. Е. По продажам каждого продукта. В данном случае это диапазон ячеек от B8 до G8.

Пример 2-3

# 2 — Введите «равно», чтобы подписать «=»

Пример 2-4

# 3 — Выберите диапазон ячеек от B2 до G2.

Пример 2-5

# 4 — После этого введите звездочку «*».

Пример 2-6

# 5 — Теперь выберите диапазон ячеек от B3 до G3.

Пример 2-7

# 6 — Что касается формулы массива, не нажимайте ввод, нажмите CTRL + SHIFT + ВВОД.

Пример 2-8

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

Ячейки от B6 до G6 представляют собой массив.

Пример # 3

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

Пример 3

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

Пример 3-1

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

# 1 — В ячейке D8 введите = среднее и нажмите кнопку TAB.

Пример 3-2

# 2 — Чтобы рассчитать рост, нам нужно вычесть значения одного месяца из предыдущего месяца, чтобы выбрать диапазон ячеек от B3 до B7.

Пример 3-3

# 3 — Поставьте после этого знак вычитания (-),

Пример 3-4

# 4 — Теперь выберите ячейки от B2 до B6.

Пример 3-5

# 5 — Что касается массива в excel, не нажимайте ввод, нажмите CTRL + SHIFT + ВВОД.

Пример 3-6

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

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

Т еперь несколько типичных примеров работы с массивами
1. Переворачиваем массив.

Здесь незнакомая для вас конструкция

макрос. Во всём коде препроцессор автоматически заменит все вхождения SIZE на 10u.
2. Удаление элемента, выбранного пользователем.

Удаление элемента в данном случае, конечно, не происходит. Массив остаётся того же размера, что и раньше. Мы просто затираем удаляемый элемент следующим за ним и выводим SIZE-1 элементов.
3. Пользователь вводит значения в массив. После этого вывести все разные значения, которые он ввёл.
Пусть пользователь вводит конечное число элементов, допустим 10. Тогда заранее известно, что всего различных значений будет не более 10. Каждый раз, когда пользователь вводит число будем проходить по массиву и проверять, было ли такое число введено.

4. Пользователь вводит число — количество измерений (от 2 до 10). После этого вводит все измерения. Программа выдаёт среднее значение, дисперсию, погрешность.

5. Сортировка массива пузырьком

6. Перемешаем массив. Воспользуемся для этого алгоритмом Fisher-Yates:
Для i от N-1 до 1 выбираем случайное число j в пределах от 0 до i и меняем местами i-й и j-й элементы.

email

Всё ещё не понятно? – пиши вопросы на ящик

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