неудобная фича в Экселе

[bgmt]

Проверяя работы, я обнаружил, что Эксель (у меня 2013, но, вероятно, любой) имеет фичу - вряд ли это баг - незнание которой может привести к серьёзным ошибкам в вычислениях.

Всем известно, что бывают адреса относительные и абсолютные, и абсолютный получается когда либо ставишь доллары ($A$1 вместо A1), либо даёшь ячейке имя.

Это очень удобно, когда ты этим пользуешься сознательно.

Но вот простейший пример, когда из-за нечаянного наличия у ячейки имени получается чушь. (А нечаянное наличие имени встречается часто, когда файл много раз правился и менялся).





В левых столбцах я написал какие-то числа в колонках А и B, в ячейке С1 создал формулу =A1+B1 (кликая на A1 и B1), потом кликнул на крестик в нижнем правом углу ячейки С1, формула скопировалась вниз, все ссылки относительные, на каждой строчке получается сумма чисел, стоящих слева.

В правых столбцах я провёл абсолютно ту же операцию. А результат, как видите, другой. В столбце Н к каждому числу из столбца F прибавилась единица - которая содержится в F1. А я ровно ничего для этого не делал.

Выясняется, что когда ты кликаешь на ячейку, у которой есть имя - а у F1 было имя coucou - создаётся формула не =F1+G1, как хочет пользователь, а =coucou+G1. Т.е. кликнутая ячейка проявляется только как абсолютный адрес. И если ты проворонишь  появление coucou+G1 сверху, в строчке формул, ты будешь думать одно, а Эксель будет делать другое.

Т.е. в Name Manager надо смотреть чаще, чем я предполагал. На всякий такой случай.

Может быть, конечно, это я один такой тупой, а все это давно знают. Но я вот не знал.

Comments

[shultz-flory.livejournal.com]

Я никогда не переименовываю ячейки, зачем?

[bgmt.livejournal.com]

Я не уверен, что правильно вас понял. Я не про ПЕРЕименование, а про НАименование. Длинные формулы гораздо легче читать, если там не стоят сппошные адреса, а вместо них - когда нужно - стоят имена ячеек или -не знаю, как оно зовётся по-русски - arrays. Особенно если таблица большая, и что такое, скажем, АА15, просто не видно, когда видишь этот адрес в формуле. Возможность присваивать имена вместо адресов - прекрасна, никаких претензий. Претензии у меня к выбору сценария кликания по умолчанию.

[shultz-flory.livejournal.com]

Я имел в виду наименование (т.е. по дефолту там имя F1, переписываем его на coucou). Наверное, читать формулы действительно удобней, если ячейки поименованы. Но ведь формулы обычно распространяют на строку (столбец), что делать с именами в этом случае?

[bgmt.livejournal.com]

Это чрезвычайно просто. Берём пример левых колонок. Там мне не нужны имена ячеек, потому что все ссылки относительные. Значит, я вместо того могу назвать массив данных в столбце А, ну скажем, pervoe, а в В - vtoroe. (Выбираю, скажем, от А1 до А20, и в name box пишу pervoe и нажимаю Энтер). Тогда нужная мне формула запишется =pervoe+vtoroe, и в таком виде она прекрасно скопируется с тем же результатом, что сейчас. А имена ячеек удобно употреблять для коэффициентов и прочего всякого, которое не должно "скользить вниз". Скажем, помещаю в D1 число, называю ячейку coef, и пишу =coef*(pervoe+vtoroe). Употребление coef будет неотличимо от употребления $D$1.

[shultz-flory.livejournal.com]

Спасибо, попробую при случае.
Я так думаю, это хорошо для файлов, которые в дальнейшем не редактируются.

[(Anonymous)]

array - по-русски "массив"

[skuzn.livejournal.com]

А как там присваивать имя ячейке, кстати? Я никогда этого не делал

[bgmt.livejournal.com]

Слева сверху, на той же строчке, что формулы, есть маленькое окошко. Оно называется name box. Обычно в нём виден адрес выбираемой ячейки. Но если ты там напишешь слово без пробелов, которое не может быть адресом (т.е.AF123 нельзя, а AF_123 можно) и нажмёшь на Enter, этой ячейке будет присвоено это имя. То же самое можно делать с массивом. Чтобы избавиться от лишних имён или их отредактировать, есть Name Manager в тэге Formulas. Там всё ясно. Там же можно их и создавать, но это неудобно.

[son-de-la-voix.livejournal.com]

В экселе действительно есть относительные и абсолютные адреса. Но это не совсем то, о чем вы тут пишете. Относительные адреса проявляются в виде смещения относительно заданной ячейки. А тут выше идет речь про конретную ячейку А1.

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

[bgmt.livejournal.com]

Извините, но это чушь. Доллар не стоит "с одной стороны" или "с другой стороны". И автозаполнение тут ни при чём, речь идёт (и у вас тоже) о копировании формулы. "Потянуть" - значит скопировать вниз или направо, смотря куда тянешь. Что до долларов, то они бывают только ПЕРЕД. $A$2 значит ячейка А2, где бы ты ни был. А2 значит "одной ячейкой левее", если ты в А3, "одной ячейкой ниже и двумя левее", если ты в С2 и т.д. Подробнее, $A$2 означает - первый доллар - что А, это настоящщее А, а не просто "на столбец левее если ты в В"; второй доллар, стоящий НЕ ПОСЛЕ А, А ПЕРЕД 2!!!- что это настоящее 2, а не ...
Далее. Да, совершенно логично, что если в формуле есть поименованное поле, что эквивалентно абсолютному адресу, то оно таким и останется. А вот что, кликнув на ячейку, ты получаешь в формуле её относительный адрес, если она не поименована, и абсолютный (закодированный как имя), если она поименована - нет, это нелогично и неудобно.

И поверьте, я экселем пользуюсь в достаточно сложной форме столько лет...

PS Что до того, что в Экселе называется автозаполнением, вот, пожалуйста, что об этом думает Микрософт:
https://support.office.com/en-us/article/Use-AutoFill-and-Flash-Fill-2e79a709-c814-4b27-8bc2-c4dc84d49464

Это не то, о чём вы говорите.

[son-de-la-voix.livejournal.com]

Столько ненависти... Был бы отличный комментарий, если бы не это ваше "извините, но это чушь".

А насчет автозаполнения, вот что думает на эту тему Микрософт:
https://support.office.com/ru-ru/article/%D0%97%D0%B0%D0%BF%D0%BE%D0%BB%D0%BD%D0%B5%D0%BD%D0%B8%D0%B5-%D1%84%D0%BE%D1%80%D0%BC%D1%83%D0%BB%D0%BE%D0%B9-%D1%81%D0%BC%D0%B5%D0%B6%D0%BD%D1%8B%D1%85-%D1%8F%D1%87%D0%B5%D0%B5%D0%BA-041edfe2-05bc-40e6-b933-ef48c3f308c6

[bgmt.livejournal.com]

Прошу прощения, никакой ненависти я сюда вложить не хотел, а "чушь" говорю часто, как и мне говорят. Постараюсь запомнить, что вы на такое не согласны. (Я знаю людей, для которых "ты сволочь" пустяшная разговорная форма, характеризует неприязнь к конкретному поступку и всё, и таких, которые обидятся насмерть).
Спор, собственно, был не об автозаполнении, потому что это чистая терминология, а о роли знака доллара. Тут я стою мёртво, знак доллара влияет на последующий элемент адреса, а не на предыдущий. Что до автозаполнения, то я вижу некоторое противоречие в собственной микрософтовской терминологии (прочтя вашу ссылку). Пытаясь разобраться, я вижу следующее: операция, которая нам нужна, это копирование формул из одной ячейки в другие. Это - не автозаполнение. (Это можно сделать путём контрол-С - контрол-V). Это можно сделать и перетаскиванием. По неизвестным мне причинам дополнительный диалог при использовании этих двух способов - разный. Если пейстить, то предлагаются разные способы пейстинга, и ни о каком автозаполнении и речи нет. А если перетаскивать, то предлагаются те же опции, что при автозаполнении. По-видимому, причины исторические, и эти две операции разрабатывались разными отделами (это не только тут так). Та, которая ввела перетаскивание (которого не было в ранних версиях), решила, что это форма автозаполнения (которое определено как автоматическое появление в ячейке чего-то, основываясь на значениях соседних ячеек). А та, которая давным-давно ввела копи-пейст, ни о каком автозаполнении не думала. А операция-то по сути одна и та же. Я предпочёл бы считать эти два варианта действий просто вариантами, и, соответственно, не говорить об автозаполнении. Дополнительный аргумент: я не вижу ситуации, когда в появляющемся диалоге кто бы то ни было выбрал, вместо опции "copy cells", одну из других предлагаемых: никто, как мне кажется, не копирует формат таким образом, все это делают через format painter (кисточку). Собственно, никто вообще не смотрит в эти опции, все берут дефолтную, которая не отличается от контрол-С - контрол-V.

Ещё раз извините.

[ny-quant.livejournal.com]

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

[green-fr.livejournal.com]

Не в менеджер имён надо смотреть, а на формулу, которую ты "протягиваешь". Твой "баг" ничем не отличается от того, если бы ты протянул "=$F$1+G1". Просто имя - это абсолютная ссылка. Ты протянул абсолютную ссылку. И абсолютная ссылка, и имя просто так в формуле не появляется, нужно сделать дополнительное усилие - нажать на F4 (или ручками проставить доллары) в одном случае, и написать имя в другом. Вполне разумное, как мне кажется, поведение Excel, вот так вот с ходу не могу придумать, как можно было бы сделать лучше...

[bgmt.livejournal.com]

Мне кажется, что ты не прочёл, что я написал. Я написал именно, что имя (в отличие от $F$1) появляется САМО, достаточно, чтобы у F1 было наименование. Оно появляется при кликанье на эту ячейку. Дальше всё как ты говоришь, но дело в том, что никто не смотрит непрерывно на формулу, когда ты её создаёшь путём простых кликов и знаков арифметических операций. На F4 нужно нажать для абслолютной ссылки, когда у ячейки имени нет. А когда есть, нет, оказывается, не нужно. И мне это совсем не нравится.

А то поведение, которое ты описываешь, и которого нет, действительно, оптимально.

[green-fr.livejournal.com]

Ой, да, ты прав. Попробовал - действительно грабли! Никогда не нарывался раньше, спасибо, что предупредил :-)