Как передать в Сумм() имена листов, вычисляемые в зависимости от выполнения условий? Что-то ни как не могу сообразить, кризис жанра? Или просто пора отдохнуть.
Igor67, а Вы добавьте пустой листочек, который будет всегда последним и задавайте один и тот же диапазон на здоровье :) Это в том случае, если Вам требуется всегда охватывать весь диапазон, конечно.
PS: В перечне функций, доступных для использования с трёхмерной ссылкой, ДВССЫЛ не числится.
Пользователь
Сообщений: Регистрация: 01.01.1970
24.08.2009 18:44:54
Практически та же проблема, только все гораздо хуже :(((
нужно суммировать не весь диапазон, а с определенного листа по определенный (т.е. те листы, которые укажет пользователь (в сереньких ячейках))
Танда, в вашем случае это в принципе возможно, если листы называются цифрами или если можно создать табличку с именами листов.
К примеру, так: массив(ctrl+shift+enter при вводе вместо enter) =СУММ(СУММЕСЛИ(ДВССЫЛ(АДРЕС(1;1;1;1;СТРОКА(ДВССЫЛ(АДРЕС(D1;1;1;1)):ДВССЫЛ(АДРЕС(F1;1;1;1)))));"<>")) Можно заменить двссыл внутри на индекс. Будет быстрее.
Кстати, возвращаясь к первому вопросу Igor67: Собственно, реально ли осуществить данную задачу без доп ячеек, в которых имена листов? Если имена листов не являются цифрами.
PS: Tanda, в прикреплённом файле так же пример с таблицей имён. Так же там же пример формул с помощью которой для данной конструкции можно задавать динамически изменяющиеся диапазоны.
Далее идёт длинное рассуждение про попытку решить данный вопрос с помощью двссыл(): Т е, насколько я понимаю ДВССЫЛ, тут требуется задание ссылок на диапазоны _каждого_ листа. Или я ошибаюсь и есть другой путь? Или это под силу какой-нибудь другой формуле?
Прикладываю немного доработанный файл Igor67, на примере которого можно, по возможности, дать пример формулы. Я в формуле использую для создания массива имён листов для двссыл функцию индекс. Индекс же берёт имена из специально созданного диапазона.
Как вариант, можно попытаться создавать массив значений для индекса внутри самой формулы формулами. К примеру, вначале извлекаем имена всех листов. Как-не знаю, не получилось. Попытка в J13. Это будет массивом индекса, т е извлечение тем или иным образом всех имён оказывается в первом аргументе индекса. Потом по этому массиву уже ищем с какого и по какой элемент выводить, согласно данным из других ячеек.
Саша, я не очень понял, в чём Вы видите проблему :-) На всякий случай приложу немного модифицированный вариант решения первоначальной задачи годичной давности (Igor67 12.07.2008, 15:16)
Я суммировал диапазоны листов с листа проба_01 по листе проба_03, т е диапазоны с трёх листов: проба_01, проба_02, проба_03. Ваша формула суммирует диапазоны с листа проба_01 и проба_03, т е 2 КРАЙНИХ диапазона, без серединного диапазона листа проба_02. Я задавал массив для двссыл с помощью дополнительно созданного диапазона, где перечисляются листы. Интересует возможность сделать то же самое без необходимости задействовать дополнительные ячейки, где перечисляются имена листов.
Т е в примере танды в связи с тем, что имена листов-цифры, можно было создать массив имён, используя те же функии строка/столбец. Что и вы и я сразу же сделали. Т е схалявили :) В случае, когда листы назваются просто именами, так сделать нельзя. В последней формуле вы убрали столбец(х) и получился просто массив из двух листов вместо массива из листов между этими листами, включая крайние листы.
Пользователь
Сообщений: Регистрация: 16.01.2013
25.08.2009 00:35:47
Немного добавлю. Извиняюсь, что указываю на ошибку, ничего личного, исключительно из "академического интереса". Если я правильно понял, вы пытаетесь задать именно диапазон листов таким образом, но эта формула не работает так в данной конструкции. Или вы где то случайно ошиблись, а я просто не знаю как задать диапазон листов в данной конструкции. Но я пробовал, честно :)
Пользователь
Сообщений: Регистрация: 16.01.2013
25.08.2009 00:55:23
Ммм... Извиняюсь за спам в виде трёх сообщений подряд, но: У меня excel 2007. Учитывая то, что я плохо разбираюсь в версиях, а формула задаётся "вручную", я допускаю, что расчёт у нас может быть разным в связи с разными версиями экселя.
Знак "'" используется в адресе, в том числе, при задании диапазонов листов. Ваш последний пример выдаёт результат 10+1+(20+2)+30+3=44 Т е цифры 20 и 2 с листа "Проба_02" в расчётах не учавствуют. Правильный ответ должен быть 66. Если у вас не так, дело в разных версиях и, соответственно, мне нужно понять как задавать диапазон такого плана в 2007 экселе.
2 формулы из моего последнего файла(1 со знаками "'", другая без) так же у меня выдают одинаковый результат. Пытался понять зачем вы используете "'" в примере Танды, если формула всё равно "создавала 3 диапазона", а потом их суммировала, а не один диапазон с 1 по 3 лист. Поэтому и задал вопрос зачем усложнять.
Пользователь
Сообщений: Регистрация: 23.12.2012
25.08.2009 01:02:11
Точно - моя ошибка в последнем приложенном файле. Впрочем, я же писал там "наверное" :-) Прилагаю исправленный вариант.
{quote}{login=Саша}{date=25.08.2009 12:55}{thema=}{post}... Пытался понять зачем вы используете "'" в примере Танды, если формула всё равно "создавала 3 диапазона", а потом их суммировала, а не один диапазон с 1 по 3 лист. Поэтому и задал вопрос зачем усложнять.{/post}{/quote} Знак "'" не удалял для общего случая - привычка. В примере Татьяны он может быть исключен.
Модератор
Сообщений: Регистрация: 14.09.2012
Контакты см. в профиле
25.08.2009 01:15:08
Пока вы тут спорите - выложу файл с небольшим макросом. Может Татьяне будет и этого достаточно :-)
Не, ну это понятно. Если это цифры или часть, которую можно извлечь, состоит из цифр, то можно через те же строки-столбцы. Интересует именно возможность делать то же самое формулами для листов с любыми именами, когда известно лишь название первого и последнего листа, но не известно названия листов между ними. Допустим, имена листов Саша-Вася-Петя.
Опять же, для меня это представляет больше теоретический интерес. Я, повторюсь, попытался создать массив индекса, состоящих из имён листов, формулами. Почему-то эксель выдал ошибку. Для извлечения имён использовал формулу: ПСТР(ЯЧЕЙКА("адрес";Проба_01:Проба_03!A1);1+ПОИСК("]";ЯЧЕЙКА("адрес";Проба_01:Проба_03!A1));ПОИСК("!";ЯЧЕЙКА("адрес";Проба_01:Проба_03!A1))-ПОИСК("]";ЯЧЕЙКА("адрес";Проба_01:Проба_03!A1))-1) Индекс() поддерживает массивы такого плана, пстр() тоже. Поиск() вроде бы тоже, не уверен. Насчёт ячейки() могу только гадать. Вы не подскажете в чём ошибка? В ячейке()? И есть ли другая возможность извлечь имена листов, которая может создать массив для индекса()?
Круто, но это "Макрос_шмакрос", а интересует именно возможность делать то же самое формулами :(
Я конечно могу сказать, что "здесь идёт стандартное описание минусов макросов", но на самом деле мне тупо интересно узнавать что то новое и решать разные задачи, а к изучению макросов так и не приступил. В связи с чем, это для меня какая то непонятная абракадабра и, соответственно, совершенно не интересно, хотя возможно и было бы, если бы да кабы.. :(
Модератор
Сообщений: Регистрация: 14.09.2012
Контакты см. в профиле
25.08.2009 02:06:31
Ну вот... а я старался :-) С формулами это не ко мне, к vikktur. Может он прямо сейчас сочиняет такую. Символов на 400... А если "интересно узнавать что то новое и решать разные задачи", то почему "к изучению макросов так и не приступил"?
Пользователь
Сообщений: Регистрация: 16.01.2013
25.08.2009 02:19:45
Да я, сюда, собственно и был послан, как место, где можно посмотреть примеры реализации того-сего, самому что-нибудь решить, да ответы на разные вопросы получить. Но потихоньку начал помогать по формулам, потом и зарегестрировался. Как то такой пищи для мозгов вроде хватает, хоть в последнее время и "конкурентов", решающих задачи по формулам, прибавилось. Хотя понимаю, что давно бы уже вместо этого научился неплохо писать макросы, что было бы намного полезнее. А до макросов так дело и не дошло по сути. Несколько раз даже пытался книги читать. Всё желание отбивает необходимость найти хорошую книжку и запомнить много-премного страшных волшебных слов, да конструкций вроде "dim свет, dim земля".
Пользователь
Сообщений: Регистрация: 01.01.2013
25.08.2009 02:41:23
ZVI, если не сложно, пропишите пожалуйста вашу последнюю формулу в сообщении, а то я неделю буду заходить сюда только с iPhone и файлов соответственно открывать не смогу, а значит, в большинстве случаев не смогу предложить своего решения, но зато попробую "попаразитироват"ь на чужих :-)
KL
Модератор
Сообщений: Регистрация: 14.09.2012
Контакты см. в профиле
25.08.2009 02:49:28
{quote}{login=Саша}{date=25.08.2009 02:19}{thema=}{post}Да я, сюда, собственно и был послан...{/post}{/quote} Кем? :-))
Guest
Гость
25.08.2009 03:42:36
KL, Вот последняя формула zvi =СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ("'"&ЛЕВСИМВ(C4;ДЛСТР(C4)-2)&ТЕКСТ(СТРОКА(ДВССЫЛ(ПРАВСИМВ(C4;2)&":"&ПРАВСИМВ(D4;2)));"00")&"'!"&E4);"<>")) С4-имя первого листа D4-имя последнего листа E4-диапазон(="a2:b3")
В прикреплённом файле "адаптированный вариант" для айфона со всеми листами/формулами и мой вопрос на последнем листе.
Обычно сначала пытаютcя понять, в чём проблема. Затем оценить - действительно ли это является проблемой? И если подтвердится, то уже пытаться её решать :-)
Бывает так, что одну задачу просто решить формулами, но другую намного удобнее решать в VBA. Причём, кому-то комфортнее обращаться именно с формулами, а кому-то с кодом, и это нормально. Часто восхищаюсь эквилибристике формул, которую здесь демонстрируют Виктор (Vikttur), Сергей (DL), Александр (Sh_Alex) и многие другие почитатели этого искусства.
В данном случае, мне проще написать в VBA недостающие любителям формул пользовательские функции, которые можно вызывать в формулах ячеек. В приложении – 4 таких функции: ShArray(), GetArray(), ShSum(), ShCount()
Примеры использования в формулах ячеек:
=ShArray("Саша";"Петя") - вернёт массив индексов листов с "Саша" по "Петя" =ShArray("Саша";"Петя";1) - вернёт массив имён листов с "Саша" по "Петя"
=GetArray(ShArray("Саша";"Петя");1) - 1-й элемент массива (индекс листа "Саша") =GetArray(ShArray("Саша";"Петя";1);2) - 2-й элемент массива имен листов ("Вася")
=ShSum("Саша";"Петя";"A1:B2") – сумма ячеек A1:B2 листов с "Саша" по "Петя" =ShSum("Саша";"Петя";"A1:B2";1) – сумма ячеек с условием A1:B2=1 листов с "Саша" по "Петя" =ShSum("Саша";"Петя";"A1:B2";">10") – сумма ячеек с условием A1:B2>10 листов с "Саша" по "Петя"
=ShCount("Саша";"Петя";"A1:B2") - кол-во числовых ячеек A1:B2 листов с "Саша" по "Петя" =ShCount("Саша";"Петя";"A1:B2";1) - кол-во ячеек с условием A1:B2=1 листов с "Саша" по "Петя" =ShCount("Саша";"Петя";"A1:B2";">10") - кол-во ячеек с условием A1:B2>10 листов с "Саша" по "Петя"
В приложенной книге – VBA код с комментариями и тестовыми процедурами, а в ячейках – примеры использования в формулах.
На всякий случай прилагаю VBA-код из приложения моего предыдущего сообщения:
' ZVI:2009-08-25 ' Функция выдает массив индексов или имен листов с ShFrom по ShTo ' ShFrom - имя начального листа ' ShTo - имя конечного листа ' IsName - выдавать массив индексов если IsName=0 (по умолчанию) или имён листов ' Пример формулы вызова (для листов с именами: "Саша","Вася","Петя"): ' =ShArray("Саша";"Петя") - вернет массив индексов листов с "Саша" по "Петя" ' =ShArray("Саша";"Петя";1) - вернет массив имён листов с "Саша" по "Петя" Function ShArray(ShFrom As String, ShTo As String, Optional IsName = 0) Dim Arr(), i&, i1&, i2& i1 = Sheets(ShFrom).Index i2 = Sheets(ShTo).Index If i2 < i1 Then i = i1: i1 = i2: i2 = i ReDim Arr(1 To i2 - i1 + 1) For i = 1 To i2 - i1 + 1 Arr(i) = IIf(IsName = 0, i1 + i - 1, Sheets(i1 + i - 1).Name) Next ShArray = Arr End Function
' Функция выбора из массива Arr элемента с номером Index ' Пример формул: ' =GetArray(ShArray("Саша";"Петя");1) - вернёт 1-й элемент массива (индекс листа "Саша") ' =GetArray(ShArray("Саша";"Петя";1);2) - вернёт 2-й элемент массива имен листов Function GetArray(Arr, Index) On Error Resume Next GetArray = Arr(Index) If Err <> 0 Then GetArray = "" End Function
' Сумма ячеек Addr в книгах с ShFrom по ShTo, аналогично СУММ() ' Если задано условие Cond, то выполняется суммирование по условию аналогично СУММЕСЛИ() ' Примеры формул: ' =ShSum("Саша";"Петя";"A1:B2") - сумма ячеек A1:B2 листов с "Саша" по "Петя" ' =ShSum("Саша";"Петя";"A1:B2";1) - сумма ячеек A1:B2=1 листов с "Саша" по "Петя" ' =ShSum("Саша";"Петя";"A1:B2";">10") - сумма ячеек A1:B2>10 листов с "Саша" по "Петя" Function ShSum(ShFrom As String, ShTo As String, Addr As String, Optional Cond) As Double Dim x#, i&, i1&, i2& i1 = Sheets(ShFrom).Index i2 = Sheets(ShTo).Index If i2 < i1 Then i = i1: i1 = i2: i2 = i With Application.WorksheetFunction For i = i1 To i2 If IsMissing(Cond) Then x = x + .Sum(Sheets(i).Range(Addr)) Else x = x + .SumIf(Sheets(i).Range(Addr), Cond) End If Next End With ShSum = x End Function
' Количество числовых ячеек Addr в книгах с ShFrom по ShToБ аналогично СЧЁТ() ' Если задано условие Cond, то выполняется счёт по условию аналогично СЧЁТЕСЛИ() ' Примеры формулы: ' =ShCount("Саша";"Петя";"A1:B2") - кол-во числовых ячеек A1:B2 листов с "Саша" по "Петя" ' =ShCount("Саша";"Петя";"A1:B2";1) - кол-во ячеек с условием A1:B2=1 листов с "Саша" по "Петя" ' =ShCount("Саша";"Петя";"A1:B2";">10") - кол-во ячеек с условием A1:B2>10 листов с "Саша" по "Петя" Function ShCount(ShFrom As String, ShTo As String, Addr As String, Optional Cond) As Double Dim x&, i&, i1&, i2& i1 = Sheets(ShFrom).Index i2 = Sheets(ShTo).Index If i2 < i1 Then i = i1: i1 = i2: i2 = i With Application.WorksheetFunction For i = i1 To i2 If IsMissing(Cond) Then x = x + .Count(Sheets(i).Range(Addr)) Else x = x + .CountIf(Sheets(i).Range(Addr), Cond) End If Next End With ShCount = x End Function
' ==== Тестирование ========
Sub Test_ShArray() MsgBox Join(ShArray("Саша", "Петя")) End Sub
Sub Test_ShArray_1() MsgBox Join(ShArray("Саша", "Петя", 1)) End Sub
Sub Test_GetArray() MsgBox GetArray(ShArray("Саша", "Петя", 1), 2) End Sub
Sub Test_ShSum() MsgBox ShSum("Саша", "Петя", "A1:B2") End Sub
Sub Test_ShSum_Cond_1() MsgBox ShSum("Саша", "Петя", "A1:B2", 1) End Sub
Sub Test_ShSum_Cond_2() MsgBox ShSum("Саша", "Петя", "A1:B2", ">10") End Sub
Sub Test_ShCount() MsgBox ShCount("Саша", "Петя", "A1:B2") End Sub
Sub Test_ShCount_Cond() MsgBox ShCount("Саша", "Петя", "A1:B2", ">10") End Sub
Guest
Гость
25.08.2009 04:03:57
Zvi, я понимаю, но опять же, повторюсь, для меня эта конкретная задача носит исключительно теоретический интерес в данный момент. Связано именно с тем, что я сам не знаю как это решить формулами и это меня часто ограничивает в возможностях решения задач таково плана. Просто хочется понять возможно ли это и, если да, то как. Обычно, когда учишься чему-нибудь новому, потом это можно применять много где. Более того, я склонен считать, что это интересно не только мне. Эту тему поднял igor67, который сам помогает посетителям этого сайта. Вышеупомянутый vikturr принял довольно активное участие в моей прошлой теме. А после он же несколько раз использовал "новые наработки" при решении задач на этом же сайте.
Если по каким то причинам вы не можете или не хотите отвечать-нет проблем. Вам тут никому ничего доказывать и объяснять не нужно. Вам тут многие благодарны. Собственно, вы и мне лично когда то помогли.
PS: KL, выкладываю второй файл :) После проверки, выяснилось, что файл имеет не такой вид, как я до этого предполагал. Надеюсь в этот раз получилось...
Саша, мне проще было решить задачу макросами, а формулами ... не знаю, наверное, тоже как-то можно исхитриться :-) Кстати в моем файле, который Вы переделываете пот iPhone для KL, все макросы нужно поудалять - это мусор, который остался после экспериментирования. Там играют только формулы в ячейках и одна формулв в имени Sheets, которая возвращает массив имен всех листов книги.
То есть, (инфо для KL):
Листы: "Проба_01", "Проба_02", "Проба_03" и еще кике-либо.
Значения C4:E4: "Проба_01", "Проба_03" "A2:B3"
Формула ячейки C14: =СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ("'"&ЛЕВСИМВ(C4;ДЛСТР(C4)-2)&ТЕКСТ(СТРОКА(ДВССЫЛ(ПРАВСИМВ(C4;2)&":"&ПРАВСИМВ(D4;2)));"00")&"'!"&E4);"<>"))
То же, но для английской версии: =SUMPRODUCT(SUMIF(INDIRECT("'"&LEFT(C4,LEN(C4)-2)&TEXT(ROW(INDIRECT(RIGHT(C4,2)&":"&RIGHT(D4,2))),"00")&"'!"&E4),"<>"))
Формула ячейки C18 (используется массив, выдаваемый формулой имени Sheets): =СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ("'"&Sheets&"'!"&E4);"<>"))
То же, но для английской версии: =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&E4),"<>"))
Формула имени Sheets: =ПОДСТАВИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);"["&ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(16)&"]";"")
То же, но для английской версии: =SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")
Пользователь
Сообщений: Регистрация: 23.12.2012
25.08.2009 05:37:07
Саша, не сильно выдумывая, вот что получилось:
Формула массива и пустой 1-й лист с именем Лист1 (его можно скрыть): =СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ("'"&ЕСЛИ((СТОЛБЕЦ(ДВССЫЛ("1:10"))>=ПОИСКПОЗ(C4;Sheets))*(СТОЛБЕЦ(ДВССЫЛ("1:10"))<=ПОИСКПОЗ(D4;Sheets));Sheets;"Лист1")&"'!"&E4);"<>"))
Или немного иначе, тоже формула массива: =СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ("'"&ЕСЛИ(СТОЛБЕЦ(ДВССЫЛ("1:10"))>=ПОИСКПОЗ(C4;Sheets);ЕСЛИ(СТОЛБЕЦ(ДВССЫЛ("1:10"))<=ПОИСКПОЗ(D4;Sheets);Sheets;"Лист1");"Лист1")&"'!"&E4);"<>"))
Использовано имя Sheets с формулой: =ПОДСТАВИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);"["&ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(16)&"]";"")
Думаю, что можно оптимизировать, но вопрос же был о возможности в принципе, не так ли?
Формула будет короче и это уже не формула массива, если суммировать нужно по всем листам с именем, начинающихся с "Проба", при этом не обязательно, чтобы такие листы были рядом: =СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ("'"&ЕСЛИ(ЛЕВСИМВ(Sheets;5)="Проба";Sheets;"Лист1")&"'!"&E4);"<>"))
Пользователь
Сообщений: Регистрация: 23.12.2012
25.08.2009 05:51:45
Без дополнительного листа1, формула массива: =СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ("'"&ЕСЛИ((СТОЛБЕЦ(ДВССЫЛ("1:10"))>=ПОИСКПОЗ(C4;Sheets))*(СТОЛБЕЦ(ДВССЫЛ("1:10"))<=ПОИСКПОЗ(D4;Sheets));Sheets&"'!"&E4;C4&"'!IV65536"));"<>"))
Пользователь
Сообщений: Регистрация: 23.12.2012
25.08.2009 06:39:04
Везде вместо ПОИСКПОЗ(C4;Sheets) нужно использовать ПОИСКПОЗ(C4;Sheets;0).
Прилагаю добавленный к VBA-функциям описанный вариант решения с помощью формул имен.
Имена ячеек G14:G16, задающих диапазон листов и ячеек, соответственно: ShStart, ShEnd, ShCond
Имя Sheets: =ПОДСТАВИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);"["&ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(16)&"]";"")
Имя SheetsSum: =СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ("'"&ЕСЛИ((СТОЛБЕЦ(ДВССЫЛ("1:10"))>=ПОИСКПОЗ(ShStart;Sheets;0))*(СТОЛБЕЦ(ДВССЫЛ("1:10"))<=ПОИСКПОЗ(ShEnd;Sheets;0));Sheets&"'!"&ShCond;ShStart&"'!IV65536"));"<>"))
