При создании формул в MS Excel используются ссылки на ячейки и диапазоны листа. Немногие пользователи знают, как использовать гибкие возможности Excel для ввода ссылок.
Стили ссылок Ссылки в Excel бывают двух стилей: Ссылка стиля А1 использует буквенную нумерацию столбцов и числовую нумерацию строк. Ниже приведены примеры ссылок стиля А1 из справочной системы Excel: Ячейка или диапазон | Ссылка |
|---|
| Ячейку в столбце A и строке 10 | A10 | | Диапазон ячеек: столбец А, строки 10-20. | A10:A20 | | Диапазон ячеек: строка 15, столбцы B-E. | B15:E15 | | Все ячейки в строке 5. | 5:5 | | Все ячейки в строках с 5 по 10. | 5:10 | | Все ячейки в столбце H. | H:H | | Все ячейки в столбцах с H по J. | H:J | | Диапазон ячеек: столбцы А-E, строки 10-20. | A10:E20 |
В стиле R1C1 и строки (rows), и столбцы (columns) обозначаются номерами. Например, R2C2 - абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце. Включение режима ссылок стиля R1C1 происходит в меню Сервис - Параметры - Общие (галочка Стиль ссылок R1C1). Смысл использования этого непривычного способа записи ссылок станет понятен, когда мы разберемся с относительными и абсолютными ссылками. Абсолютные и относительные ссылкиВ зависимости от поведения при копировании ячеек различают абсолютные, относительные и смешанные ссылки. Относительные ссылки при копировании ячейки будут автоматически корректироваться, "сдвигаться" относительно ячейки так, что смещение влияющей ячейки (той, на которую мы ссылаемся) относительно зависимой (той, в которую введена формула сос ссылкой) останется прежним. Например, в ячейку А1 введена формула =В1*2. При копировании формулы в ячейку А2 она будет автоматически откорректирована на формулу =В2*2. Как в первом, так и во втором случае, ссылка смещена относительно ячейки с формулой на одну ячейку вправо и находится в той же строке. Если мы скопируем формулу в ячейку С1, формула превратится в =D1*2. Теперь корректировка коснулась столбца, а смещение осталось прежним. Абсолютные ссылки при копировании не корректируются. Формула из нашего примера в абсолютной нотации будет выглядеть так: =$B$1*2. Теперь, куда бы мы ни копировали формулу, она все равно будет ссылаться на ячейку В1. Вводить абсолютные ссылки просто - при вводе формулы после ввода ссылки (обычно это делается мышью - Вы открываете формулу знаком "=" и щелкаете мышью нужную ячейку. В формуле появляется относительная ссылка) нужно нажать клавишу F4. Ссылка преобразуется в абсолютную. F4 - "закрепить" ссылку, преобразовать ее в абсолютную нотацию Повторное нажатие F4 преобразует ссылку в смешанную. Преобразования происходят в такой последовательности: А1 → $A$1 → $A1 → A$1 → A1 и дальше по кругу. Вы уже наверняка догадались, что у смешанных ссылок "закреплены" либо столбец, либо строка, в зависимости от того, перед чем стоит знак доллара. Изменить тип ссылки можно в любой момент - просто выделите в строке формул нужную ссылку и нажмите F4. При использовании стиля ссылок R1C1 относительные ссылки записываются как R[1]C[1], где в квадратных скобках записано относительное смещение влияющей ячейки соответственно по вертикали и по горизонтали. R[1]C означает ссылку на ячейку на одну строку ниже в том же столбце. RC[-1] - ячейка на один столбец левее в той же строке. При копировании формула в другой ячейке останется неизменной, но ссылаться будет на другую, "смещенную", ячейку. R1C1 - пример абсолютной ссылки. Если числа даны без квадратных скобок, они обозначают абсолютный номер строки. Стиль R1C1 записи гораздо удобнее, например, если вы работаете с макросами и используете циклы обхода ячеек. Зачем нужны абсолютные и смешанные ссылки?Разберем пример. Допустим, нам нужно проанализировать структуру продаж за два года. Данные по продажам представлены в таблице следующего вида | A | B | C | D | E | 1 | Наименование | Объем | Доля, % | 2 | 2005 г. | 2006 г. | 2005 г. | 2006 г. | 3 | Продукт 1 | 150 | 120 | 13,6 | 15,1 | 4 | Продукт 2 | 155 | 160 | 14,0 | 20,1 | 5 | Продукт 3 | 120 | 110 | 10,9 | 13,8 | 6 | Продукт 4 | 10 | 50 | 0,9 | 6,3 | ... | ... | ... | ... | ... | ... | 100 | Продукт 98 | 655 | 200 | 59,3 | 25,2 | 101 | Продукт 99 | 15 | 155 | 1,4 | 19,5 | 102 | ИТОГО | 1105 | 795 | 100,0 | 100,0 |
Как рассчитать долю каждого продукта в выручке? В ячейку D3 вводим формулу =B3/B$102% Теперь достаточно скопировать формулу в столбец E и все строки таблицы. Трехмерные ссылки Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно. Трехмерные ссылки могут быть использованы для создания ссылок на ячейки на других листах, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, ДИСП, ДИСПА, ДИСПР и ДИСПРА. Операторы ссылки| Оператор ссылки | Значение (пример) |
|---|
| : (двоеточие) | Ставится между ссылками на первую и последнюю ячейки диапазона. Такое сочетание является ссылкой на диапазон (B5:B15) | | ; (точка с запятой) | Оператор объединения. Объединяет несколько ссылок в одну ссылку (СУММ(B5:B15;D5:D15)) | | (пробел) | Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8). (Также возможно неявное пересечение - ссылка на диапазон ячеек вместо одной ячейки, из которого в расчете выбирается одна соответствующая ячейка. Например, если в ячейке C10 содержится формула =B5:B15*5, на 5 будет умножено значение из ячейки B10, потому что ячейки B10 и C10 находятся в одной строке.) |
{mos_sb_discuss:2} |
