Использование формул массивов в Excel

246058 08.03.2007 Скачать пример

Терминология

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

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

Пример 1. Классика жанра - товарный чек

Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:

1. выделяем ячейку С7

2. вводим с клавиатуры =СУММ(

3. выделяем диапазон B2:B5

4. вводим знак умножения (звездочка)

5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ - в итоге должно получиться так:

6. чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Вуаля!

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

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

Пример 2. Разрешите Вас... Транспонировать?

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

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

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

вводим функцию транспонирования =ТРАНСП(

в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем "перевернутый массив" в качестве результата:

Редактирование формулы массива

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение:

Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

Пример 3. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике... На обороте тетради на обложке было что? Таблица умножения вот такого вида:

При помощи формул массива она вся делается в одно движение:

1. выделяем диапазон B2:K11

2. вводим формулу =A2:A11*B1:K1

3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

и получаем результат:

Пример 4. Выборочное суммирование

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

В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

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

или воспользоваться быстрыми клавишами 1)Встать на ячейку массива 2)Нажатием CTRL+/ выделить весь массив 3)изменить формулу в строке формул 4)нажав сочетание клавиш Ctrl + Shift + Enter, ввести новую формулу

СРАВНЕНИЕ ЧЕРЕЗ МАКРОСЫ

Добрый день! Очень часто пользуюсь операцией сравнения двух массивов на соответствие элементов. Идея какая, 1. выделяем 1 столбик ячеек - нажимаем кнопку 1 - это массив mas1, 2. выделяем 2 столбик ячеек - нажимаем кнопку 2 - это массив mas2, 3. нажимаем кнопку 3 - происходит сравнение элементов mas1 и mas2, и если нет отличий - то выдается сообщение Ok, если есть отличие, выделяем ячейку 1-го несоответствия. Действия 1 и 2 реализуются следующим образом Sub Макрос_mas1() ActiveWorkbook.Names.Add Name:="mas1", RefersToR1C1:=Selection End Sub Sub Макрос_mas2() ActiveWorkbook.Names.Add Name:="mas2", RefersToR1C1:=Selection End Sub Помоготе реализовать действие 3. Или у кого есть другие варианты решения этой задачи?

treider

13.10.2005, 09:28

Можно просто сравнить два диапазона Dim R1 As Range Dim R2 As Range Private Sub CommandButton1_Click() Set R1 = Selection End Sub Private Sub CommandButton2_Click() Set R2 = Selection End Sub Sub d3() r = R1.Rows.Count c = R1.Columns.Count For a = 1 To r For b = 1 To c If R1.Cells(a, b).Value <> R2.Cells(a, b).Value Then R1.Cells(a, b).Select Exit Sub End If Next Next End Sub

Avsha

13.10.2005, 10:15

Спасибо treider, :D немного подредактировал ваш вариант, расположив макросы в PERSONAL.XLS и привязал их к кнопкам панели инструментов: Модуль "Mod_Ravno" Dim R1 As Range Dim R2 As Range Sub mas1() Set Mod_Ravno.R1 = Selection ActiveWorkbook.Names.Add Name:="mas1", RefersToR1C1:=Selection End Sub Sub mas2() Set Mod_Ravno.R2 = Selection ActiveWorkbook.Names.Add Name:="mas2", RefersToR1C1:=Selection End Sub Sub Ravno() r = Mod_Ravno.R1.Rows.Count c = Mod_Ravno.R1.Columns.Count For a = 1 To r For b = 1 To c If Mod_Ravno.R1.Cells(a, b).Value <> Mod_Ravno.R2.Cells(a, b).Value Then Mod_Ravno.R1.Cells(a, b).Select Exit Sub End If Next Next MsgBox "Сравнение завершено успешно !" End Sub

Naeel Maqsudov

14.10.2005, 20:49

Нет предела свершенству! Замените 3 действия одним! Sub Ravno() Dim R1 As Range, R2 As Range, c As Range, DiffAddr As String DiffAddr = "" With Selection.Areas If .Count = 2 Then Set R1 = .Item(1) Set R2 = .Item(2) If R1.Rows.Count = R2.Rows.Count And _ R1.Columns.Count = R2.Columns.Count Then For Each c In R2 If c.Value <> Cells(c.Row - R2.Row + R1.Row, c.Column - R2.Column + R1.Column).Value Then DiffAddr = DiffAddr & "," & c.Address(False, False) End If Next DiffAddr = Mid(DiffAddr, 2) If DiffAddr = "" Then MsgBox "These ranges are equals. Congratulations!!!" Else Range(DiffAddr).Select MsgBox Selection.Cells.Count & " wrong cell(s) was found." End If Else MsgBox "These ranges are not equals because its size is different." End If Else MsgBox "Select exactly TWO ranges, please." End If End With End Sub Теперь надо выделить один (эталоный) массив, затем с клавишей Ctrl второй (порядок выделения важен!), в котором ищутся отличия и запустить макрос. Он найдет и выделит ВСЕ отличия. PS Поясню прелесть выделения нескольких ячеек: Выделенные ячейки можно редактировать "прыгая" по ним клавишей Enter. Главное - это не нажимать стрелок.

Avsha

15.10.2005, 09:32

Naeel Maqsudov, Спасибо за вариант, я тоже думал, как нормальный (или ненормальный) :) максималист о выделении обоих диапазонов за раз. Но есть ограничение при этом способе выбора, оно следущее: Если выбирать один диапазон с помощью клавиатуры, например длинный столбец длиной под 1000 ячеек, то я использую удобное сочтание Shift-Ctrl-стрелка вниз, - выделяется столбец до "упора" - последнего значения. Но выделить одновременно два таких столбца - удобным способом у меня не очень получается. Хотя для небольших диапазонов - ваш вариант несомненно выигрывает по лаконичности.

Naeel Maqsudov

19.10.2005, 22:07

Коллега, :) разработчики позаботились обо всем. Выделите один, затем нажмите Shift+F8 и выделите другой, затем опять Shift+F8, и третий.... Никакой мыши!

Avsha

20.10.2005, 05:19

Спасибо, ну вот вроде "добили" тему :)

Avsha

01.11.2005, 06:42

Нет предела свершенству! Теперь диапазоны самовыделяются: Sub mas1() Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Set Mod_Ravno.R1 = Selection ActiveWorkbook.Names.Add Name:="mas1", RefersToR1C1:=Selection End Sub Sub mas2() Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Set Mod_Ravno.R2 = Selection ActiveWorkbook.Names.Add Name:="mas2", RefersToR1C1:=Selection End Sub

Сравнение и соединение таблиц с данными в Excel

Допустим имеется 2 таблицы с данными, при этом 1 столбец в данных таблицах одинаков, таблицы размещены на разных листах. В моем примере одинаков столбец "А".

Таблица 1. Лист1.

Таблица 2.Лист2.

Необходимо, объединить данные таблицы при этом сравнение будет производится по столбцу "А"(ФИО).Будем использовать стандартную функцию Excel - ВПР.

Функция ВПР

— Ищет значение в первом столбце массива таблицы и возвращает значение в той же строке из другого столбца массива таблицы. Буква В в названии функции ВПР означает «вертикально». Функция ВПР используется вместо функции ГПР, если сравниваемые значения расположены в столбце слева от искомых данных.

Синтаксис функции ВПР - ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)

Итак, функция для сравнения и соединения таблиц, будет следующей:

=ЕСЛИ(ЕНД(ВПР(A2;Лист2!$A$1:$B$200;2;0));"";ВПР(A2;Лист2!$A$1:$B$200;2;0))

Опишу части формулы:

ВПР(A2;Лист2!$A$1:$B$200;2;0)

• A2 - значение, которое должно быть найдено в первом столбце табличного массива. В моем примере ищем значение указанное в столбце "A", начиная с ячейки A2.

• Лист2!$A$1:$B$200; -таблица в которой искать, т.е. в данном случае это ссылка на лист2(Лист2), а таблица ($A$1:$B$200) задается, как и все таблицы в Excel - задается левое верхнее значение и правое нижнее значение, т.е. A1-левое верхнее, B200-правое нижнее.

• Значок $ - обозначает абсолютную ссылку в формуле и используется при указании фиксированного адреса ячейки.

• 2; - номер столбца в аргументе «таблица», из которого возвращается соответствующее значение. Если номер_столбца = 1, то возвращается значение из первого столбца таблицы; если номер_столбца = 2 — значение из второго столбца таблицы и т. д. В моем примере, возвращается значение из второго столбца из таблицы $A$1:$B$200.0;

• Функция ЕСЛИ. Синтаксис (лог_выражение;значение_если_истина;значение_если_ложь). В данном случае:

  • если значение недоступно, то ничего не указыват

  • если значение доступно и выполнилась функция ВПР, то записываем в ячейку полученное значение.

• Функция ЕНД. Возвращает ИСТИНА, если значение ссылается на значение ошибки #Н/Д (значение недоступно). Необходимо для проверки.

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

1. Берется первое значение в стоблце "А" листа 1

2. Происходит поиск во всем столбце "А" на листе 2

3. Если найдено совпадение, информация из строки на листе 2, дополняется в ячейку, где используется функция.

4. Если значений на листе 2, больше двух, то необходимо указать в функции правую границу поиска, т.е. крайний правый столбец, в моем примере это столбец "B", т.к. первое значение это ФИО, второе внутренний номер телефона(см. таблицу 1 и таблицу 2.)

5. Поиск будет производится до двухсотой строки (значение $B$200), но это значение можно конечно же увеличивать и уменьшать, в зависимости от количества записей в вашей таблицы.

Использование функции

Вставляем функцию в столбец "F" и растягиваем по всей длине списка.Получаем: Данный метод, я использовал для соединения таблиц, с количеством записей(строк) - 300, и столбцов - 10. При этом указанная формула использовалась для нескольких таблиц, т.е. 1ая - список ФИО + дни рождения, 2ая - список ФИО + телефоны, 3ий - список ФИО + должности + отделы + кабинеты.

Есть два sheet'a(Sheet1 & Sheet2). Каждый имеет таблицу, состоящую из из двух колонок - первая - числа, вторая - текст. Нужно сравнить 1й шит со 2м по принципу - если строка из двух колонок первого массива совпадает со строкой во втором(xi=xj, yi=yj) - то напротив каждой строки в двух шитах пишем "1", если же нет совпадений - то нолик. Пример: мас1(искомый) 11 Лена 1 12 Маша 0 13 Вика 1 14 Лера 1 15 Рита 0 мас2(проверяемый) 11 Лена 1 13 Вика 1 123 Маша 0 15 Рима 0 14 Лера 1 Третья строка - рез-т, который я должен получить.

Sub find()

Dim xi As Double

Dim xj As Double

Dim yi As String

Dim yj As String

Dim j As Double

Dim i As Double

Application.ScreenUpdating = False

Sheet1.Activate

i = 1

xi = Cells(i, 1).Value

yi = Cells(i, 2).Value

Do Until i = 11

Sheet2.Activate

j = 1

Do Until j = 10

xj = Cells(j, 1).Value

yj = Cells(j, 2).Value

If xj = xi And yj = yi Then

Sheet1.Activate

Cells(i, 3).Value = "1"

Sheet2.Activate

Cells(j, 3).Value = "1"

Sheet1.Activate

Else: j = j + 1

End If

Loop

i = i + 1

Loop

End Sub

Работа с массивами в Экселе Подскажите пожалуйста, знающие люди, следующее: 1) Дан массив из N<=30 натуральных чисел. Найти: а) наименьший элемент массива б) наиболее удаленный от среднего арифметического значения чисел массива. В А я так понимаю ф-я "=мин", в Б "=срзнач" а вот дальше хз. 2)Дана квадратная матрица. Найти: а)максимальный элемент по модулю, Б) его положение( номер строки & стольца). с этим вообще туго. Заранее спасибо

Первая задача.

Код Visual Basic

1 2 3 4 5 6 7 8 9 10 11

Sub sum_st() Cells.Clear Set Rng = Range(Cells(1, 1), Cells(1, 30)) Rng.Formula = "=int(rand()*10)+1" minn = Application.WorksheetFunction.Min(Rng) sr_ar = Application.WorksheetFunction.Average(Rng) maxx = Application.WorksheetFunction.Max(Rng) MsgBox "Мин. э-т=" & minn MsgBox "Среднее арифметическое=" & sr_ar MsgBox "Наиболее удаленный э-т от среднего арифметического=" & maxx End Sub

Добавлено через 28 минут Вторая задача.

Код Visual Basic

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Sub m_st() Cells.Clear Dim cel, rng As Range Dim n_r, n_c, min As Byte Dim n As Long n = InputBox("Введите размер матрицы") Set rng = Range(Cells(1, 1), Cells(n, n)) rng.Formula = "=int(rand()*10)-3" maxx = Abs(Application.WorksheetFunction.Max(rng)) For Each cel In rng If cel.Value = maxx Then n_r = cel.Row n_c = cel.Column End If Next cel MsgBox "max=" & maxx & "Строка=" & n_r & "Cтолбец=" & n_c End Sub

Сравнение соответствия двух таблиц Excel Теперь, используя функцию "ЕСЛИ", в ячейке R2 можно сравнить фамилию, находящуюся в базе данных сотрудников, и фамилию из списка с окладами, полученную в результате обработки. В ячейке R2 должна использоваться такая формула: =ЕСЛИ(Н2=Q2;ИСТИНА;ЛОЖЬ) Если фамилии в обоих списках совпадут, то в ячейке R11 появится значение "ИСТИНА", если не совпадут - значение "ЛОЖЬ". Чтобы определить, выполняются ли все три условия, применим логическую функцию И. Функция возвращает значение "ИСТИНА", если все аргументы имеют значение "ИСТИНА", и значение "ЛОЖЬ", если хотя бы один из аргументов имеет значение "ЛОЖЬ". Синтаксис функции таков: И(логическое_значение1; логическое_значение2; ...) где логическое_значение 1, логическое_значение2, ... - это проверяемые условия (их может быть от 1 до 30), которые имеют либо значение "ИСТИНА", либо значение "ЛОЖЬ". Аргументы функции должны быть логическими значениями, массивами или ссылками, содержащими логические значения. В нашем случае результаты сравнений находятся в ячейках N2, О2 и R2. Вызвав панель функции И (рис. 5.20), введите в качестве ее аргументов ссылки на эти ячейки. В ячейке S2 появится новая формула: =И(N2;O2;R2) Pис 5.20. Панель функции "И" При наличии трех значений "ИСТИНА", а такое возможно лишь при условии, что, во-первых, работник не уволился, во-вторых, табельные номера соответствуют друг другу, а в-третьих, фамилии в соответствующих строках одинаковы, функция И возвратит значение "ИСТИНА". Если хотя бы одно условие не выполняется и имеет значение "ЛОЖЬ", то и формула возвращает значение "ЛОЖЬ". На рис. 5.21 фрагмент рабочего листа Оклады показан с результатами сравнения таблиц, а на рис. 5.22 - с введенными формулами. Рис. 5.21. Результат сравнения соответствия двух рассматриваемых таблиц Рис. 5.22. Рабочий лист с формулами для определения соответствия двух таблиц

Обзор формул

Применимо к: Microsoft Office Excel 2007

Печать

Показать все

Скрыть все

Формулы представляют собой выражения, по которым выполняются вычисления на листе. Формула начинается со знака равенства (=). Ниже приведен пример формулы, умножающей 2 на 3 и прибавляющей к результату 5.

=5+2*3

Формула может также содержать такие элементы, как: функции (Функция. Стандартная формула, которая возвращает результат выполнения определенных действий над значениями, выступающими в качестве аргументов. Функции позволяют упростить формулы в ячейках листа, особенно, если они длинные или сложные.), ссылки, операторы (Оператор. Знак или символ, задающий тип вычисления в выражении. Существуют математические, логические операторы, операторы сравнения и ссылок.) и константы (Константа. Постоянное (не вычисляемое) значение. Например, число 210 и текст «Квартальная премия» являются константами. Выражение и результат вычисления выражения константами не являются.).

Элементы формулы

Функции: функция ПИ() возвращает значение числа пи (3,142...).

Ссылки: A2 возвращает значение, хранящееся в ячейке A2.

Константы: числовые или текстовые значения, вводимые непосредственно в формулу, например 2.

Операторы: оператор ^ (знак крышки) возводит число в степень, а оператор * (звездочка) умножает числа.

В этой статье:

• Использование констант в формулах

• Использование операторов в формулах

• Использование функций и вложенных функций в формулах

• Использование ссылок в формулах

• Использование имен в формулах

• Использование формул массива и констант массива

Использование констант в формулах

Константа представляет собой готовое (не вычисляемое) значение. Например, дата 09.10.2008, число 210 и текст «Прибыль за квартал» являются константами. Выражение, или его значение, константами не являются. Если в формуле в ячейках не содержится ссылок на другие ячейки (например, формула имеет вид =30+70+110), значение в такой ячейке изменяется только после изменения формулы вручную.

К началу страницы

Использование операторов в формулах

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

Типы операторов

В Microsoft Excel включено четыре вида операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.

Арифметические операторы

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

Арифметический оператор	Значение	Пример
+ (плюс)	Сложение	3+3
– (минус)	Вычитание Отрицание	3–1 –1
* (звездочка)	Умножение	3*3
/ (косая черта)	Деление	3/3
% (знак процента)	Процент	20%
^ (знак крышки)	Возведение в степень	3^2