Все про массивы в VBA

Задача Статический
массив
Динамический
массив
Объявление Dim arr(0 To 5) As
Long
Dim arr() As Long
Dim arr As Variant
Установить размер Dim arr(0 To 5) As
Long
ReDim arr(0 To 5)As
Variant
Увеличить размер
(сохранить
существующие
данные)
Только
динамический
ReDim Preserve arr(0 To 6)
Установить
значения
arr(1) = 22 arr(1) = 22
Получить значения total = arr(1) total = arr(1)
Первая позиция LBound(arr) LBound(arr)
Последняя позиция Ubound(arr) Ubound(arr)
Читать все записи (1D) For i = LBound(arr) To UBound(arr)
Next i
Or
For i = LBound(arr,1) To UBound(arr,1)
Next i
For i = LBound(arr) To UBound(arr)
Next i
Or
For i = LBound(arr,1) To UBound(arr,1)
Next i
Читать все записи (2D) For i = LBound(arr,1) To UBound(arr,1)
For j = LBound(arr,2) To UBound(arr,2)
Next j
Next i
For i = LBound(arr,1) To UBound(arr,1)
For j = LBound(arr,2) To UBound(arr,2)
Next j
Next i
Читать все записи Dim item As Variant
For Each item In arr
Next item
Dim item As Variant
For Each item In arr
Next item
Перейти на Sub Sub MySub(ByRef arr() As String) Sub MySub(ByRef arr() As String)
Возврат из функции Function GetArray()
As Long()
Dim arr(0 To 5) As
Long
GetArray = arr
End Function
Function GetArray()
As Long()
Dim arr() As Long
GetArray = arr
End Function
Получить от
функции
Только
динамический
Dim arr() As Long
Arr = GetArray()
Стереть массив Erase arr
*Сбрасывает все
значения по
умолчанию
Erase arr
*Удаляет массив
Строка в массив Только
динамический
Dim arr As Variant
arr = Split(«James:Earl:Jones»,»:»)
Массив в строку Dim sName As String
sName = Join(arr, «:»)
Dim sName As String
sName = Join(arr, «:»)
Заполните
значениями
Только
динамический
Dim arr As Variant
arr = Array(«John», «Hazel», «Fred»)
Диапазон в массив Только
динамический
Dim arr As Variant
arr = Range(«A1:D2»)
Массив в диапазон Так же, как в
динамическом
Dim arr As Variant
Range(«A5:D6») = arr

В этой статье подробно рассматриваются массивы на языке программирования Excel VBA. Она охватывает важные моменты, такие как:

  • Зачем вам массивы
  • Когда вы должны их использовать
  • Два типа массивов
  • Использование более одного измерения
  • Объявление массивов
  • Добавление значений
  • Просмотр всех предметов
  • Супер эффективный способ чтения Range в массив

В первом разделе мы рассмотрим, что такое массивы и зачем они нужны. Вы можете не понимать часть кода в первом разделе. Это нормально. Я буду разбивать на простые термины в следующих разделах статьи.

Функция Excel AVERAGE

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

  • номер 1 — Номер или ссылка на ячейку, относящиеся к числовым значениям.
  • номер 2 — [необязательно] Число или ссылка на ячейку, которая относится к числовым значениям.

Функция СРЕДНЕЕ вычисляет среднее значение чисел, представленных как аргументы . Чтобы вычислить среднее значение, Excel суммирует все числовые значения и делит их на количество числовых значений. Это поведение можно воспроизвести с помощью СУММ и СЧИТАТЬ функционирует так:

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

Функция СРЕДНИЙ автоматически игнорирует текстовые значения и пустые ячейки. Однако включены нулевые (0) значения. Вы можете игнорировать нулевые (0) значения и / или применять определенные критерии, используя Функция СРЗНАЧЕСЛИМН , как описано ниже.

Функция AVERAGE игнорирует логические значения и числа, введенные как текст. Если вам нужно включить эти значения в среднее значение, см. Функция СРЗНАЧ .

Если значения, заданные для СРЕДНЕГО, содержат ошибки, СРЗНАЧ возвращает ошибку. Вы можете использовать Функция AGGREGATE для игнорирования ошибок .

Основное использование

Типичный способ использования функции AVERAGE — указать диапазон, как показано ниже. Копируемая формула в F3:

Базовое использование функции СРЕДНИЙ

В каждой новой строке AVERAGE вычисляет среднее значение оценок каждого человека.

Пустые ячейки

Функция СРЕДНИЙ автоматически игнорирует пустые ячейки. Обратите внимание, что на приведенном ниже экране ячейка C4 пуста, а AVERAGE просто игнорирует ее и вычисляет среднее значение только с B4 и D4:

Функция СРЕДНЕЕ с пустыми ячейками

Однако обратите внимание на нулевое (0) значение в C5 Включено в среднем, поскольку это допустимое числовое значение. Чтобы исключить нулевые значения, используйте СРЕДНИЙ или же В СРЕДНЕМ вместо. В приведенном ниже примере AVERAGEIF используется для исключения нулевых значений. Как и функция СРЕДНИЙ, СРЕДНИЙ автоматически исключает пустые ячейки.

Функция СРЗНАЧЕСЛИ исключает ноль

Смешанные аргументы

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

Функция СРЗНАЧ со смешанными аргументами

Среднее по критериям

Чтобы вычислить среднее значение с критериями, используйте СРЕДНИЙ или же В СРЕДНЕМ . В приведенном ниже примере СРЗНАЧЕСЛИМН используется для расчета среднего балла для красных и синих групп:

Функция СРЗНАЧЕСЛИМН с критериями

Функция СРЗНАЧЕСЛИМН также может применять несколько критериев .

Средний топ-3

Объединив функцию СРЕДНИЙ с НАИБОЛЬШИЙ функция , вы можете вычислить среднее из n верхних значений. В приведенном ниже примере формула в столбце I вычисляет среднее значение трех лучших результатов тестов в каждой строке:

СРЕДНЯЯ функция верхняя 3

Подробное объяснение здесь .

Средневзвешенное

Для расчета взвешенный в среднем, вы захотите использовать функцию СУММПРОИЗВ, как показано ниже:

Если в массиве несколько одинаковых значений?

Добавим несколько одинаковых элементов в массив:

12 строк в массиве - JavaScript

12 строк в массиве — JavaScript

Теперь, если мы попробуем получить индекс элемента, который содержит «as» , то мы получим только индекс 3. Индексы 10 и 11 мы не увидим.

Только индекс первого совпадения - JavaScript

Только индекс первого совпадения — JavaScript

Пример 3

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

Давайте посмотрим на пример массива, принимающего значение из пользовательского ввода

Теперь файл открыт в редакторе GNU nano. Мы добавили в него приведенный ниже сценарий C, чтобы вводить данные пользователя. После включения стандартного заголовка ввода-вывода и основной функции мы объявили массив «store» размером 7. Оператор print использовался для печати сообщения для пользователя о добавлении только целочисленных значений. Цикл «for» использовался для сбора и добавления введенных пользователем значений в массив «store» с помощью & sign в функции scanf. После завершения цикла «for» было распечатано другое сообщение на дисплее. Второй цикл «for» использовался для отображения значений массива на экране с помощью оператора «printf». Функция main закрывается фразой return 0. Сохраните этот код и уходите.

Читайте также: 

Теперь файл открыт в редакторе GNU nano

Теперь используйте запрос «gcc» для компиляции этого кода.

Теперь используйте запрос «gcc» для компиляции этого кода

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

После того, как код был скомпилирован, выполните запрос, показанный ниже

Использование Flash Fill

Еще один быстрый способ получить фамилии — использовать функцию Flash Fill.

Flash Fill был представлен в Excel 2013 и помогает манипулировать данными путем выявления шаблонов. Чтобы это сработало, вам нужно пару раз показать ожидаемый результат Flash Fill.

Как только он определит шаблон, он быстро сделает остальную работу за вас.

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

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

  • В ячейке B2 введите текст «Мори». Это результат, который вы ожидаете в ячейке.
  • Перейдите в следующую ячейку и введите фамилию для имени в соседней ячейке (Эллиот в этом примере).
  • Выберите обе ячейки
  • Наведите курсор на правую нижнюю часть выделения. Вы заметите, что курсор изменится на значок плюса.
  • Дважды щелкните по нему (или щелкните и перетащите его вниз). Это даст вам какой-то результат в ячейках (вряд ли это будет тот результат, который вам нужен)
  • Щелкните значок «Параметры автозаполнения».
  • Нажмите на Flash Fill.

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

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

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

Когда у вас есть все фамилии в столбце, вы можете отсортировать данные на основе этих фамилий.

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

Поиск DSUM с критериями > или <

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

В этом случае мы можем указать условие как «> 01.01.2020» в поле «Дата присоединения». Итак, давайте сначала построим таблицу критериев следующим образом:

Тогда формула DSUM будет:

Точно так же, если вы хотите, чтобы сумма включала продажи сотрудников, присоединившихся до 31.12.2019, ваша таблица критериев будет выглядеть так:

Наконец, вы также можете включить условие ИЛИ в свою таблицу критериев. Итак, если вы хотите включить продажи сотрудников, присоединившихся после 01.01.2020 или до (скажем) 01.06.2019, ваша таблица критериев будет выглядеть так:

Тогда ваша формула DSUM будет:

Вот результат, который мы получаем, когда эту формулу применяем к образцу базы данных:

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

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

  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. Результат:

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

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

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

4 способа поиска данных в таблице Excel

Добрый день уважаемый читатель!

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

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

Теперь на примерах рассмотрим все 4 способа поиска данных в таблице Excel и комбинаций работы функции ВПР с другими функциями:

Используем функцию СУММПРОИЗВ

Как я уже описывал ранее в своей статье о функции СУММПРОИЗВ, она является одной из мощнейших в арсенале Excel. И именно первый способ мы сделаем с помощью возможностей формулы при использовании функции СУММПРОИЗВ. Для наших целей формула будет выглядеть так:

Читайте также:  Функция ВЕРОЯТНОСТЬ для расчета вероятности событий в Excel

=СУММПРОИЗВ((C2:C11=G2)*(B2:B11=G3);D2:D11) Принцип работы формулы следующий: создается условная таблица, в которой значения ячеек «G2» сравнивается с диапазоном «C2:C11» и ячейка «G3» с диапазоном «B2:B11». После этого сравниваются и сопоставляются все эти два массива и переводятся в единицы и нули, где значение единицы ставится строке, где все условия формулы выполнены. Следующая операция – это умножения полученного условного массива на диапазон «D2:D11», а поскольку в массиве всего одна единичка то формула получит результат 146.

Обращаю ваше внимание, если в диапазоне «D2:D11» будут найдены текстовые значения, формула откажется работать. Для более углублённого ознакомления с функцией СУММПРОИЗВ советую почитать мою статью.

Применение функции ВЫБОР

Я описывал уже функцию ВЫБОР, но в таком исполнении еще не упоминал. В нашем случае нужно создать новую таблицу, в которой будут совместными столбики «Период» и «Месяц», всё это виртуально создаст функция ВЫБОР. Формула для работы будет выглядеть так:

;C2:C11&B2:B11;D2:D11);2;0)> Основная работа, которую проделывает функция ВЫБОР в своей части «ВЫБОР( ;C2:C11&B2:B11;D2:D11)» это объединение значений столбиков «Период» и «Город» в общий массив, значения в котором будут прописаны как: «МоскваЯнварь», «БрянскФевраль», …. и т.д. Получив такое объединённое значения столбиков мы сможем легко сделать просмотр и отбор нужного значения, вот теперь я думаю, формула стала ближе.

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

Создаем дополнительные столбики

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

Рассмотрим на стандартном примере, когда необходимо определить продажи по двум показателям: «Период» и «Город». В этом случае обыкновенное использование функции ВПР не будет нам подходить, так как функция может возвращать значение по одному условию. В таком случае нам необходимо создать дополнительный столбик, в котором произойдёт объединение двух критериев в один, поэтому в созданном столбике приписываем формулу слияния значений: =B2&C2. А вот теперь результат из столбика D, мы сможем использовать в ячейке H4 нашу формулу:

=ВПР(H2&H3;D2:E11;2;0)

Как видите, наши отдельные условия отбора значений также объединяются аргументом H2&H3 в один критерий. После поиска в указанном диапазоне D2:E11, формула вернёт найденное значение со столбика 2.

Совмещаем функции ПОИСКПОЗ и ИНДЕКС для работы

Последний способ в нашем списке будет конечно не самым лёгким, но достаточно простым и легко повторимым. Для его реализации будем снова использовать формулу массива, а также использованы функции ПОИСПОЗ и ИНДЕКС в эффективном и полезном симбиозе. Детально о работе этих функций вы можете ознакомиться в моих отдельных статьях.

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

Что же она делает, такая большая и непонятная…. Рассмотрим ее в разрезе нескольких блоков или этапов. Формула для функции имеет такой вид ПОИСКПОЗ (1;(B2:B11=G3)*(C2:C11=G2);0) и происходит следующее, со значением в ячейке G3, последовательно сравниваются значения из диапазона B2:B11 и в случае совпадения условий получаем результат ИСТИНА, а если есть отличия получаем ЛОЖЬ. Такой же процесс происходит для значения G2 и диапазона C2:C11. После сравнения этих массивов, которые состоят из аргументов ИСТИНА и ЛОЖЬ, производится сравнения на соответствие значению 1, это ИСТИНА*ИСТИНА, все остальные комбинации будут проигнорированы.

Теперь, когда функция ПОИСКПОЗ нашла в массиве значение, которое соответствует «1» и указала его позицию в шестой строке, а значит, в функцию ИНДЕКС был передан аргумент «6» для диапазона D2:D11.

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

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

Как сделать выборку в Excel по условию

Чтобы определить соответствующие значение первому наименьшему числу нужна выборка из таблицы по условию. Допустим мы хотим узнать первый самый дешевый товар на рынке из данного прайса:

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

В месте «диапазон_данных_для_выборки» следует указать область значений A6:A18 для выборки из таблицы (например, текстовых), из которых функция ИНДЕКС выберет одно результирующие значение. Аргумент «диапазон» означает область ячеек с числовыми значениями, из которых следует выбрать первое наименьшее число. В аргументе «заголовок_столбца» для второй функции СТРОКА, следует указать ссылку на ячейку с заголовком столбца, который содержит диапазон числовых значений.

Естественно эту формулу следует выполнять в массиве. Поэтому для подтверждения ее ввода следует нажимать не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.

Обратите внимание ниже на рисунок, где в ячейку B3 была введена данная формула в массиве:

Выборка соответственного значения с первым наименьшим числом:

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

Построение графиков

В Excel можно строить диаграммы разных типов. Но для нас интересны только два вида: диаграмма рассеяния (scatter) и график (line). Пример диаграммы рассеяния приведен на Рис. 24 .

Рис.24 Диаграмма рассеяния

Диаграммы такого типа используются для построения графиков счетов, зависимостей "измерено-предсказано", и т.п. От линейных графиков они отличаются равноправием обеих осей. В линейных графиках ось абсцисс предназначена только для отображения категорийных переменных, т.е. величин, в которых важна не их величина, а порядок следования. Поэтому линейные графики подходят для представления зависимостей от числа главных компонент, например, для изображения того, как величины RMSEC и RMSEP меняются при усложнении модели.

Читайте также:  Пример функция ТДАТА для вставки текущей дата и время в Excel

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

Что такое индекс рентабельности инвестиций (PI)

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

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

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

Индекс прибыльности (PI – аббревиатура английского термина Profitability Index) определяется как результат отношения сумм дисконтированного дохода и вложенного капитала. Иными словами, индекс показывает, сколько копеек приносит каждый рубль, потраченный на реализацию проекта. Особого пояснения требует слово «дисконтированный». В контексте, термин означает перерасчет прибыли с учетом фактора времени.

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

  • риски,
  • оборачиваемость капитала,
  • ликвидность приобретаемых активов,
  • перспективность проекта.

Немалую роль играет опыт проведения аналогичных операций.

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

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

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

Случайная выборка в Excel

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

Исходный набор данных:

Сначала вставим слева два пустых столбца. В ячейку А2 впишем формулу СЛЧИС (). Размножим ее на весь столбец:

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

Чтобы вставились значения, а не формула, щелкаем правой кнопкой мыши по столбцу В и выбираем инструмент «Специальная вставка». В открывшемся окне ставим галочку напротив пункта «Значения»:

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

Управление массивами в Microsoft Excel

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

Как в 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% и выше.

Ссылка на основную публикацию