Ссылки на другие листы или рабочие книги

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

=Лист1!$D$10

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

='E:\Документы\[Бюджет на 2010 год.xlsx]Лист1'!$D$10

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

Трёхмерные ссылки

Трёхмерные ссылки – это ссылки на одну и ту же ячейку или на один и тот же диапазон ячеек, расположенные на нескольких листах одной книги, например, Лист1:Лист3!A1 или Лист1:Лист3!A1:E6.

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

Одна из самых существенных возможностей приложения Microsoft Excel – это назначение содержательных имён самым разным объектам. Имена можно присваивать ячейкам, диапазонам ячеек, строкам, столбцам, диаграммам, а также константам и формулам.

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

Присвоение имён ячейкам и диапазонам

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

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

Пересечение имён

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

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

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

Присвоением имён формулам

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

 

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

Массив – это коллекция ячеек или значений, которой управляют как единым целым. Формулой массива является формула специального вида, которая обрабатывает массивы данных. Результатом вычисления формулы массива может быть как единственный результат, так и набор значений. Обратите внимание, что для вставки формулы массива необходимо нажать не просто клавишу Ввод, а сочетание клавиш Ctrl + Shift + Ввод. Кроме того, в строке формул формула массива записывается в фигурных скобках. Изменить часть массива, полученного в результате вычисления формулы массива, невозможно.

Подсчёт и суммирование

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

=СЧЁТ(A1:A11)

=СЧЁТЕСЛИ(A1:A11;">0")

=СЧЁТЕСЛИМН(A1:A11;">0";A1:A11;"<=3")

=СУММ(A1:A11)

=СУММЕСЛИ(A1:A11;"<0")

=СУММЕСЛИ(A1:A11;"<0";B1:B11)