Мои вопросы к Уокенбаху

[Олег*]

Добрый день!

Начал изучать труды Уокенбаха.
Решил начать с глубокого изучения его "Библии" для 2003, потом буду "грызть"   его "Профессиональное программирование на VBA в Excel 2003".
В этой теме буду задавать вопросы Уокенбаху, адресуя их форуму, поскольку Уокенбах далеко, а форум близко

Думаю, что чтение этой темы будет полезно всем новичкам Экселя, которые рано или поздно сталкиваются с "однотипными" проблемами.

Итак, изучая труды мистера Уокенбаха, заинтересовался следующим вопросом. Можно ли использовать в формулах массивов ссылки на фрагменты поименнованных диапазонов, не используя при этом функцию ИНДЕКС, поскольку с ней получается очень громоздко?

В прилагаемом примере имеется поименнованный диапазон МойДиап (A1:B3). Допустим, я хочу вычислить сумму построчных произведений этого диапазона, используя при этом ф-лу массива.

1.Если вообще без имен обойтись, тогда это будет выглядеть вот так:

Код Выделить Развернуть

{=СУММ(A1:A3*B1:B3)}


2.Если присвоить имена столбцам этого диапазона (Столб1 и Столб2), тогда это будет выглядеть вот так:

Код Выделить Развернуть

{=СУММ(Столб1*Столб2)}

Достоинство: очень лаконично.
Недостаток: надо присваивать "лишние" имена, в которых рано или поздно можно запутаться

3.Если использовать функцию ИНДЕКС, тогда можно получить тот же самый результат вот так:

Код Выделить Развернуть

{=СУММ(ИНДЕКС(МойДиап;0;1)*ИНДЕКС(МойДиап;0;2))}

В этом варианте меня устраивает все кроме того, что получается очень "громоздко" по сравнению со вторым вариантом. Может быть можно сделать то же самое более лаконично, используя ту же самую функцию ИНДЕКС, или есть какие-нибудь способы вообще обойтись без нее?

P.S. Пример взят вообще не из Уокенбаха, просто "навеяно им"
На самом деле, мне не нравится, как он построил свою книгу. Сначала все гладко идет, потом начинает то и дело то тут то там использовать довольно сложные формулы массивов, упоминая при этом, что они будут рассматриваться лишь в 14-15-ой главе. Добравшись до 10-11-ой главы, я понял, что ни хрена не понимаю, в голове получается "каша". Пришлось бросить все и "перескочив" сразу через несколько глав, заняться изучением 14-ой и 15-ой.
Ну кто ж так делает-то?! Надо или в самом начале давать какие-нибудь элементарные сведения по этой теме, а уже потом использовать этот материал для примеров, либо уж вообще их не использовать до поры до времени. Короче говоря, минус ему в репу!!!

[Serge 007]

...Может быть можно сделать то же самое более лаконично, используя ту же самую функцию ИНДЕКС, или есть какие-нибудь способы вообще обойтись без нее?..

Способов есть много, но так или иначе они все подразумевают указание перемножаемых диапазонов, так что применив вместо ИНДЕКС ДВССЫЛ, СМЕЩ или МУМНОЖ например, формула для Вас всё-равно будет "громоздкой" и если цель избавится от этого, то думаю Вы её не достигнете

Как вариант для понимания:

Код Выделить Развернуть

=СУММ(МУМНОЖ(МойДиап*{0;1};{1:1})*МойДиап*{1;0})

[_Boroda_]

А такой вариант?

Код Выделить Развернуть

=СУММ((МойДиап A:A)*(МойДиап B:B))
или, если в С1:С3 ничего нет, то

Код Выделить Развернуть

=СУММ(СМЕЩ(МойДиап;;1)*МойДиап)

[Serge 007]

А такой вариант?

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

[Олег*]

А такой вариант?

Код Выделить Развернуть

=СУММ((МойДиап A:A)*(МойДиап B:B))

На самом деле, мне этот вариант понравился больше всех предложенных, включая и мой собственный.
Просто как все гениальное! И без "лишних" формул.  Беру на вооружение!

[Serge 007]

...этот вариант понравился больше всех предложенны...

Олег*, а чем Сашин вариант по сути отличается от например Вашего

Код Выделить Развернуть

=СУММ(Столб1*Столб2)?
Вы писали:

Код Выделить Развернуть

Недостаток: надо присваивать "лишние" имена, в которых рано или поздно можно запутаться
Но ведь и в варианте

Код Выделить Развернуть

=СУММ((МойДиап A:A)*(МойДиап B:B)) тоже придётся присваивать "лишние" имена, если  МойДиап необходимо будет изменить и ссылаться он будет не на столбцы А и В. Я думал что Вы ищете универсальное решение, которое будет перемножать первый и второй столбцы именованого диапазона, независимо от того в каких столбцах именнно он располагается, иначе какой тогда вообще смысл в имени МойДиап?

[Олег*]

Я думал что Вы ищете универсальное решение, которое ...

Сейчас попробую объяснить, хотя заранее понимаю, что это будет нелегко

Все дело в том, что мы смотрим на мир Экселя разными глазами   Вы смотрите с точки зрения закоренелого профессионала, а я с точки зрения начинающего любителя.
Мне сейчас не нужно найти универсальное решение, мне нужно научиться обращаться с формулами массивов, раз уж Уокенбах использует их для примеров с самого начала своей книги, а объяснять, что это такое и с чем это едят взялся только в 14-ой главе (т.е на 285-ой странице 700-страничной книги).

А чтобы в чем-то разобраться, надо (по возможности) отбросить все лишнее-сложное и оставить только то, в чем именно хочешь разобраться. Ведь трудно решать уравнение, в котором сразу очень много неизвестных

Кроме того, может быть я неправильно сформулировал в топике то, что хотел услышать:

Можно ли использовать в формулах массивов ссылки на фрагменты поименнованных диапазонов, не используя при этом функцию ИНДЕКС, поскольку с ней получается очень громоздко?

На самом деле, под словом "громоздко" я подразумевал не "много лишней писанины", а скорее "много для меня непонятного".

Но ведь и в варианте

Код Выделить Развернуть

=СУММ((МойДиап A:A)*(МойДиап B:B)) тоже придётся присваивать "лишние" имена, если  МойДиап необходимо будет изменить и ссылаться он будет не на столбцы А и В. Я думал что Вы ищете универсальное решение, которое будет перемножать первый и второй столбцы именованого диапазона, независимо от того в каких столбцах именнно он располагается, иначе какой тогда вообще смысл в имени МойДиап?

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

Так вот... в этой книге УЖЕ есть саморасширяющийся (с помощью СМЕЩ + СЧЁТЗ) именованный диапазон, данные в который транслируются с помощью макроса. И принцип, приблизительно, как в приложенном к этой теме примере с той лишь разницей, что построчные результаты вычисляются не с помощью простого умножения, а с помощью довольно сложной комбинации функций ЕСЛИ. Столбцы этого диапазона (а их там 11 штук) не поименованы, но они вполне статичны, я их никуда перемещать не собираюсь, да и весь диапазон никуда не перемещается и по горизонтали не расширяется (только по вертикали).
Поэтому именно вариант, который предложил Александр, мне сразу понравился. Пересечение всего диапазона и целого конкретного столбца  рабочего листа как раз дает нам полный столбец этого диапазона. Практически, мы обращаемся к нему по имени, но при этом не используя самих имен.

[Serge 007]

...Столбцы этого диапазона (а их там 11 штук) не поименованы, но они вполне статичны, я их никуда перемещать не собираюсь, да и весь диапазон никуда не перемещается и по горизонтали не расширяется (только по вертикали).

Ну тогда - нет вопросов. Если диапазоны статичны - то и имена ни к чему. А зная что у Вас Excel не ниже 2007 - то вдвойне странно что возник такой вопрос, но...
Я вобщем-то привык, что пользователи выбирают не оптимальное решение, а то, которое им понятно, это жизнь такая...
Как говорится - хозяин-барин

ЗЫ Жаль только что хорошие советы канут в лету...
Ну и хрен с ними

[Олег*]

ЗЫ Жаль только что хорошие советы канут в лету...

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

Я вобщем-то привык, что пользователи выбирают не оптимальное решение, а то, которое им понятно, это жизнь такая...

Ну так это же вполне естественно. Например, про функцию ИНДЕКС я вычитал в справочной системе Экселя только сегодня, вставил ее в свой пример, практически, методом тыка, в смысле, не будучи до конца уверенным, что все делаю правильно. А если использовать ее в серьезной работе, надо же быть на все сто уверенным, что сюрпризов от нее не будет.

А зная что у Вас Excel не ниже 2007 - то вдвойне странно что возник такой вопрос, но...

Вот, вот, вот... кстати, кстати
Хотелось бы на этом вопросе подробнее остановиться. С самого начала, как начал читать эту книгу, не покидает мысль, что может быть половина рекомендуемых там "танцев с бубном" уже давным-давно утратила свою актуальность? Может быть, действительно, подскажете, что в этой книге можно вообще пропустить? Сейчас я знаю только то, что в связи с появлением функции СУММЕСЛИМН можно не уделять особого внимания шаманским танцам во имя возможности суммирования по нескольким условным критериям.

P.S. У меня на большом компьютере 2010-й, а на нетбуке 2003-й, он 2010-й не потянет, слабоват (я уже пробовал).

[Олег*]

...не покидает мысль, что может быть половина рекомендуемых там "танцев с бубном" уже давным-давно утратила свою актуальность?

Верно. Утратила

Ну так может быть потихоньку и начнем разбираться в том вопросе,  что полезного можно из этой книги почерпнуть, а на что уже можно/нужно смотреть сквозь пальцы. Может и книжку эту читать уже не стоит? Но тогда какую читать? Для 2010-го, насколько я знаю, книги этого автора на русский еще не переведены. А его книги для 2007-го... Ну так ведь у меня он не установлен сейчас. Хотел-было установить, но мне здесь на форуме посоветовали устанавливать сразу 2010-й. А других таких авторитетных авторов вроде бы и нет пока, ну по крайней мере, я их имена не знаю.

Может быть возметесь начертить "дорожную карту" для изучения современного Экселя, практически, с нуля? Вот Вы бы сами сейчас какую литературу стали бы читать, если бы были новичком в этом деле, но из "прошлой жизни" сохранили смутные воспоминания того, в какую сторону надо двигаться?

[_Boroda_]

Олег, про СУММЕСЛИМН хочу Вам сказать (сразу оговорюсь, что это мое личное мнение) - функция абсолютно ненужная и даже немного вредная. Предположу, что ввели ее исключительно по многочисленным просьбам пользователей, которые не освоили в должной степени СУММПРОИЗВ и массивную СУММ. Попробуйте поиском найти вопросы по СУММЕСЛИМН и Вы увидите, что очень много ответов на вопросы типа "А как прописать ..." её не используют, а используют СУММПРОИЗВ. Поверьте, это неспроста.
По поводу именованных диапазонов - если у Вас они ставятся макросом, то обратите внимание на опцию "Создать из выделенного" - на VBA это примерно так: Range("A1:C3").CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False    Создается куча имен по названиям столбцов, строк, нижней строке и правому столбцу соответственно. Иногда так удобнее.
А еще вместо имен можно использовать таблицы Excel (в 2003 аналог - списки). Там саморасширяющиеся диапазоны и ссылки на них выглядят примерно так:=Наименование_таблицы[наименование_столбца]. И не нужно никаких имен.

[Олег*]

Serge 007

Если диапазоны статичны - то и имена ни к чему.

Сразу не сообразил, что на это ответить, а теперь вот подумал немного...
Уокенбах рекомендует использовать именованные диапазоны даже не столько из-за их удобной перемещаемости, сколько из-за того, что с осмысленными именами гораздо удобнее работать. Например, он говорит так (цитирую по памяти, но суть именно такая):

Вот допустим у вас имеется на листе такая формула

Код Выделить Развернуть

=B1-A1
А теперь сравните ее вот с такой формулой:

Код Выделить Развернуть

=Доход-Расходы
А теперь скажите, с какой из этих формул удобнее работать?

_Boroda_

По поводу именованных диапазонов - если у Вас они ставятся макросом, то обратите внимание на опцию "Создать из выделенного" - на VBA это примерно так: Range("A1:C3").CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False    Создается куча имен по названиям столбцов, строк, нижней строке и правому столбцу соответственно. Иногда так удобнее.

Да нет, не макросом имена присваиваю, вручную. Сейчас вот у Уокенбаха (все-таки уже 200 страниц изучил) вычитал, про ту возможность, которую Вы упоминули, в смысле, выделить, а потом "Создать из выделенного". Как это макросом делать я пока не понял, но думаю, что мне это пока и не нужно. Честно говоря, я, по возможности, стараюсь не создавать излишне много именованных диапазонов вовсе не потому, что я их не люблю (Как раз наоборот! С ними гораздо удобнее во всех отношениях!), а потому что бывает порой трудно придумывать для них осмысленные выразительные имена, а если имен будет много и при этом они будут невыразительными, тогда в них будет даже легче запутаться, чем в обычных адресах, мне кажется. А кроме того, во мне еще живут опасения новичка, который недолюбливает того, чего он не видит на рабочих листах, того что находится где-то там "за кулисами" рабочей книги.

А еще вместо имен можно использовать таблицы Excel (в 2003 аналог - списки). Там саморасширяющиеся диапазоны и ссылки на них выглядят примерно так:=Наименование_таблицы[наименование_столбца]. И не нужно никаких имен.

До списков я пока еще у Уокенбаха не дочитал, поэтому с ними не знаком А что касается, таблиц саморасширяющихся, то я с ними замучался недавно, даже в отдельной теме вопрос задавал, как это "удобство" отключить. Сначала вроде получилось,

Код Выделить Развернуть

Application.AutoCorrect.AutoFillFormulasInLists = False

потом опять какая-то хрень стала вылезать там, где не надо. Все-таки решил пока автозаполнения таблиц избегать до поры до времени.

[Serge 007]

Уокенбах рекомендует использовать именованные диапазоны даже не столько из-за их удобной перемещаемости, сколько из-за того, что с осмысленными именами гораздо удобнее работать. Например, он говорит так (цитирую по памяти, но суть именно такая):

Вот допустим у вас имеется на листе такая формула

Код Выделить Развернуть

=B1-A1
А теперь сравните ее вот с такой формулой:

Код Выделить Развернуть

=Доход-Расходы
А теперь скажите, с какой из этих формул удобнее работать?

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

=Доход-Расходы - это конечно хорошо, но лично для меня - неудобно, потому как для того что бы посмотреть откуда эти самые Доход и Расходы берутся необходимо сделать несколько лишних телодвижений (Ctrl+F3, найти имя, особенно если их много, посмотреть диапазон), так что запись =B1-A1 для меня намного информативнее (глядя на неё можно сразу сказать что данные берутся с активного листа этой книги из ячеек B1 и A1)
На крайний случай можно добавить описание в саму формулу, типа так:

Код Выделить Развернуть

=B1+Ч("B1-это Доходы")-A1+Ч("A1-это Расходы")

Имена я практически всегда применяю только в трёх случаях:
1. Для констант
2. Для макрофункций
3. Для создания источника сводных таблиц

[_Boroda_]

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

[Олег*]

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

Но с другой стороны, если нет необходимости перемещать формулы в другую книгу (а у меня такая необходимость возникает достаточно редко) и область применения всегда устанавливать на уровне книги, а не на уровне отдельных листов (а я всегда именно так и делаю), то имена все-таки очень удобны.
Например, я часто делаю так: на листе в столбце A располагаются какие-нибудь осмысленные названия данных, которые используются в макросах, а в столбце B в соответствующей строке располагаются сами эти данные.  Часто возникает необходимость перемещать эти данные в пределах листа (ну например, дополнительные данные понадобились по ходу разработки макроса, появилась мысль скомпоновать данные иначе как-нибудь, чтобы они находились в соотвествующих им по смыслу группах, иногда просто требуется перемещать данные по листу из эстетических соображений...), и при этом присвоенные им имена (за малым изменением совпадающие с их именами в столбце A) всегда автоматически перемещаются вместе с ними. Если бы этого не было, то это был бы адский труд (а так и было, пока я не узнал про возможность присваивать имена), связанный с постоянным изменением в макросах адресов нужных ячеек. А сейчас я перетаскиваю по листу что хочу куда хочу, а в макросах ничего при этом изменять не требуется, очень удобно.

Олег, а насчет таблиц - посмотрите еще разок, может, понравится, когда привыкнете. Там есть несколько очень занимательных и удобных особенностей.

На самом деле, я не совсем против таблиц   Я согласен, что они ОЧЕНЬ удобны и с ними очень комфортно работать. Когда мне не требуется вставлять в таблицы формулы с помощью макросов, я всегда ими охотно пользуюсь. А вот если такая необходимость имеется или просматривается в перспективе, то после недавних нескольких дней "стояния на ушах" стараюсь теперь их избегать.