Формулы массивов в excel

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

Приведу два примера, где не обойтись без массивов.

1. Как известно, функция Split возвращает одномерный массив подстрок, извлеченных из первоначальной строки с разделителями. Эти данные присваиваются заранее объявленному строковому (As String) одномерному динамическому массиву. Размерность устанавливается автоматически в зависимости от количества подстрок.

2. Данные в массивах обрабатываются значительно быстрее, чем в ячейках рабочего листа. Построчную обработку информации в таблице Excel можно наблюдать визуально по мерцаниям экрана, если его обновление (Application.ScreenUpdating) не отключено. Чтобы ускорить работу кода, можно значения из диапазона ячеек предварительно загрузить в динамический массив с помощью оператора присваивания (=). Размерность массива установится автоматически. После обработки данных в массиве кодом VBA полученные результаты выгружаются обратно на рабочий лист Excel

Обратите внимание, что загрузить значения в диапазон ячеек рабочего листа через оператор присваивания (=) можно только из двумерного массива

Пример2. Три наибольших значения

Найдем 3 наибольших значения в списке и выведем их в 3-х ячейках.

Формула массива для решения этой задачи: =НАИБОЛЬШИЙ(A2:A11;)

• выделите 3 ячейки, которые будут содержать 3 наибольших значения.
• в Строке формул введите вышеуказанную формулу массива;
• нажмите сочетание клавиш CTRL+SHIFT+ENTER .

Excel нельзя добавить в режиме совместного доступа — Вэб-шпаргалка для интернет предпринимателей!
Случается, что при открытии книги Excel с совместным доступом к файлу появляется запись «Файл заблокирован». Сохранить не получается. При последующем открытии оказывается, что общий доступ отключен. Возможные причины проблемы:

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

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

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

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

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

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

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

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

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

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

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



Как заставить ваши макросы работать на суперскорости

Если ваши макросы работают очень медленно, этот раздел будет очень полезным. Особенно, если вы имеете дело с большими объемами данных. В VBA это держится в секрете.

Обновление значений в массивах происходит экспоненциально быстрее, чем обновление значений в ячейках.

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

1. Скопируйте данные из ячеек в массив.
2. Измените данные в массиве.
3. Скопируйте обновленные данные из массива обратно в ячейки.

Например, следующий код будет намного быстрее, чем код ниже:

Public Sub ReadToArray()

    ' Считать значения в массив из первой строки
    Dim StudentMarks  As Variant
    StudentMarks = Range("A1:Z20000").Value

    Dim i As Long
    For i = LBound(StudentMarks) To UBound(StudentMarks)
        ' Обновление отметок здесь
        StudentMarks(i, 1) = StudentMarks(i, 1) * 2
        '...
    Next i

    ' Запишите новые значения обратно на лист
    Range("A1:Z20000").Value = StudentMarks

End Sub

Sub UsingCellsToUpdate()
    
    Dim c As Variant
    For Each c In Range("A1:Z20000")
        c.Value = ' Обновите значения здесь
    Next c
    
End Sub

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

' Назначение - быстрее
Range("A1:A10").Value = Range("B1:B10").Value

' Копировать Вставить - медленнее
Range("B1:B1").Copy Destination:=Range("A1:A10")

Простой способ зафиксировать значение в формуле Excel

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

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

Полная фиксация ячейки

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

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

Когда мы в ячейку Е4 пропишем формулу =D4/D1, то в результате копирования, ячейки поменяют адреса и сдвинутся ниже, пропуская, так необходимый нам обменный курс.

А вот если внести изменения и зафиксировать значение в формуле простым символом доллара («$»), то мы получим следующий результат =D4/$D$1 и в этом случае, сдвигая и копируя, формулу мы получаем нужный нам результат во всех ячейках диапазона;

Фиксация формулы в Excel по вертикали

Частичная фиксация по вертикали (пример $A1), это закрепления только столбцов, возможность сдвига формулы частично сохраняется, но только по горизонтали (в строке). Как видно со скриншота или скачанного вами файла с примером.

Фиксация формул по горизонтали

Следующее закрепление будет по горизонтали (пример, A$1). И все правила остаются действительными как и предыдущем пункте, но немножко наоборот. Рассмотрим данный пример подробнее.

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

Итак, мы фиксируем горизонтальную строку $1 и вертикальный столбец $J и в ячейке К4 прописываем формулу =$J4*K$1 и после ее копирование во все ячейки вычисляемого диапазона и получаем нужный результат без каких-либо сдвигов в формуле.

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

    Что бы постоянно не переключать раскладку клавиатуры при прописании знака «$» для закрепления значение в формуле, можно использовать «горячую» клавишу F4.

Если курсор стоит на адресе ячейки, то при нажатии, будет автоматически добавлен знак «$» для столбцов и строчек.

При повторном нажатии, добавится только для столбцов, еще раз нажать, будет только для строк и 4-е нажатие снимет все закрепления, формула вернется к первоначальному виду.

      Скачать пример можно здесь.

      А на этом у меня всё! Я очень надеюсь, что вы поняли все варианты как возможно зафиксировать ячейку в формуле. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

    Не забудьте поблагодарить автора!

Деньги — нерв войны. Марк Туллий Цицерон

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

Применение массива констант

А. Умножение векторов (столбец на столбец, строку на строку) С помощью формулы массива умножим столбец значений ( B 2: B 6 ) на массив констант и просуммируем. Массив констант записан в «вертикальном» виде с использованием двоеточия, т.е. также представляет собой столбец. Размерности столбца и массива должны совпадать.

После ввода формулы необходимо нажать CTRL+SHIFT+ ENTER .

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

=СУММ(B2*1;B3*2; B4*3; B5*4; B6*5)

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

Б. Проверка значений Проверим, равно ли значение в ячейке А1 одному из определенных значений: 4, 6 или 9.

После ввода формулы нет необходимости нажимать CTRL+SHIFT+ENTER . Такая запись может существенно сократить время создания формулы по сравнению с использованием вложенных функций ЕСЛИ() .

Применение двумерного массива констант

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

Чтобы избежать утомительного ввода этого массива создайте два столбца, один с числами от 1 до 12, другой с названиями месяцев (см. статью Текстовые последовательности ). Затем, в Строке формул введите ссылку на этот диапазон, нажмите F9 и скопируйте в Буфер обмена .

Теперь записав формулу =ВПР(A2;Месяцы;2) , где в ячейке A2 — номер месяца, получим желаемый результат.

Для желающих получить дополнительную информацию о константах массива — ]]> ссылка на статью сайта Microsoft на английском языке ]]> .

Почему не сортируется столбец в excel | Хитрости Жизни
Далее Обновлять изменения. Как вы заметили, если сохраните файл, то будет показано, какие изменения внесли соредакторы файла. Есть возможность увеличить частоту. Даже сделать обновления почти он-лайн, каждые 5 минут (минимальный показатель).

ВидПредставленияПредставленияУдалить

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

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

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

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя .
• В поле Имя введите Массив123 .
• В поле Диапазон введите массив констант (не забудьте ввести скобки вручную), например ;
• Нажмите кнопку ОК.

Пример, найдем сумму 3-х наибольших значений , записав формулу =СУММПРОИЗВ(НАИБОЛЬШИЙ(A1:A10;Массив123)) .

Предполагается, что в диапазоне A1:A10 имеется список числовых значений.

Формулы массива в MS EXCEL. Знакомство

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

Без формул массива (array formulas) можно обойтись, т.к. это просто сокращенная запись группы однотипных формул. Однако, у формул массива есть серьезное преимущество: одна такая формула может заменить один или несколько столбцов с обычными формулами.

Например, можно найти сумму квадратов значений из диапазона А2:A12, просто записав в ячейке B14 формулу =СУММ(A2:A12^2) . Для сравнения: чтобы найти сумму квадратов, используя обычные формулы, нам потребуется дополнительный столбец для вычисления квадратов значений и одна ячейка для их суммирования (см. файл примера или диапазон B2:B13 на рисунке ниже).

В отличие от ввода обычных формул, после ввода формулы массива нужно нажать вместо ENTER комбинацию клавиш CTRL+SHIFT+ENTER (поэтому, иногда, формулы массива также называются формулами CSE — это первые буквы от названия клавиш, используемых для ввода Ctrl, Shift, Enter). После этого формула будет обрамлена в фигурные скобки (их не вводят с клавиатуры, они автоматически появляются после нажатия CTRL+SHIFT+ENTER). Это обрамление показано на рисунке выше (см. Строку формул).

Если бы мы нажали просто ENTER, то получили бы сообщение об ошибке #ЗНАЧ!, возникающую при использовании неверного типа аргумента функции, т.к. функция СУММ() принимает в качестве аргумента только диапазон ячеек (или формулу, результатом вычисления которой является диапазон, или константы). В нашем случае мы в качестве аргумента ввели не диапазон, а некое выражение, которое еще нужно вычислить перед суммированием, поэтому и получили ошибку.

Чтобы глубже понять формулы массива проведем эксперимент:

• выделим ячейку B13, содержащую обычную формулу =СУММ($B$2:$B$12) ;
• в Cтроке формул выделим аргумент функции СУММ() , т.е. $B$2:$B$12;
• нажмем клавишу F9, т.е. вычислим, выделенную часть формулы;
• получим – массив квадратов значений из столбца В. Массив – это просто набор неких элементов (значений).

Т.е. обычная функция СУММ() в качестве аргумента получила некий массив (или точнее ссылку на него).Теперь проведем тот же эксперимент с формулой массива:

• выделим ячейку, содержащую формулу массива =СУММ($A$2:$A$12^2) ;
• в строке формул выделим аргумент функции СУММ() , т.е. $A$2:$A$12^2 ;
• нажмем клавишу F9, т.е. вычислим, выделенную часть формулы;
• получим – тот же массив, что и в первом случае.

Т.е. нажатие CTRL+SHIFT+ENTER заставило EXCEL перед суммированием произвести промежуточные вычисления с диапазоном ячеек (с массивом содержащихся в нем значений). Для самой функции СУММ() ничего не изменилось – она получила тот же массив, только предварительно вычисленный, а не прямо из диапазона ячеек, как в случае с обычной формулой. Понятно, что вместо функции СУММ() в формуле массива может быть использована любая другая функция MS EXCEL: СРЗНАЧ() , МАКС() , НАИБОЛЬШИЙ() и т.п.

Вышеприведенный пример иллюстрирует использование функции массива возвращающей единственное значение, т.е. результат может быть выведен в одной ячейке. Это достигается использованием функций способных «свернуть» вычисленный массив до одного значения ( СУММ() , СРЗНАЧ() , МАКС() ). Примеры таких функций массива приведены в статье Формулы массива, возвращающие одно значение.

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

Преимущества и недостатки формул массива рассмотрены в одноименной статье Формулы массива. Преимущества и недостатки.

В файле примера также приведено решение данной задачи функцией СУММПРОИЗВ() , которая зачастую не требует введения ее как формулы массива: =СУММПРОИЗВ($A$2:$A$12^2)

Здесь, при вводе формулы СУММПРОИЗВ() нажимать CTRL+SHIFT+ENTER необязательно.

Ссылки на статьи о формулах массива на сайте Microsoft:

2. Основы формул массива (на английском) из книги авторов Colin Wilcox и John Walkenbach

ПРИМЕЧАНИЕ При создании Именованных формул и правил Условного форматирования формулы массива нельзя ввести нажимая CTRL+SHIFT+ENTER. Эти формулы вводятся только в ячейки листа. Однако, если формуле массива присвоить Имя, то EXCEL «сообразит», что нужно с ней нужно делать. Например, если формуле =СУММ($A$2:$A$12^2) присвоить имя Сумма_квадратов, а затем в ячейке указать =Сумма_квадратов , то получим правильный результат.

Применение массива констант

А. Умножение векторов (столбец на столбец, строку на строку)
С помощью формулы массива
умножим столбец значений (B

2:

B

6

) на массив констант
{1:2:3:4:5} и просуммируем. Массив констант
записан в «вертикальном» виде с использованием двоеточия, т.е. также представляет собой столбец. Размерности столбца и массива должны совпадать.

СУММ(B2:B6*{1:2:3:4:5})

После ввода формулы необходимо нажать CTRL+SHIFT+
ENTER
.

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

СУММ(B2*1;B3*2; B4*3; B5*4; B6*5)

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

Б. Проверка значений
Проверим, равно ли значение в ячейке А1

одному из определенных значений: 4, 6 или 9.

ИЛИ(A1={4;6;9})

После ввода формулы нет необходимости нажимать CTRL+SHIFT+ENTER
. Такая запись может существенно сократить время создания формулы по сравнению с использованием вложенных функций ЕСЛИ()
.

Типизированные константы типа «запись»

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

   
Примеры типизированных констант типа «запись».

type			
    Reс = record			
                 R : Real;			
                 В : Boolean;			
                 C : Char;			
             end;			
    ArrayOfRec = array  of  Rec;	
    RecOfArray = record			
                           ArrInt   : array  of  Integer;	
                           ArrChar  : array  of Char;
                          end;			
    RecOfRec = record			
                            I : Integer;
                            S : String;			
                            Z : Rec;
                        end;			
const		
    RecElem : Rec = ( R: 3.1415; B: True; С : '*' ) ;
    ArrRec   : ArrayOfRec = 	
                             ( ( R: 3.1415; B: True;  C : '*' ),
                               ( R: 0.0;    B: False; C : '$' ),
                               ( R: 6.2832; B: True;  C : '&' ) );
    RecArr  : RecOfArray  =		
                             ( Arrlnt: (1,2 ,3);  ArrChar: (' 1' , ' 2') ) ;
   RecRec  : RecOfRec =			
                             ( I: 32767;  S: 'PASCAL';	
                               Z: ( R: 3.1415; В: True; С: '*' ) ) ;

Общий доступ Excel. Совместная работа с файлом

Как создать общий доступ Excel?

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

Откроется окно Управление доступом к файлу. Ставьте галочку «Разрешить изменять файл нескольким пользователям…». В этом же окне вы сможете увидеть, кто сейчас работает в файле (сейчас User). Жмете ОК. Все, общий доступ дан и теперь в файле может работать сразу несколько человек. Удобно.

История общего доступа или «я в эту ячейку ничего не вносил»

Чтобы получить изменения, нажмите на кнопку Исправления — Выделить исправления

Если оставите все галочки как показано на картинке выше, то изменения созданные со времени последнего сохранения выделяться прямо в таблице примерно вот так:

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

Так же выбирете нужного пользователя или диапазон в котором необходимо искать изменения

Есть возможность настроить, и даже отключить эту функцию — смотрите ниже.

Чтобы удалить историю изменений, отключите и включите заново совместный доступ.

Что нужно знать еще о совместном доступе к файлу? Управление доступом к файлу

В окне Управление доступом к файлу, есть вкладка Подробнее, где найдется много интересного.

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

Далее Обновлять изменения. Как вы заметили, если сохраните файл, то будет показано, какие изменения внесли соредакторы файла. Есть возможность увеличить частоту. Даже сделать обновления почти он-лайн, каждые 5 минут (минимальный показатель).

Часто задаваемый вопрос — «Какие изменения занесутся, мои или соседа по файлу?». В настройках существует пункт Для противоречивых изменений — у вас спрашивают, чьи изменения оставить. Если выберите «Запрашивать…», то перед сохранением будет вопрос «Чьи изменения оставить», для второго пункта останутся те изменения, которые сделаны ранее.

Ну и последний, но не менее важный пункт — Включить в личное представление. Личное представление — настройки, которые пользователь создает под себя в общем файле. Если вы владелец процесса и не хотите, чтобы кто-то изменял параметры печати (вы их настроили заранее) или добавлял дополнительные фильтры — снимите эти галочки.

Что нельзя делать, если файл находится в общем доступе?

Да, существуют еще ограничения при работе с общим доступом. В первую очередь неудобно отсутствие условного форматирования и то что нельзя вставлять диаграмму или сводную таблицу.

• Управление и изменение макросов
• Добавлять рисунки и другие объекты
• Группировать или скрывать столбца или строки
• Делать объединение ячеек (не придется потом снимать объединение:) )
• Пользоваться инструментом «Проверка данных», например создавать выпадающий список.
• Делать защиту листа
• Невозможна работа с формулами массива

В одном разделе с общим доступом, расположено еще много кнопочек, подробнее о них рассказывается в статье: Как защитить Excel от редактирования?

Join

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

СинтаксисПараметры и Описание

• Список — требуемый параметр. Массив, содержащий подстроки, которые должны быть соединены.
• Разделитель — необязательный параметр. Символ, который используется как разделитель при возврате строки. По умолчанию разделителем является .

пример

Добавьте кнопку и добавьте следующую функцию.

Private Sub Constant_demo_Click()
   ' Join using spaces
   a = array("Red","Blue","Yellow")
   b = join(a)
   msgbox("The value of b " & " is :"  & b)
  
   ' Join using $
   b = join(a,"$")
   msgbox("The Join result after using delimiter is : " & b)
End Sub

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

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

Erase

Функция Erase используется для сброса значений массивов фиксированного размера и освобождения памяти динамических массивов.Он ведет себя в зависимости от типа массивов.

Синтаксис

• Фиксированный числовой массив, каждый элемент в массиве сбрасывается до нуля.
• Исправлен строковый массив, каждый элемент в массиве сбрасывается до нулевой длины «».
• Массив объектов, каждый элемент в массиве сбрасывается до специального значения .

пример

Добавьте кнопку и добавьте следующую функцию.

Private Sub Constant_demo_Click()
   Dim NumArray(3)
   NumArray(0) = "VBScript"
   NumArray(1) = 1.05
   NumArray(2) = 25
   NumArray(3) = #23/04/2013#
  
   Dim DynamicArray()
   ReDim DynamicArray(9)   ' Allocate storage space.
  
   Erase NumArray          ' Each element is reinitialized.
   Erase DynamicArray      ' Free memory used by array.
  
   ' All values would be erased.
   msgbox("The value at Zeroth index of NumArray is " & NumArray(0))
   msgbox("The value at First index of NumArray is " & NumArray(1))
   msgbox("The value at Second index of NumArray is " & NumArray(2))
   msgbox("The value at Third index of NumArray is " & NumArray(3))
End Sub

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Функция, которая восстанавливает выделенную память для переменных массива.

Предыдущие статьи

• Функции даты-времени в VBA. Работа с датой и временем — 25/03/2018 17:33
• Работа со строками в VBA: InStr, LCASE, UCase, Left, Right, Mid, LTrim, RTrim, Trim, Len, Replace, Space, StrComp, String, StrReverse — 25/03/2018 15:57
• Работа с циклами в VBA:loop, for each, for next — 17/03/2018 18:40
• Решения, условия, алгоритмы if, then, switch в VBA Excel — 10/03/2018 21:52
• Константы в VBA Excel — Const. Как присвоить значение константам — 10/03/2018 07:53
• Поле ввода в VBA Excel — InputBox. Синтаксис, параметры и описание — 10/03/2018 07:03
• Окно сообщения в VBA Excel — MsgBox — 09/03/2018 06:34
• Простые Макросы Excel, написать шаг за шагом — 10/02/2018 20:07
• Переменные в VBA, значения и типы — 26/06/2017 06:37
• Программирование vba, макросы в Word и Excel — 19/12/2015 16:18

Работа с массивами Excel

Содержание работы

Работа с массивами.

Рассмотрим один важный ас­пект применения функций, работающих с массивами, а именно, подве­дение сложных итогов. Обычно в таблицах требуется подсчитать сум­му или количество элементов с некоторыми общими признаками. Ис­пользование функций СУММЕСЛИ() и СЧЕТЕСЛИ() ограничено воз­можностью анализа в них только одного единственного условия, кото­рое можно внести в качестве аргумента, а также ограниченности ссы­лок на клетки. Ввиду этого, если нужно сделать сложную выборку, они нам не помогут.

Мин. зарп.

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

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

Похожие материалы

• Решение системы линейных уравнений тремя способами в Excel
• Для базы данных «Моя библиотека» создание формы СУБД MS Access 97 для ввода данных с помощью Мастера. Ввод данных с помощью формы. Создание простейшего макроса. Работа с формой в режиме Конструктора. Создание отчетов
• Расчет стипендии с помощью приложения MS Excel с использованием функций

Информация о работе

• АлтГТУ 419
• АлтГУ 113
• АмПГУ 296
• АГТУ 267
• БИТТУ 794
• БГТУ «Военмех» 1191
• БГМУ 172
• БГТУ 603
• БГУ 155
• БГУИР 391
• БелГУТ 4908
• БГЭУ 963
• БНТУ 1070
• БТЭУ ПК 689
• БрГУ 179
• ВНТУ 120
• ВГУЭС 426
• ВлГУ 645
• ВМедА 611
• ВолгГТУ 235
• ВНУ им. Даля 166
• ВЗФЭИ 245
• ВятГСХА 101
• ВятГГУ 139
• ВятГУ 559
• ГГДСК 171
• ГомГМК 501
• ГГМУ 1966
• ГГТУ им. Сухого 4467
• ГГУ им. Скорины 1590
• ГМА им. Макарова 299
• ДГПУ 159
• ДальГАУ 279
• ДВГГУ 134
• ДВГМУ 408
• ДВГТУ 936
• ДВГУПС 305
• ДВФУ 949
• ДонГТУ 498
• ДИТМ МНТУ 109
• ИвГМА 488
• ИГХТУ 131
• ИжГТУ 145
• КемГППК 171
• КемГУ 508
• КГМТУ 270
• КировАТ 147
• КГКСЭП 407
• КГТА им. Дегтярева 174
• КнАГТУ 2910
• КрасГАУ 345
• КрасГМУ 629
• КГПУ им. Астафьева 133
• КГТУ (СФУ) 567
• КГТЭИ (СФУ) 112
• КПК №2 177
• КубГТУ 138
• КубГУ 109
• КузГПА 182
• КузГТУ 789
• МГТУ им. Носова 369
• МГЭУ им. Сахарова 232
• МГЭК 249
• МГПУ 165
• МАИ 144
• МАДИ 151
• МГИУ 1179
• МГОУ 121
• МГСУ 331
• МГУ 273
• МГУКИ 101
• МГУПИ 225
• МГУПС (МИИТ) 637
• МГУТУ 122
• МТУСИ 179
• ХАИ 656
• ТПУ 455
• НИУ МЭИ 640
• НМСУ «Горный» 1701
• ХПИ 1534
• НТУУ «КПИ» 213
• НУК им. Макарова 543
• НВ 1001
• НГАВТ 362
• НГАУ 411
• НГАСУ 817
• НГМУ 665
• НГПУ 214
• НГТУ 4610
• НГУ 1993
• НГУЭУ 499
• НИИ 201
• ОмГТУ 302
• ОмГУПС 230
• СПбПК №4 115
• ПГУПС 2489
• ПГПУ им. Короленко 296
• ПНТУ им. Кондратюка 120
• РАНХиГС 190
• РОАТ МИИТ 608
• РТА 245
• РГГМУ 117
• РГПУ им. Герцена 123
• РГППУ 142
• РГСУ 162
• «МАТИ» — РГТУ 121
• РГУНиГ 260
• РЭУ им. Плеханова 123
• РГАТУ им. Соловьёва 219
• РязГМУ 125
• РГРТУ 666
• СамГТУ 131
• СПбГАСУ 315
• ИНЖЭКОН 328
• СПбГИПСР 136
• СПбГЛТУ им. Кирова 227
• СПбГМТУ 143
• СПбГПМУ 146
• СПбГПУ 1599
• СПбГТИ (ТУ) 293
• СПбГТУРП 236
• СПбГУ 578
• ГУАП 524
• СПбГУНиПТ 291
• СПбГУПТД 438
• СПбГУСЭ 226
• СПбГУТ 194
• СПГУТД 151
• СПбГУЭФ 145
• СПбГЭТУ «ЛЭТИ» 379
• ПИМаш 247
• НИУ ИТМО 531
• СГТУ им. Гагарина 114
• СахГУ 278
• СЗТУ 484
• СибАГС 249
• СибГАУ 462
• СибГИУ 1654
• СибГТУ 946
• СГУПС 1473
• СибГУТИ 2083
• СибУПК 377
• СФУ 2424
• СНАУ 567
• СумГУ 768
• ТРТУ 149
• ТОГУ 551
• ТГЭУ 325
• ТГУ (Томск) 276
• ТГПУ 181
• ТулГУ 553
• УкрГАЖТ 234
• УлГТУ 536
• УИПКПРО 123
• УрГПУ 195
• УГТУ-УПИ 758
• УГНТУ 570
• УГТУ 134
• ХГАЭП 138
• ХГАФК 110
• ХНАГХ 407
• ХНУВД 512
• ХНУ им. Каразина 305
• ХНУРЭ 325
• ХНЭУ 495
• ЦПУ 157
• ЧитГУ 220
• ЮУрГУ 309

Полный список ВУЗов

• О проекте
• Реклама на сайте
• Правообладателям
• Правила
• Обратная связь

Чтобы распечатать файл, скачайте его (в формате Word).

Меняем местами столбцы и строки

Иногда при работе в Excel нужно поменять положение столбцов или строк — транспортировать их. Например, перевести шапку таблицы из горизонтального положения в вертикальное. Делать это вручную долго — особенно, когда ячеек очень много. Ускорить процесс помогут массивы и оператор ТРАНСП:

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

Используем оператор ТРАНСП, чтобы поменять положение шапки таблицы с горизонтального на вертикальноеСкриншот: Excel / Skillbox Media

Шаг 2. Выделяем шапку таблицы и закрываем скобку. Строка ссылок принимает вид: fx=ТРАНСП(A1:E1).

Выделяем диапазон, который нужно транспортироватьСкриншот: Excel / Skillbox Media

Шаг 3. Нажимаем Enter — функция меняет положение шапки таблицы на вертикальное.

За несколько секунд шапка таблицы превратилась из горизонтальной в вертикальнуюСкриншот: Excel / Skillbox Media

Как создать формулу массива в «Google Таблицах»? Всё точно так же, как в Excel, но нужно добавить оператор ARRAYFORMULA. Его ставят перед всей формулой массива в строке ссылок. Например, если вы хотите перемножить данные в двух столбцах, формула в готовом виде будет выглядеть так: fx=ARRAYFORMULA(B2:B9*D2:D9).

Так выглядит работа формулы массива в «Google Таблицах»Скриншот: Google Таблицы / Skillbox Media

Сортировка массива

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

Функция быстрой сортировки ниже может использоваться для сортировки массива.

Sub QuickSort(arr As Variant, first As Long, last As Long)
  
  Dim vCentreVal As Variant, vTemp As Variant
  
  Dim lTempLow As Long
  Dim lTempHi As Long
  lTempLow = first
  lTempHi = last
  
  vCentreVal = arr((first + last) / 2)
  Do While lTempLow <= lTempHi
  
    Do While arr(lTempLow) < vCentreVal And lTempLow < last
      lTempLow = lTempLow + 1
    Loop
    
    Do While vCentreVal < arr(lTempHi) And lTempHi > first
      lTempHi = lTempHi - 1
    Loop
    
    If lTempLow <= lTempHi Then
    
        ' Поменять значения
        vTemp = arr(lTempLow)

        arr(lTempLow) = arr(lTempHi)
        arr(lTempHi) = vTemp
      
        ' Перейти к следующим позициям
        lTempLow = lTempLow + 1
        lTempHi = lTempHi - 1
      
    End If
    
  Loop
  
  If first < lTempHi Then QuickSort arr, first, lTempHi
  If lTempLow < last Then QuickSort arr, lTempLow, last
  
End Sub

Вы можете использовать эту функцию так:

Sub TestSort()

    ' Создать временный массив
    Dim arr() As Variant
    arr = Array("Банан", "Дыня", "Персик", "Слива", "Яблоко")
  
    ' Сортировать массив
    QuickSort arr, LBound(arr), UBound(arr)

    ' Печать массива в Immediate Window(Ctrl + G)
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i)
    Next i

End Sub

5 основных функции для работы с массивами

     Доброго времени суток друзья!

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

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

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

     Позволит вам выбрать значение из общего списка по указанному номеру позиции:

           =ВЫБОР(2;»Стул»;»Стол»;»Шкаф»;»Диван»)

      Эта функция возвращает указанное значение из одно- или двумерного диапазона:

       =ИНДЕКС(A1:C6;4;3)

     Как видно с примера, полученное значение 37, в указанном диапазоне стоит на пересечении строки №4 и столбика №3 в диапазоне A1:C6 указанном в формуле. В более простом примере показано как в диапазоне С1:С6, на 2 месте находится значение 15:

       =ИНДЕКС(С1:С6;2)

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

         =ПОИСКПОЗ(B3;B2:B5;0)

    С примера вы можете видеть что слово «Стол» занимает 2 позицию в указанном диапазоне. Замечу, что третий аргумент в функции не является обязательным. При введенном значении 0, функция вернет ту позицию элемента массива, которое точно совпадает со значением, которое мы ищем. В случае, когда точное совпадение отсутствует, функция выдаст ошибку #Н/Д (#N/A).

     Ищет значение в указанном диапазоне и возвращает значение ячейки, которая находится в указанной строке того же столбца:  =ГПР(C1;$B$1:$E$2;1;ЛОЖЬ). Как видите с примера, функция ГПР ищет в указанном диапазоне $B$1:$E$2 (знаком $ я указал абсолютную ссылку) и согласно условию возвращает искомое значение из первой строки, а аргумент «ЛОЖЬ» означает, что-либо, будет найдено нужное значение, либо мы получим ошибку #Н/Д.

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

         =ВПР(B4;$B$2:$C$5;2;ЛОЖЬ)

      Как видим, формула идентична предыдущей функции ГПР и так же ищет указанный номер «B4» в диапазоне $B$2:$C$5 со знаком $ (это сделано для создания абсолютной ссылки, что бы при копировании формулы на диапазон, аргумент не будет изменен), в третьем столбце, так как аргумент функции равен 2. Ну и четвёртый аргумент равен значению «ЛОЖЬ», это означает, что-либо будет найдено совпадение значений, либо будет получено сообщение об ошибке #Н/Д. Теперь при необходимости, мы копируем формулу, и она перенесёт все правильные аргументы по всему диапазону вычислений. Это возможно стало из-за абсолютной ссылки на массив значений, а вот первый аргумент на B4, при копировании, должен измениться на B5 и так далее.

     А на этом у меня всё! Я очень надеюсь, что описание 5 основных функций для работы с массивами вам стали ближе и понятнее. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

     Не забудьте поблагодарить автора! 

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!