Операции с массивами
Массив – это группа данных, которая расположена на листе в смежных ячейках. По большому счету, любую таблицу можно считать массивом, но не каждый из них является таблицей, так как он может являться просто диапазоном. По своей сущности такие области могут быть одномерными или двумерными (матрицы). В первом случае все данные располагаются только в одном столбце или строке.
Во втором — в нескольких одновременно.
Кроме того, среди одномерных массивов выделяют горизонтальный и вертикальный тип, в зависимости от того, что они собой представляют – строку или столбец.
Нужно отметить, что алгоритм работы с подобными диапазонами несколько отличается от более привычных операций с одиночными ячейками, хотя и общего между ними тоже много. Давайте рассмотрим нюансы подобных операций.
Создание формулы
Формула массива – это выражение, с помощью которого производится обработка диапазона с целью получения итогового результата, отображаемого цельным массивом или в одной ячейке. Например, для того, чтобы умножить один диапазон на второй применяют формулу по следующему шаблону:
Над диапазонами данных можно также выполнять операции сложения, вычитания, деления и другие арифметические действия.
Координаты массива имеют вид адресов первой её ячейки и последней, разделенные двоеточием. Если диапазон двумерный, то первая и последняя ячейки расположены по диагонали друг от друга. Например, адрес одномерного массива может быть таким: A2:A7.
А пример адреса двумерного диапазона выглядит следующим образом: A2:D7.
- Чтобы рассчитать подобную формулу, нужно выделить на листе область, в которую будет выводиться результат, и ввести в строку формул выражение для вычисления.
После ввода следует нажать не на кнопку Enter, как обычно, а набрать комбинацию клавиш Ctrl+Shift+Enter. После этого выражение в строке формул будет автоматически взято в фигурные скобки, а ячейки на листе будут заполнены данными, полученными в результате вычисления, в пределах всего выделенного диапазона.
Изменение содержимого массива
Если вы в дальнейшем попытаетесь удалить содержимое или изменить любую из ячеек, которая расположена в диапазоне, куда выводится результат, то ваше действие окончится неудачей. Также ничего не выйдет, если вы сделаете попытку отредактировать данные в строке функций. При этом появится информационное сообщение, в котором будет говориться, что нельзя изменять часть массива. Данное сообщение появится даже в том случае, если у вас не было цели производить какие-либо изменения, а вы просто случайно дважды щелкнули мышью по ячейке диапазона.
Если вы закроете, это сообщение, нажав на кнопку «OK», а потом попытаетесь переместить курсор с помощью мышки, или просто нажмете кнопку «Enter», то информационное сообщение появится опять. Не получится также закрыть окно программы или сохранить документ. Все время будет появляться это назойливое сообщение, которое блокирует любые действия. А выход из ситуации есть и он довольно прост
- Закройте информационное окно, нажав на кнопку «OK».
Затем нажмете на кнопку «Отмена», которая расположена в группе значков слева от строки формул, и представляет собой пиктограмму в виде крестика. Также можно нажать на кнопку Esc на клавиатуре. После любой из этих операций произойдет отмена действия, и вы сможете работать с листом так, как и прежде.
Но что делать, если действительно нужно удалить или изменить формулу массива? В этом случае следует выполнить нижеуказанные действия.
Для изменения формулы выделите курсором, зажав левую кнопку мыши, весь диапазон на листе, куда выводится результат
Это очень важно, так как если вы выделите только одну ячейку массива, то ничего не получится. Затем в строке формул проведите необходимую корректировку
После того, как изменения внесены, набираем комбинацию Ctrl+Shift+Esc. Формула будет изменена.
- Для удаления формулы массива нужно точно так же, как и в предыдущем случае, выделить курсором весь диапазон ячеек, в котором она находится. Затем нажать на кнопку Delete на клавиатуре.
После этого формула будет удалена со всей области. Теперь в неё можно будет вводить любые данные.
Как сделать в Экселе гиперссылку
Кроме обычных ссылок в Экселе есть еще такое понятие, как гиперссылка. Она работает таким же образом, как и на сайте: когда пользователь щелкнет на нее, откроется указанный ранее источник. Это может быть сайт в интернете, изображение или какой-то документ. С помощью этого можно автоматизировать рабочий процесс в Excel, открывая нужный сайт или документ всего одним щелчком мыши.
Существует 3 способа, как сделать в Экселе гиперссылку:
- напрямую в ячейку;
- с помощью объектов;
- с помощью функции.
Достаточно щелкнуть правой кнопкой мыши по любой ячейке и в появившемся списке выбрать пункт «Гиперссылка». Откроется новое окно, в котором необходимо указать адрес сайта или файл на компьютере. Также можно создать ее и через панель меню: «Вставка — Гиперссылка».
Теперь, когда вы знаете, как сделать простую гиперссылку, можно легко создать такую же ссылку с помощью объектов рабочего листа. К ним относятся любые изображения, надписи, фигуры, объекты WordArt, диаграммы и пр. Для этого нужно вызвать контекстное меню на одном из таких объектов и выбрать пункт «Гиперссылка». Кроме того, можно это сделать и с помощью комбинации клавиш — Ctrl+K.
Еще один вариант, как сделать гиперссылку, с помощью встроенной функции. Данная функция имеет следующий синтаксис: =Гиперссылка(адрес; ). Вызвать ее можно 2 способами:
- через панель меню «Вставка — Функция»;
- вручную.
Если воспользоваться первым способом, то сначала в появившемся окне необходимо будет найти функцию «Гиперссылка» (проще всего написать название функции в поле «Поиск функции»). После этого откроется еще одно окно, в котором надо будет заполнить 2 параметра — адрес и имя. Адрес — это местоположение ячейки, диапазона ячеек или объекта в документе. Здесь можно указать и страницу в интернете или путь к файлу на компьютере. А в поле «Имя» указывается наименование, которым будет подписано выбранное поле. Хотя заполнять его необязательно.
Можно сделать все это вручную, выделив необходимое поле и прописав: =гиперссылка(. После открытой скобки нужно будет указать адрес, поставить точку с запятой, указать имя гиперссылки, закрыть скобку и нажать «Enter». Например, можно написать в поле E1 такую формулу: =Гиперссылка(«yandex.ru»; «Открыть Яндекс»). В поле E1 будет написана фраза «Открыть Яндекс», щелкнув по которой вы откроете сайт в браузере.
Кроме того, существует еще один способ, как сделать гиперссылку, с помощью макросов. Но этот вариант слишком сложный: лучше использовать более простые варианты.
Статистические функции Excel
Как видно из названия, статистические функции предназначены для статистического анализа данных в таблицах Excel. Немалая часть таких функций используется для анализа вероятностей. Возможно, эти функции покажутся очень сложными, но и среди них есть несколько довольно простых функций, которые может использовать в своей работе даже рядовой пользователь.
- СРЗНАЧ. Функция предназначена для вычисления среднего значения в диапазонах ячеек. Причем, диапазонов может быть несколько. В расчетах могут использоваться и несмежные диапазоны и ячейки.
- СРЗНАЧЕСЛИ. Функция похожа на предыдущую, но значения для расчета среднего выбираются по указанному условию.
- МАКС. Находит максимальное значение в указанном диапазоне ячеек.
- НАИБОЛЬШИЙ. Находит указанное по величине значение из диапазона ячеек. Например, можно найти второе по величине значение или десятое по величине значение из списка.
- МОДА. Эта функция находит в указанном массиве данных максимально часто встречающееся значение.
Работа с массивами функций в Excel
(см. рисунок выше). Файл примера). вторая строка содержит одной строке (горизонтальный знать, что вычисление функций в Экселе
так как если окончится неудачей. Также от более привычных красиво и легко столбца, то надо а потом взять сразу несколько значений.
Виды массивов функций Excel
диапазона. с массивами функций данные не из поможет формула массива.Массив последовательных чисел будемСумму 3-х наибольших значений всего 4 элемента. массив) … обратной матрицы возможно
является вы выделите только
- ничего не выйдет, операций с одиночными выбираются данные по
- выделить диапазон пустых сумму по этому
Преобразует горизонтальную матрицу
- Если попытаться изменить данные в Excel.
- всех ячеек столбца,В ячейку А7
формировать с помощью можно подсчитать сЕсли попробовать ввести такой…или в одном столбце исключительно в том
СУММ одну ячейку массива, если вы сделаете ячейками, хотя и определенному товару и ячеек размером 2 столбцу. Если же
в вертикальную и в какой-либо ячейке
- Массив – данные, объединенные а из каждой
- пишем такую формулу. следующей формулы: СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список))) помощью формулы =СУММПРОИЗВ(НАИБОЛЬШИЙ(A2:A15;))
- массив на рабочий (вертикальный массив).
случае, если она. Её можно применять, то ничего не попытку отредактировать данные общего между ними заказчику:
Синтаксис формулы массива
применить формулу массива, наоборот. Выделяем диапазон столбца «К оплате» в группу. В второй или третьей, =СУММ(A1:A6+2)Здесь функция ЧСТРОК() возвращаетЧтобы не вводить массив лист, то ничего
- Двухмерный массив хранится в
- содержит равное количество как для суммирования
- получится. Затем в в строке функций. тоже много. ДавайтеВ данном случае формула
столбцов. то все будет ячеек, где количество — ничего не данном случае группой т.д. ячейки
ЗдесьВнимание!
количество значений в вручную, можно не получится. Excel диапазоне ячеек, его строк и столбцов, содержимого отдельных ячеек, строке формул проведите При этом появится рассмотрим нюансы подобных
массива синхронно пробегаетвводим функцию транспонирования =ТРАНСП( гораздо красивее: строк = числу выйдет. Формула в является массив функций
- нам поможет формулаКак ввести формулу массива
- исходном списке (9).
- использовать следующий подход. не позволит Вам еще называют матрицей.
и если её так и для необходимую корректировку. информационное сообщение, в операций. по всем элементам
выделяем ячейку столбцов в таблице массиве защищает значения в Excel. Любую массива. Смотрите статью в После выполнения функции
Введите в диапазон
Функции работы с массивами Excel
это сделать.В отличие от языка определитель не равен нахождения суммы целыхПосле того, как изменения котором будет говориться,Формула массива – это диапазонов C3:C21 ив качестве аргумента функцииС7 с исходными данными. диапазона от изменений. таблицу, которую мы «Как в Excel
- Excel. СТРОКА() получим массив ячеекВ качестве заполнителя отсутствующего программирования VBA, Excel нулю. Если применять массивов. Синтаксис этого внесены, набираем комбинацию
- что нельзя изменять выражение, с помощью B3:B21, проверяя, совпадают выделяем наш массиввводим с клавиатуры А количество столбцов На экране появляется составим и заполним сложить каждую вторуюТеперь, чтобы ввести последовательных чисел ,
- B1:B3 элемента в массиве не поддерживает трехмерные данную функцию к оператора для массивовCtrl+Shift+Esc часть массива. Данное которого производится обработка ли они с ячеек A1:B8=СУММ( = числу строк соответствующая запись: в Excel, можно ячейку, строку». эту формулу, как причем этот массивзначения 1, 2
- можно использовать значение массивы. области с разным выглядит следующим образом:. Формула будет изменена. сообщение появится даже диапазона с целью заданными значениями изжмемвыделяем диапазон
в исходном массиве.
Рассмотрим другие примеры использования назвать массивом. Пример:Пятый пример. формулу массива, нажимаем будет динамическим: в и 3 соответственно.#Н/ДРанее мы уже отмечали, количеством строк и=СУММ(массив1;массив2;…)Для удаления формулы массива в том случае, получения итогового результата,
1.7. Некоторые важные функции
Excel предоставляет широкий выбор стандартных
(встроенных) функций. Мы не можем рассмотреть их все, поэтому
остановимся только на тех, которые представляются нам самыми важными.
SUM/ СУММ
Суммирует все числа в
списке аргументов или в области.
Синтаксис:
SUM(number1
)
Пример
Рис.12
Функция SUM
SUMSQ / СУММКВ
Возвращает сумму квадратов аргументов.
Синтаксис:
SUMSQ(number1
)
Пример
Рис.13 Функция SUMSQ
SUMPRODUCT / СУММПРОИЗВ
Перемножает соответствующие элементы заданных массивов и
возвращает сумму произведений
Синтаксис:
SUMPRODUCT (array1,
array2, …)
Пример
Рис.14 Функция SUMPRODUCT
AVERAGE / СРЗНАЧ
Возвращает
своих аргументов
Синтаксис:
AVERAGE(number1
)
Пример
Рис.15 Функция AVERAGE
VAR / ДИСП
Оценивает по выборке .
Синтаксис:
VAR(number1
,number2,
…)
Пример
Рис.16 Функция VAR
STDEV / СТАНДОТКЛОН
Оценивает по выборке
.
Синтаксис:
STDEV(number1
,number2,
…)
Пример
Рис.17 Функция
STDEV
CORREL / КОРРЕЛ
Возвращает
между интервалами
ячеек array1 и array2
Синтаксис:
CORREL(array1,
array2)
Пример
Рис.17 Функция
CORREL
Функции можно объединять в
составные формулы, пример которой показан на Рис. 19.
Рис.19 Пример составной формулы
Пример: меняем местами строки и столбцы
Очень часто может появляться ситуация, когда нужно строки и колонки менять местами. Такая операция называется транспонированием. С помощью массивов функций это делать невероятно легко. Представим, у нас есть такой двумерный массив, расположенный по вертикали.
Нам нужно выделить диапазон данных, в котором будет размещаться готовая таблица. Поскольку в нашем примере 8 рядов и 2 колонки, то соответственно нужно выделять наоборот, 8 колонок и 2 ряда.
После этого надо ввести формулу = ТРАНСП , введя в качестве аргумента функции A1:B8.
После этого нажимаем вышеуказанную комбинацию клавиш для создания формулы массива, после чего получаем транспонированную таблицу.
Как отредактировать формулу массива
На рисунке ниже представлена формула массива, которая складывает значения двух диапазонов. Из рисунка видно, что при вводе формулы мы допустили небольшую ошибку, наша задача ее исправить.
Чтобы отредактировать формулу массива, выполните следующие действия:
- Выделите диапазон массива любым из известных Вам способов. В нашем случае это диапазон C1:C12.
- Перейдите в режим редактирования формулы, для этого щелкните по строке формул или нажмите клавишу F2. Excel удалит фигурные скобки вокруг формулы массива.
- Внесите необходимые корректировки в формулу:
- А затем нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы сохранить изменения. Формула будет отредактирована.
2.2. Создание и изменение формул массива
Для того чтобы правильно ввести формулу массива, нужно
выделить на листе область, размеры которой совпадают с ожидаемым
результатом.
Если выделить слишком большую область, то при вычислении
избыточные ячейки будут заполнены символами ошибки
#N/A. Если область вывода будет меньше, чем нужно, то часть
результатов пропадет. После выделения области, в
Formula Bar записывается
формула и нажимается CTRL+SHIFT+ENTER.
Альтернативно, сначала можно ввести формулу в одну
ячейку, затем отметить область вывода, начиная с этой ячейки (право и
вниз), потом перейти в Formula Bar и нажать
CTRL+SHIFT+ENTER.
Для того, чтобы изменить формулу массива нужно выделить
область содержащую результат. Затем нужно перейти
Formula Bar. При этом фигурные скобки вокруг формулы
{ } исчезнут. После этого формулу можно
изменить и нажать CTRL+SHIFT+ENTER.
Для того чтобы расширить область, которую занимает
формула массива, достаточно выделить для нее новую область, перейти в
Formula Bar и нажать CTRL+SHIFT+ENTER. А вот для того, чтобы уменьшить эту область
(например, чтобы избавиться от символов #N/A)
придется потратить больше сил. Сначала нужно встать на любую ячейку
области, перейти в Formula Bar и скопировать
строку формулы. Затем нужно стереть содержимое старой области и отметить
новую, меньшую область. После этого опять перейти в
Formula Bar, вставить формулу и нажать CTRL+SHIFT+ENTER.
Изменять отдельные ячейки в формуле массива нельзя. При
попытке сделать это появляется предупреждение .
Рис.27 Предупреждение о недопустимой операции с формулой массива
2.4. Доступ к частям матрицы
Для доступа и отделения частей матрицы применяются две
стандартные функции листа.
OFFSET / СМЕЩ
Возвращает ссылку на диапазон, отстоящий от ячейки или
диапазона ячеек на заданное число строк и столбцов.
Синтаксис
OFFSET(reference,
rows, cols )
Примечания
-
Если
аргументы height или width
опущены, то предполагается, что используется
такая же высота или ширина, как в аргументе
reference; -
Аргумент
reference – это ссылка на область,
которая должна быть реальным, а не виртуальным массивом, т.е.
находиться где-то на листе.
Пример
Рис.29 Функция OFFSET
OFFSET является функцией массива и ее ввод должен
завершаться нажатием комбинации CTRL+SHIFT+ENTER.
INDEX / ИНДЕКС
Возвращает значения элементов в массиве, выбранных с
помощью индексов строк и столбцов.
Синтаксис
INDEX (reference
)
Примечания
-
Если
аргумент row_num опущен, то выбирается
весь столбец; -
Если
аргумент col_num опущен, то выбирается вся строка; -
Если указаны
оба необязательных аргумента, то возвращается значение, находящееся
в ячейке на пересечении соответствующей строки и столбца; -
Аргумент
reference может быть ссылкой, как на
область, так и на виртуальный массив.
Пример
Рис.30 Функция INDEX
INDEX является функцией массива и ее ввод должен
завершаться нажатием комбинации CTRL+SHIFT+ENTER.
Строка поиска в Excel
«Область поиска». пространство только определенным
должны быть самостоятельными «Найти и заменить». помощь) денежном эквиваленте. возвращает наибольшее значениеМассив – массив данных лишь одну строкуЕсли команда возвращается значение. ВКапустаА
Теперь значение дляВ2.
- определяется, среди какихВ окне появляется целый диапазоном ячеек. элементами. Так, если Кроме того, вTimShaРезультат вычислений: из массива, которое в Excel (текстовые,
- этом примере значение(B7), и возвращаетна белый и «United States» составляет.Бывают случаи, когда нужно конкретно элементов производится ряд дополнительных инструментовВыделяем область ячеек, в в качестве запроса
приложении имеется возможность: Расширенный фильтр.То есть, клиент Иванов меньше либо равно числовые, логические), сравниваемый Соответственно, функция ПРОСМОТРнедоступна, необходимо загрузить возвращается из столбца значение в ячейке скройте столбцы 1,00006, а для=SEARCH($B$2,E4) произвести поиск не поиск. По умолчанию, для управления поиском. которой хотим произвести будет задано выражение - расширенного поиска данных.sboy обязан вернуть 127,2 искомому. Если бы с искомым значением. используется для поиска надстройка мастера подстановок. D C7 (С «United Kingdom» –=ПОИСК($B$2;E4) по конкретному словосочетанию, это формулы, то
- «прав», то вПростой поиск данных в: Вкладка Главная - денежных единиц. Подобным производился поиск, например, Функция ПРОСМОТР производит определенного значения в
Загрузка надстройки мастера подстановокПродажи100и 1,00009. Кроме этогоДважды кликните по маркеру а найти ячейки, есть те данные, эти инструменты находятсяНабираем на клавиатуре комбинацию выдаче будут представлены программе Excel позволяет Условное форматирование. методом производится расчет числа 10, в поиск в соответствии одной строке илиНажмите кнопку.).D мы добавили функцию автозаполнения, который находится в которых находятся которые при клике в состоянии, как клавиш все ячейки, которые найти все ячейки, - Подробнее с файлом-примером задолженности для остальных данном случае было с размерностями массива, одном столбце. Синтаксис:Microsoft Office
- .IFERROR в правом нижнем поисковые слова в по ячейке отображаются при обычном поиске,
Ctrl+F содержат данный последовательный в которых содержитсяakkord87 клиентов. бы возвращено значение то есть в=ПРОСМОТР(искомое_значение; просматриваемый_вектор; ), а затем —Для выполнения этой задачи разделах, посвященных функциямРезультат: Ваша собственная строка(ЕСЛИОШИБКА). Если ячейка углу ячейки любом порядке, даже, в строке формул. но при необходимости, после чего запуститься набор символов даже введенный в поисковое - : sboy, Извините, подготовилПример 3. В офисе 9,4, что не первой строке либоДва первых аргумента функции кнопку используется функция ГПР. ИНДЕКС и ПОИСКПОЗ. поиска в Excel. содержит ошибку, кD4 если их разделяют Это может быть
- внутри слова. Например, окно набор символов файл, а в работают 5 сотрудников является верным результатом первом столбце таблицы,
- должны быть обязательноПараметры Excel См. пример ниже.К началу страницыУрок подготовлен для Вас примеру, когда строка, чтобы быстро скопировать другие слова и слово, число или
По умолчанию, функции окно
релевантным запросу в (буквы, цифры, слова, тему вложить забыл:) различного возраста. Необходимо
(максимальное значение в
office-guru.ru>
Многомерные массивы
Массивы не ограничиваются одним измерением, однако они могут иметь максимум 60 измерений. Двумерными массивами являются наиболее часто используемые.
пример
В следующем примере многомерный массив объявляется с тремя строками и четырьмя столбцами.
Private Sub Constant_demo_Click()
Dim arr(2,3) as Variant ' Which has 3 rows and 4 columns
arr(0,0) = "Apple"
arr(0,1) = "Orange"
arr(0,2) = "Grapes"
arr(0,3) = "pineapple"
arr(1,0) = "cucumber"
arr(1,1) = "beans"
arr(1,2) = "carrot"
arr(1,3) = "tomato"
arr(2,0) = "potato"
arr(2,1) = "sandwitch"
arr(2,2) = "coffee"
arr(2,3) = "nuts"
msgbox("Value in Array index 0,1 : " & arr(0,1))
msgbox("Value in Array index 2,2 : " & arr(2,2))
End Sub
Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.
Простой пример использования массива для получения общего итога.
Приведем простой вариант использования в Excel массива. Это, который можно назвать классическим. Посмотрите на следующее изображение фрагмента листа:
Необходимо найти итоговую сумму
Обратите внимание, что графы, в которой бы содержалось значение суммы построчно для каждой позиции нет. Конечно, мы можем с помощью формулы посчитать общую стоимость для каждой строки в отдельном столбце, а затем применить к полученным результатам автосумму
Все это не будет ошибкой, и именно такие действия применят большинство из сотрудников организации.
Однако давайте внимательно посмотрим на итоговую формулу. В скобках указан диапазон, значения ячеек которого просуммированы. Что же находится в этих ячейках? Очевидно, что результаты умножения ячеек в текущей строке из столбцов «В» и «С» с ценой и количеством соответственно.
А теперь подумаем вот о чем. Диапазон с ценами – это массив? Безусловно, там же только числа. Аналогично и диапазон с количеством также является массивом. А если это массивы, то вместо того, чтобы попарно перемножать значения цены и находящегося в той же строки количества, можно применить Функцию «СУММ» непосредственно к произведению этих массивов!
В результате вместо трех действий – написание формулы, копирование ее вниз, применение новой формулы – мы использовали только одну формулу с тем же результатом. Мы сделали ту же работу, но гораздо быстрее.
Обратите внимание на фигурные скобки в строке формул. Они говорят о том, что перед нами не просто формула, а формула массива
Для ее ввода одновременно нажимаем комбинацию клавиш «Ctrl + Shift + Enter». Если просто нажать»Enter» то программа решит, что мы вводим обычную формулу. Она просто не поймет, как можно в ней ОДНОВРЕМЕННО перемножать несколько ячеек и даст ошибку. Поэтому будьте внимательны!