Всем привет, меня зовут Андрей!
Поскольку эта моя статья - первая в моем блоге, то вначале расскажу пару слов о самом блоге.
Мой блог прежде всего для тех, кто хочет лучше владеть электронными таблицами Microsoft Excel, а также для тех, кто интересуется математикой или решением судоку.
Если говорить про Excel – то начать я хочу с самых простых нюансов. Кстати, именно этим простым нюансам и посвящена эта статья.
Итак, начнем. Одна из самых главных, но в то же время и самых простых частей электронной таблицы Excel – это ссылка. Ссылка может быть как на одну ячейку, так и на диапазон ячеек.
Что же такое ссылка? Прежде всего, ссылка представляет собой адрес ячейки, который используется в вычислениях. Другими словами, ссылка – это часть формулы, содержащая адрес какой-либо ячейки или диапазона ячеек.
Приведем пример.
Пусть есть какой-то банк, продающий драгоценные металлы. Чаще всего большой популярностью пользуются 4 металла – это золото, серебро, палладий и платина. Как мы видим, банки обычно покупают металлы дороже, чем продают. Доход банка – это и есть разница между курсом продажи и курсом покупки. Если для некоторых ячеек в данной таблице формулы и, соответственно, ссылки не понадобятся вообще, то для ячеек E3, E4, E5 и E6 нам понадобятся и формулы, и ссылки. Если показать не значения ячеек, а их формулы, то получится следующее:
Итак, можно сказать следующее:
- во-первых, любая формула начинается со знака равенства (в нашем примере формулы есть только в ячейках E3, E4, E5 и E6; в остальных ячейках находятся или числа, или текст, про остальные типы данных будем рассказывать в других статьях);
- во-вторых, все формулы, приведенные в нашей таблице, содержат по две ссылки каждая;
- в-третьих, все формулы содержат именно «относительные ссылки» - то есть такие ссылки, которые при копировании запоминают свое положение относительно источника и при копировании изменяют свои координаты. Как это понимать? Разберем более подробно.
Итак, в ячейку в E3 мы вводим формулу:
=D3-C3
В данной формуле есть две ссылки: на ячейку D3 и на ячейку C3. То есть, в данной формуле имеется два источника. Таким образом – сама ячейка E3, в которой и находится наша формула, будет являться зависимой ячейкой. А влияющие ячейки – это ячейки D3 и C3. Итак, теперь более подробно рассмотрим фразу «относительная ссылка запоминает свое положение относительно источника и при копировании изменяет свои координаты».
«относительная ссылка» - в нашем примере это две ссылки: D3 и C3;
«источник» - это координата той ячейки, в которой осуществлялся ввод этой формулы, то есть E3;
«положение относительно источника» - это есть разница между номерами строк и столбцов зависимой и влияющей ячейки. То есть нужно мысленно преобразовать адрес ячейки, и тогда «E3» превратится в «R3C5», потому что R – сокращение от Row – строка, C – сокращение от Column – столбец. Тогда в нашем примере зависимые ячейки примут вид «R3C3» (C3) и «R3C4» (D3). И поскольку наша исходная формула состояла из разности двух значений, то и «положение относительно источника» - будет означать тоже разность: RC[-1] – RC[-2]. По сути, это то же самое, что «формула для E3: =D3-C3».
Почему именно так? Все просто – для ячейки E3 ячейка D3 будет называться именно «RC[-1]», потому, что номер строки у обеих ячеек одинаковый (разница в номере строк – ноль, поэтому после R нет ничего, в данной ситуации ноль можно опустить), а номер столбца у первой из влияющих ячеек на 1 меньше, или на минус 1 больше, чем у зависимой ячейки. Поэтому после C мы видим цифру -1. Аналогичная ситуации при сравнении ячеек E3 и C3 - для ячейки E3 ячейка C3 будет называться «RC[-2]».
Кстати, в Excel есть возможность отображения формул именно в таком виде. Если в меню «файл» выбрать подменю «параметры», а затем – «формулы», то можно выбрать именно такой стиль ссылок – R1C1:
Если у нас выбран стиль ссылок R1C1, то это означает не только специфичность в отображении формул, но и отображение цифр в заголовках и строк, и столбцов.
Как мы видим, курсор находится в ячейке R3C5 – это та самая ячейка E3 – и наша формула из =D3-C3 преобразилась в = RC[-1] – RC[-2], как и было сказано раньше.
Кстати, поскольку у нас еще не было присвоено ни одного имени, то в левом верхнем углу мы видим «R3C5» - это имя текущей ячейки – третья строка, пятый столбец. Если бы столбцы нумеровались в буквах, то там вместо R3C5 было бы E3.
Кстати, перевод любой формулы из одного вида в другой можно осуществить так же – поменяв стиль отображения формул.
А теперь разберем другие виды формул. Кроме относительных ссылок, есть абсолютные и смешанные. Поскольку мы уже разобрали стиль ссылок R1C1, то с него и начнем:
Как понимать смысл вновь созданного столбца? Очень просто - если остальные числа предполагали цену за 1 грамм, то шестой столбец - за 10 граммов. То есть 10 - не просто десятка, а количество граммов.
Здесь мы добавили один столбец – шестой. В ячейке R2C6 не формула, а просто число 10. Остальные формулы шестого столбца – это смесь абсолютной и смешанной ссылки. Так, в ячейке R3C6 мы видим формулу: =R2C6*RC5. Первая часть формулы – это абсолютная ссылка (ссылка на ту ячейку, что находится в 6 столбце и 2 строке, т.е. число 10); вторая часть – RC5 – это уже пример смешанной ссылки. Всегда берем пятый столбец (это и есть C5) и всегда ту же строку, что в зависимой ячейке. Строку с тем же номером, разность в номерах строк равна нулю (это и есть R перед C5). При копировании данной формулы отображение и абсолютной, и смешанной формулы останется прежним, но значения формул будут изменены. Хотя после того, как мы изменим стиль ячеек, убрав флажок с пункта "стиль ячеек R1C1", отображение только абсолютной ссылки останется неизменным при копировании формулы на другие ячейки.
Итак, если мы сменим формат отображения формул, то получим следующее:
Как видим, в формулах появился знак $ - доллара. Для Excel этот знак – что-то типа статуса неприкосновенности. Если этот знак стоит только перед символом столбца или номером строки – это значит, что ссылка будет смешанная. Если знак $ находится перед номером строки – это значит, что при копировании формулы именно этот номер строки останется без изменения. Если значок $ и перед номером строки, и перед номером столбца – тогда ссылка будет абсолютной, и при копировании формулы неизменным будет и номер строки, и номер столбца. В нашем примере мы видим, что все данные из столбца E будут умножаться на 10, то есть на то число, что находится в ячейке F2. Как только изменится число в ячейке F2 – автоматически изменятся все другие числа в столбце F, это правило действует всегда: изменение влияющей ячейки автоматически означает изменение всех зависимых ячеек.
Конечно же, одна и та же ячейка может быть одновременно и зависимой, и влияющей. Поэтому одна из главных задач – недопущение так называемых «циклических ссылок» - это ситуации, при которой ячейка влияет сама на себя. Вот простой пример:
Формула в ячейке B2: =B3+11;
Формула в ячейке B3: =B2+11;
Уже после этого появится сообщение о циклической ссылке.
Может быть и всего одна ячейка с циклической ссылкой. Если в ячейку B4 ввести формулу:
=B4*11,
то эта формула будет сама по себе циклическая ссылка. Очевидно, что для более сложных ситуаций может быть несколько десятков и даже больше ячеек, но в конце концов последнее звено этой цепи будет ссылаться на первое звено той же цепи, и получится циклическая ссылка.
Но не всегда упоминание внутри ячейки о ней самой будет приводить к циклической ссылке. Если, например, в ячейку B4 ввести формулу:
=СТРОКА(B4)*11,
то мы получим результат – число 44, потому что 4 * 11 это будет именно 44.
Аналогично, если в B4 ввести формулу:
=СТРОКА(B4)*СТОЛБЕЦ(B4),
то мы получим число 8, т.к. 2*4 это будет 8. Два - это цифровой код буквы B, поскольку это вторая буква многих алфавитов.
Кстати, для функций СТРОКА и СТОЛБЕЦ не обязательно писать адрес ячейки внутри скобок, если имеется ввиду строка или столбец текущей ячейки. Можно написать проще: СТРОКА()*СТОЛБЕЦ(). Но если мы в ячейке A1 хотим узнать номер столбца ячейки XY20, тогда адрес внутри скобок станет обязательным и формула будет такой:
=СТОЛБЕЦ(XY20).
И результат этой формулы – 649, так как столбец XY именно под этим номером.
