Анализ по всей книге и вывод значения в виде названия листа
Georg81
Дата: Суббота, 27.12.2014, 13:17 |
Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация:
0
±
Замечаний:
0% ±
Excel 2003, Excel 2010
Добрый день товарищи гуру Exel. Есть такая проблема. Есть книга , на первом листе(с названием ИТОГ) будет определенная ячейка, в которой будет формула (скажем A1B1). Происходит анализ по всей книге и если в этой же ячейке (A1B1) в любом другом листе будет любое значение то выводится на первый лист(ИТОГО) название листа(1,2,3.....) на котором есть хоть какое -нибудь значение, желательно через запятую. Возможно ли это сделать без скриптов просто формулой. Заранее благодарен!!!
Добрый день товарищи гуру Exel. Есть такая проблема. Есть книга , на первом листе(с названием ИТОГ) будет определенная ячейка, в которой будет формула (скажем A1B1). Происходит анализ по всей книге и если в этой же ячейке (A1B1) в любом другом листе будет любое значение то выводится на первый лист(ИТОГО) название листа(1,2,3.....) на котором есть хоть какое -нибудь значение, желательно через запятую. Возможно ли это сделать без скриптов просто формулой. Заранее благодарен!!! Georg81
Сообщение отредактировал Georg81 - Суббота, 27.12.2014, 13:49
Ответить
Сообщение Добрый день товарищи гуру Exel. Есть такая проблема. Есть книга , на первом листе(с названием ИТОГ) будет определенная ячейка, в которой будет формула (скажем A1B1). Происходит анализ по всей книге и если в этой же ячейке (A1B1) в любом другом листе будет любое значение то выводится на первый лист(ИТОГО) название листа(1,2,3.....) на котором есть хоть какое -нибудь значение, желательно через запятую. Возможно ли это сделать без скриптов просто формулой. Заранее благодарен!!! Автор - Georg81 Дата добавления - 27.12.2014 в 13:17
AlexM
Дата: Суббота, 27.12.2014, 13:23 |
Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4513
Репутация:
1128
±
Замечаний:
0% ±
Excel 2003
Прочитайте правила форума
Прочитайте правила форума AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Сообщение Прочитайте правила форума Автор - AlexM Дата добавления - 27.12.2014 в 13:23
Georg81
Дата: Суббота, 27.12.2014, 13:29 |
Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация:
0
±
Замечаний:
0% ±
Excel 2003, Excel 2010
Хорошо, что-то нарушил? Сейчас попробую создать файл примера, но я не знаю какую формулу в том-то и дело.
Хорошо, что-то нарушил? Сейчас попробую создать файл примера, но я не знаю какую формулу в том-то и дело. Georg81
Сообщение отредактировал Georg81 - Суббота, 27.12.2014, 13:31
Ответить
Сообщение Хорошо, что-то нарушил? Сейчас попробую создать файл примера, но я не знаю какую формулу в том-то и дело. Автор - Georg81 Дата добавления - 27.12.2014 в 13:29
AlexM
Дата: Суббота, 27.12.2014, 13:31 |
Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4513
Репутация:
1128
±
Замечаний:
0% ±
Excel 2003
Формулу не надо. Там где формула покажите результат и объясните как его получать.
Формулу не надо. Там где формула покажите результат и объясните как его получать. AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Сообщение отредактировал AlexM - Суббота, 27.12.2014, 13:32
Ответить
Сообщение Формулу не надо. Там где формула покажите результат и объясните как его получать. Автор - AlexM Дата добавления - 27.12.2014 в 13:31
Georg81
Дата: Суббота, 27.12.2014, 13:50 |
Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация:
0
±
Замечаний:
0% ±
Excel 2003, Excel 2010
Исправил
Ответить
Сообщение Исправил Автор - Georg81 Дата добавления - 27.12.2014 в 13:50
Richman
Дата: Суббота, 27.12.2014, 14:07 |
Сообщение № 6
Группа: Проверенные
Ранг: Обитатель
Сообщений: 346
Репутация:
54
±
Замечаний:
0% ±
Excel 2007
Georg81 , вот так? Код
=ЕСЛИ(Лист2!A1<>0;"Лист2:"&Лист2!A1;"")&ЕСЛИ(Лист3!A1<>0;"; Лист3:"&Лист3!A1;"")&ЕСЛИ(Лист4!A1<>0;"; Лист4:"&Лист4!A1;"")&ЕСЛИ(Лист5!A1<>0;"; Лист5:"&Лист5!A1;"")&ЕСЛИ(Лист6!A1<>0;"; Лист6:"&Лист6!A1;"")&ЕСЛИ(Лист7!A1<>0;"; Лист7:"&Лист7!A1;"")
Georg81 , вот так? Код
=ЕСЛИ(Лист2!A1<>0;"Лист2:"&Лист2!A1;"")&ЕСЛИ(Лист3!A1<>0;"; Лист3:"&Лист3!A1;"")&ЕСЛИ(Лист4!A1<>0;"; Лист4:"&Лист4!A1;"")&ЕСЛИ(Лист5!A1<>0;"; Лист5:"&Лист5!A1;"")&ЕСЛИ(Лист6!A1<>0;"; Лист6:"&Лист6!A1;"")&ЕСЛИ(Лист7!A1<>0;"; Лист7:"&Лист7!A1;"")
Richman
С Уважением, Richman
Ответить
Сообщение Georg81 , вот так? Код
=ЕСЛИ(Лист2!A1<>0;"Лист2:"&Лист2!A1;"")&ЕСЛИ(Лист3!A1<>0;"; Лист3:"&Лист3!A1;"")&ЕСЛИ(Лист4!A1<>0;"; Лист4:"&Лист4!A1;"")&ЕСЛИ(Лист5!A1<>0;"; Лист5:"&Лист5!A1;"")&ЕСЛИ(Лист6!A1<>0;"; Лист6:"&Лист6!A1;"")&ЕСЛИ(Лист7!A1<>0;"; Лист7:"&Лист7!A1;"")
Автор - Richman Дата добавления - 27.12.2014 в 14:07
Georg81
Дата: Суббота, 27.12.2014, 14:11 |
Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация:
0
±
Замечаний:
0% ±
Excel 2003, Excel 2010
немного не так, проще, если в графе ОПИСАНИЕ на любом листе есть какая-нибудь запись, то на листе "итог" пишется на каком ЛИСТЕ есть какая-то запись... Но спасибо большое что отозвались!!!
немного не так, проще, если в графе ОПИСАНИЕ на любом листе есть какая-нибудь запись, то на листе "итог" пишется на каком ЛИСТЕ есть какая-то запись... Но спасибо большое что отозвались!!! Georg81
Сообщение отредактировал Georg81 - Суббота, 27.12.2014, 14:12
Ответить
Сообщение немного не так, проще, если в графе ОПИСАНИЕ на любом листе есть какая-нибудь запись, то на листе "итог" пишется на каком ЛИСТЕ есть какая-то запись... Но спасибо большое что отозвались!!! Автор - Georg81 Дата добавления - 27.12.2014 в 14:11
Georg81
Дата: Суббота, 27.12.2014, 14:14 |
Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация:
0
±
Замечаний:
0% ±
Excel 2003, Excel 2010
хотя я формулу сейчас попробую переделать под себя....а как-нибудь ее можно упростить максимально? просто листов ОЧЕНЬ много
хотя я формулу сейчас попробую переделать под себя....а как-нибудь ее можно упростить максимально? просто листов ОЧЕНЬ много Georg81
Ответить
Сообщение хотя я формулу сейчас попробую переделать под себя....а как-нибудь ее можно упростить максимально? просто листов ОЧЕНЬ много Автор - Georg81 Дата добавления - 27.12.2014 в 14:14
Richman
Дата: Суббота, 27.12.2014, 14:16 |
Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 346
Репутация:
54
±
Замечаний:
0% ±
Excel 2007
Georg81 , Ловите Код
=ЕСЛИ(Лист2!B2<>0;"Лист2;";"")&ЕСЛИ(Лист3!B2<>0;" Лист3;";"")&ЕСЛИ(Лист4!B2<>0;" Лист4;";"")&ЕСЛИ(Лист5!B2<>0;" Лист5;";"")&ЕСЛИ(Лист6!B2<>0;" Лист6;";"")&ЕСЛИ(Лист7!B2<>0;" Лист7;";"")
Georg81 , Ловите Код
=ЕСЛИ(Лист2!B2<>0;"Лист2;";"")&ЕСЛИ(Лист3!B2<>0;" Лист3;";"")&ЕСЛИ(Лист4!B2<>0;" Лист4;";"")&ЕСЛИ(Лист5!B2<>0;" Лист5;";"")&ЕСЛИ(Лист6!B2<>0;" Лист6;";"")&ЕСЛИ(Лист7!B2<>0;" Лист7;";"")
Richman
С Уважением, Richman
Ответить
Сообщение Georg81 , Ловите Код
=ЕСЛИ(Лист2!B2<>0;"Лист2;";"")&ЕСЛИ(Лист3!B2<>0;" Лист3;";"")&ЕСЛИ(Лист4!B2<>0;" Лист4;";"")&ЕСЛИ(Лист5!B2<>0;" Лист5;";"")&ЕСЛИ(Лист6!B2<>0;" Лист6;";"")&ЕСЛИ(Лист7!B2<>0;" Лист7;";"")
Автор - Richman Дата добавления - 27.12.2014 в 14:16
Georg81
Дата: Суббота, 27.12.2014, 14:20 |
Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация:
0
±
Замечаний:
0% ±
Excel 2003, Excel 2010
Да так !!!! Вы мне очень помогли, теперь буду думать как это упроститЬ!
Да так !!!! Вы мне очень помогли, теперь буду думать как это упроститЬ! Georg81
Ответить
Сообщение Да так !!!! Вы мне очень помогли, теперь буду думать как это упроститЬ! Автор - Georg81 Дата добавления - 27.12.2014 в 14:20
Georg81
Дата: Суббота, 27.12.2014, 14:28 |
Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация:
0
±
Замечаний:
0% ±
Excel 2003, Excel 2010
да проблема будет , 31 лист ведь будет сканироваться, и всего будет более 100 строк по которым будет сканироваться.
да проблема будет , 31 лист ведь будет сканироваться, и всего будет более 100 строк по которым будет сканироваться. Georg81
Ответить
Сообщение да проблема будет , 31 лист ведь будет сканироваться, и всего будет более 100 строк по которым будет сканироваться. Автор - Georg81 Дата добавления - 27.12.2014 в 14:28
Georg81
Дата: Суббота, 27.12.2014, 14:30 |
Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация:
0
±
Замечаний:
0% ±
Excel 2003, Excel 2010
А можно ли как-то объединить все страницы и чтоб выводилось это же значение. Допустим =ЕСЛИ('Лист1:Лист31'!B2<>0;"ТУТ НАЗВАНИЕ ЛИСТОВ НА КОТОРЫХ УСЛОВИЕ ВЕРНО ;";"") ? или только в варианте огромной формулы? потому как будет и B3 и B100 ну вы понимаете...
А можно ли как-то объединить все страницы и чтоб выводилось это же значение. Допустим =ЕСЛИ('Лист1:Лист31'!B2<>0;"ТУТ НАЗВАНИЕ ЛИСТОВ НА КОТОРЫХ УСЛОВИЕ ВЕРНО ;";"") ? или только в варианте огромной формулы? потому как будет и B3 и B100 ну вы понимаете... Georg81
Сообщение отредактировал Georg81 - Суббота, 27.12.2014, 14:39
Ответить
Сообщение А можно ли как-то объединить все страницы и чтоб выводилось это же значение. Допустим =ЕСЛИ('Лист1:Лист31'!B2<>0;"ТУТ НАЗВАНИЕ ЛИСТОВ НА КОТОРЫХ УСЛОВИЕ ВЕРНО ;";"") ? или только в варианте огромной формулы? потому как будет и B3 и B100 ну вы понимаете... Автор - Georg81 Дата добавления - 27.12.2014 в 14:30
AlexM
Дата: Суббота, 27.12.2014, 14:53 |
Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4513
Репутация:
1128
±
Замечаний:
0% ±
Excel 2003
31 лист. Обидно. Решение сделал до 9 листов.Код
=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(СУММ((Т(ДВССЫЛ(СТРОКА($1:$9)&"!B2"))<>"")*СТРОКА($1:$9)*10^((9-СТРОКА($1:$9))*2));0;" "));" ";",")
31 лист. Обидно. Решение сделал до 9 листов.Код
=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(СУММ((Т(ДВССЫЛ(СТРОКА($1:$9)&"!B2"))<>"")*СТРОКА($1:$9)*10^((9-СТРОКА($1:$9))*2));0;" "));" ";",")
AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Сообщение 31 лист. Обидно. Решение сделал до 9 листов.Код
=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(СУММ((Т(ДВССЫЛ(СТРОКА($1:$9)&"!B2"))<>"")*СТРОКА($1:$9)*10^((9-СТРОКА($1:$9))*2));0;" "));" ";",")
Автор - AlexM Дата добавления - 27.12.2014 в 14:53
Georg81
Дата: Суббота, 27.12.2014, 14:54 |
Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация:
0
±
Замечаний:
0% ±
Excel 2003, Excel 2010
я поправлю спасибо
Ответить
Сообщение я поправлю спасибо Автор - Georg81 Дата добавления - 27.12.2014 в 14:54
AlexM
Дата: Суббота, 27.12.2014, 14:56 |
Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4513
Репутация:
1128
±
Замечаний:
0% ±
Excel 2003
Больше не получится. Нужно макросы применять.
Больше не получится. Нужно макросы применять. AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Сообщение Больше не получится. Нужно макросы применять. Автор - AlexM Дата добавления - 27.12.2014 в 14:56
Georg81
Дата: Суббота, 27.12.2014, 15:01 |
Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация:
0
±
Замечаний:
0% ±
Excel 2003, Excel 2010
в общем расскажу в чем суть, есть месяц в нем 31 день есть примерно 100 пунктов в которых колличественные значения они просто суммируются это понятно, и к НЕКОТОРЫМ значениям будут описания НЕ КО ВСЕМ, так суть в том чтоб в итоговой странице выводились названия страниц (от 1 до 31) в столбце описания в строках где вносились какие-то записи. вот черт знает как сделать лучше.
в общем расскажу в чем суть, есть месяц в нем 31 день есть примерно 100 пунктов в которых колличественные значения они просто суммируются это понятно, и к НЕКОТОРЫМ значениям будут описания НЕ КО ВСЕМ, так суть в том чтоб в итоговой странице выводились названия страниц (от 1 до 31) в столбце описания в строках где вносились какие-то записи. вот черт знает как сделать лучше. Georg81
Ответить
Сообщение в общем расскажу в чем суть, есть месяц в нем 31 день есть примерно 100 пунктов в которых колличественные значения они просто суммируются это понятно, и к НЕКОТОРЫМ значениям будут описания НЕ КО ВСЕМ, так суть в том чтоб в итоговой странице выводились названия страниц (от 1 до 31) в столбце описания в строках где вносились какие-то записи. вот черт знает как сделать лучше. Автор - Georg81 Дата добавления - 27.12.2014 в 15:01
buchlotnik
Дата: Суббота, 27.12.2014, 15:06 |
Сообщение № 17
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация:
929
±
Замечаний:
20% ±
2010, 2013, 2016 RUS / ENG
Цитата
буду думать как это упроститЬ!
могу предложить UDF аргумент функции - ячейка с любого другого листа, с которых будем собирать. В таком виде устойчиво к переименованию листов и к изменению их числа Код под спойлером
[vba]
Код
Function ITOGO(rr As Range) As String Application.Volatile Dim n As String, r As Long, c As Integer, ss As Worksheet r = rr.Row c = rr.Column ITOGO = "" For Each ss In Sheets If ss.Name <> "Итого" Then If ss.Cells(r, c).Value <> "" Then ITOGO = ITOGO & ss.Name & "; " End If Next ss If Len(ITOGO) > 2 Then ITOGO = Left(ITOGO, Len(ITOGO) - 2) End Function
[/vba]
Цитата
буду думать как это упроститЬ!
могу предложить UDF аргумент функции - ячейка с любого другого листа, с которых будем собирать. В таком виде устойчиво к переименованию листов и к изменению их числа Код под спойлером
[vba]
Код
Function ITOGO(rr As Range) As String Application.Volatile Dim n As String, r As Long, c As Integer, ss As Worksheet r = rr.Row c = rr.Column ITOGO = "" For Each ss In Sheets If ss.Name <> "Итого" Then If ss.Cells(r, c).Value <> "" Then ITOGO = ITOGO & ss.Name & "; " End If Next ss If Len(ITOGO) > 2 Then ITOGO = Left(ITOGO, Len(ITOGO) - 2) End Function
[/vba]
buchlotnik
Сообщение отредактировал buchlotnik - Суббота, 27.12.2014, 17:23
Ответить
Сообщение Цитата
буду думать как это упроститЬ!
могу предложить UDF аргумент функции - ячейка с любого другого листа, с которых будем собирать. В таком виде устойчиво к переименованию листов и к изменению их числа Код под спойлером
[vba]
Код
Function ITOGO(rr As Range) As String Application.Volatile Dim n As String, r As Long, c As Integer, ss As Worksheet r = rr.Row c = rr.Column ITOGO = "" For Each ss In Sheets If ss.Name <> "Итого" Then If ss.Cells(r, c).Value <> "" Then ITOGO = ITOGO & ss.Name & "; " End If Next ss If Len(ITOGO) > 2 Then ITOGO = Left(ITOGO, Len(ITOGO) - 2) End Function
[/vba]
Автор - buchlotnik Дата добавления - 27.12.2014 в 15:06
Georg81
Дата: Суббота, 27.12.2014, 15:16 |
Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация:
0
±
Замечаний:
0% ±
Excel 2003, Excel 2010
buchlotnik , спасибо, отличное решение! Надо на 2003 протестировать, я так понимаю просто вставить в файл и потом использовать параметр ITOGO ?
buchlotnik , спасибо, отличное решение! Надо на 2003 протестировать, я так понимаю просто вставить в файл и потом использовать параметр ITOGO ?Georg81
Сообщение отредактировал Georg81 - Суббота, 27.12.2014, 15:20
Ответить
Сообщение buchlotnik , спасибо, отличное решение! Надо на 2003 протестировать, я так понимаю просто вставить в файл и потом использовать параметр ITOGO ?Автор - Georg81 Дата добавления - 27.12.2014 в 15:16
buchlotnik
Дата: Суббота, 27.12.2014, 15:44 |
Сообщение № 19
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация:
929
±
Замечаний:
20% ±
2010, 2013, 2016 RUS / ENG
Цитата
просто вставить в файл
ну да - добавляем программный молуль в ваш файл и вам становится доступной функция
Цитата
просто вставить в файл
ну да - добавляем программный молуль в ваш файл и вам становится доступной функцияbuchlotnik
Ответить
Сообщение Цитата
просто вставить в файл
ну да - добавляем программный молуль в ваш файл и вам становится доступной функцияАвтор - buchlotnik Дата добавления - 27.12.2014 в 15:44
Georg81
Дата: Суббота, 27.12.2014, 16:20 |
Сообщение № 20
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация:
0
±
Замечаний:
0% ±
Excel 2003, Excel 2010
Разобрался спасибо
Сообщение отредактировал Georg81 - Суббота, 27.12.2014, 17:13
Ответить
Сообщение Разобрался спасибо Автор - Georg81 Дата добавления - 27.12.2014 в 16:20