Как использовать имя массива при вводе формулы?
Цель работы
Познакомиться с работой пакета Excel, как инструмента для решения задач бухгалтерского учета. Научиться правильно задавать имена переменным, определять ссылки на ячейки, использовать функции при вводе формул, работать с массивами данных в Excel.
Что такое относительная ссылка на ячейку?
Ссылка Excel — это адреса ячеек в формуле, которые нужно посчитать. Бывает относительная ссылка Excel, абсолютная или смешанная. Указывая адрес ячейки в формуле, мы даем компьютеру ссылку на ячейку, которую нужно прибавить, умножить, т.д. Автоматически при написании формулы ставятся относительные ссылки.
Относительная ссылка Excel — когда при копировании и переносе формул в другое место, в формулах меняется адрес ячеек относительно нового места.
Копируем формулу из ячейки D9 
В формуле поменялись адреса ячеек относительно нового места. Относительные ссылки в формулах удобны тем, что, где необходимо ввести много однотипных формул при составлении таблицы, это ускоряет работу, скопировав эту формулу.
Что такое абсолютная ссылка на ячейку?
Абсолютная ссылка Excel. Для этого перед названием столбца и названием строки в формуле ставим знак «$». Например: относительная ссылка «A1» , а абсолютная ссылка «$A$1». Теперь при копировании этой ячейки с абсолютной ссылкой в другое место таблицы, адрес ячейки не изменится. Это нужно когда, например, добавили строки, столбцы в таблицу.
Изменить относительную ссылку на абсолютную можно просто. Выделим ячейку, в строке формул в конце формулы ставим курсор, можно выделить всю формулу и нажимаем на клавиатуре F4.
Если нажмем один раз, то и столбец, и строка в формуле станут абсолютными (перед их адресами будет стоять знак доллара,$).
Если еще раз нажмем F4, то будет знак доллара стоять только у буквы столбца — это уже смешанная ссылка.
Что такое массив ячеек?
Массивы в Excel используют для создания формул, которые возвращают некоторое множество результатов или оперируют множеством значений.
Перед вводом формулы массива надо выделить ячейку или диапазон ячеек, который будет содержать результаты. Если формула возвращает несколько значений, необходимо выделить диапазон такого же размера и формы, как диапазон с исходными данными.
Нажмите клавиши Ctrl+Shift+Enter для фиксации ввода формулы массива. При этом Excel заключит формулу в фигурные скобки в строке формул. НЕ ВВОДИТЕ ФИГУРНЫЕ СКОБКИ ВРУЧНУЮ!
В диапазоне нельзя изменять, очищать или перемещать отдельные ячейки, а также вставлять или удалять ячейки. Все ячейки в диапазоне массива надо рассматривать как единое целое и редактировать все их сразу.
Для изменения или очистки массива надо выделить весь массив и активизировать строку формул. После изменения формулы надо нажать комбинацию клавиш Ctrl+Shift+Enter.
Чтобы переместить содержимое диапазона массива, надо выделить весь массив и в меню "Правка" выбрать команду "Вырезать". Затем выделите новый диапазон и в меню "Правка" выберите команду "Вставить".
Вырезать, очищать или редактировать часть массива не разрешается, но можно назначать разные форматы отдельным ячейкам в массиве.
Как задать имя массива?
Чаще гораздо удобнее работать с ячейками или массивами ячеек, когда им присвоены имена, особенно если они находятся на разных листах рабочей книги, то бывает легче от руки вписать имя в формулу, чем выделять их каждый раз на других листах.
Чтобы присвоить имя в Excel 2003, выделяем одну или несколько (массив) ячеек, заходим в меню Вставка > Имя > Присвоить. и в появившемся окне вводим имя (обязательно без пробелов).
Чтобы присвоить имя в Excel 2007 или Excel 2010, нужно на вкладке "Формулы" выбрать "Присвоить имя".
Как использовать имя массива при вводе формулы?
Массивы и формулы массива в Excel.
Для начала следует понимать, что такое массив и какие массивы бывают.
Массивом называют группу данных объединенных (сгруппированных) в одну структуру (группу)
В «Excel» массивы подразделяют на три типа в зависимости от структуры расположения данных в таблице:
Горизонтальный одномерный (линейный) массив – массив, в котором данные расположены горизонтально в одну строку.
Вертикальный одномерный (линейный) массив – массив, в котором данные расположены вертикально в один столбец.
Двумерный массив или матрица представляет собой таблицу прямоугольной формы, состоящую из нескольких строк и столбцов.
Формулы (функции) массивов.
Для работы с массивами в «Эксель» предусмотрены специальные формулы – формулы массивов.
Формулы массивов в свою очередь классифицируются на формулы, которые выводят (рассчитывают) единичный результат, и формулы которые рассчитывают и выдают результат в виде массива (матрицы).
Для расчета данных в массивах могут применяться и обычные функции. При нажатии клавиш Ctrl + Shift + Enter обычная формула выделяется фигурными скобками и становится формулой массива.
Рассмотрим в качестве примера смету состоящую из столбцов «Количество», «Трудозатраты на единицу», «Стоимость одного чел часа».
| Смета на проведение работ в Excel. | |||
| Вид работ | Количество | Трудозатраты на единицу | Стоимость одного чел часа |
| Прокладка кабеля, м | 25 | 0,5 | 123 |
| Укладка тротуарной плитки, м² | 45 | 0,125 | 244 |
| Покраска металлических поверхностей, м² | 4 | 0,2 | 233 |
| Монтаж дверного замка, шт. | 12 | 0,8 | 40 |
| Итого: | 3480,4 | ||
Чтобы получить полную стоимость работ следует перемножить количество, стоимость и трудозатраты на единицы для каждого вида работ, а потом сложить затраты на каждый вид работ. Сделать это можно в несколько действий по порядку, а можно написать одну единственную функцию массива: и нажать сочетание клавиш «Ctrl + Shift + Enter», чтобы «Excel» распознал формулу массив.
Рассмотрим функцию массива ТРАНСП(). Эта функция полностью относится к функциям массива и производить транспонирование выделенного массива, то есть меняет местами столбцы и строки (переворачивает таблицу). Чтобы использовать данную функцию следует:
- Выделить диапазон, в который планируете транспонировать таблицу (если в исходной таблице четыре столбца и шесть строк, то выделяем шесть столбцов и четыре строки);
- В строке функций пишем =ТРАНСП();
- В скобках указать массив, который вы хотите транспонировать (перевернуть) и нажать клавишу «ENTER».