Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Отобразить диапазон из других ячеек. - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Отобразить диапазон из других ячеек.
Отобразить диапазон из других ячеек.
ALARMus Дата: Пятница, 14.09.2012, 17:02 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

В ячейке А1 стоит значение С1
В ячейке А2 стоит значение С6
Как в функции указать диапазон, начало и конец которого указаны в А1 и А2

ВПР("искомый текст2";значение из А1:значение из А2;2)

Значения в А1 и А2 в свою очередь тоже формульные:
А1="C"&ТЕКСТ((ПОИСКПОЗ(искомый текст1;диапазон));0)
А2="C"&ТЕКСТ((ПОИСКПОЗ(искомый текст1;диапазон))+6;0)

Для определенности написал, что в данный момент "искомый текст1" нашелся в первой строке.

Поэтому в идеале вставить бы в ВПР сразу функции из А1 и А2 - чтобы в одной ячейке все вычислялось бы


Сообщение отредактировал ALARMus - Пятница, 14.09.2012, 17:54
 
Ответить
СообщениеВ ячейке А1 стоит значение С1
В ячейке А2 стоит значение С6
Как в функции указать диапазон, начало и конец которого указаны в А1 и А2

ВПР("искомый текст2";значение из А1:значение из А2;2)

Значения в А1 и А2 в свою очередь тоже формульные:
А1="C"&ТЕКСТ((ПОИСКПОЗ(искомый текст1;диапазон));0)
А2="C"&ТЕКСТ((ПОИСКПОЗ(искомый текст1;диапазон))+6;0)

Для определенности написал, что в данный момент "искомый текст1" нашелся в первой строке.

Поэтому в идеале вставить бы в ВПР сразу функции из А1 и А2 - чтобы в одной ячейке все вычислялось бы
Автор - ALARMus
Дата добавления - 14.09.2012 в 17:02
ALARMus Дата: Пятница, 14.09.2012, 17:30 | Сообщение № 2
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

вот так если полностью
Еще раз подправил файл с новыми пояснениями
К сообщению приложен файл: _1.xlsx (14.4 Kb)


Сообщение отредактировал ALARMus - Пятница, 14.09.2012, 18:19
 
Ответить
Сообщениевот так если полностью
Еще раз подправил файл с новыми пояснениями
Автор - ALARMus
Дата добавления - 14.09.2012 в 17:30
DV Дата: Пятница, 14.09.2012, 18:24 | Сообщение № 3
Группа: Друзья
Ранг: Обитатель
Сообщений: 286
Репутация: 194 ±
Замечаний: 0% ±

Excel 2010 RUS
Если "Итог 1:" всегда на 4 строки ниже кода, то все намного проще (Лист 2 В2):
Code
=ИНДЕКС(Лист1!C$2:C$36;ПОИСКПОЗ(A2;Лист1!A$2:A$36;0)+4)
 
Ответить
СообщениеЕсли "Итог 1:" всегда на 4 строки ниже кода, то все намного проще (Лист 2 В2):
Code
=ИНДЕКС(Лист1!C$2:C$36;ПОИСКПОЗ(A2;Лист1!A$2:A$36;0)+4)
Автор - DV
Дата добавления - 14.09.2012 в 18:24
ALARMus Дата: Пятница, 14.09.2012, 18:29 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

нет не всегда, но если прибавить к строке кода 6 (строк) то туда однозначно попадет только одна строка со значением "Итог 1:" и как следствие относиться к текущему (обрабатываемому) коду, а от нового кода будут просмотрены лишние строки, но они не будут содержать "Итог 1:" от него (от следующего за первым)
 
Ответить
Сообщениенет не всегда, но если прибавить к строке кода 6 (строк) то туда однозначно попадет только одна строка со значением "Итог 1:" и как следствие относиться к текущему (обрабатываемому) коду, а от нового кода будут просмотрены лишние строки, но они не будут содержать "Итог 1:" от него (от следующего за первым)
Автор - ALARMus
Дата добавления - 14.09.2012 в 18:29
_Boroda_ Дата: Пятница, 14.09.2012, 18:29 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 16715
Репутация: 6504 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Code
=ВПР("Итог 1:";ДВССЫЛ("Лист1!"&"B"&ПОИСКПОЗ(A2;Лист1!A:A;)&":"&"C"&ПОИСКПОЗ(A2;Лист1!A:A;)+6);2;)

Первый столбец должен быть не С, а В
С такой поправкой Ваша формула тоже работает
Code
=ВПР("Итог 1:";ДВССЫЛ("Лист1!"&("B"&ТЕКСТ((ПОИСКПОЗ(A2;Лист1!A:A));0))&":"&("C"&ТЕКСТ((ПОИСКПОЗ(A2;Лист1!A:A))+6;0)));2;ЛОЖЬ)

или так
Code
=ВПР("Итог 1:";СМЕЩ(Лист1!B$1;ПОИСКПОЗ(A2;Лист1!A:A;)-1;;6;2);2;)
К сообщению приложен файл: _1_1.xlsx (14.6 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Code
=ВПР("Итог 1:";ДВССЫЛ("Лист1!"&"B"&ПОИСКПОЗ(A2;Лист1!A:A;)&":"&"C"&ПОИСКПОЗ(A2;Лист1!A:A;)+6);2;)

Первый столбец должен быть не С, а В
С такой поправкой Ваша формула тоже работает
Code
=ВПР("Итог 1:";ДВССЫЛ("Лист1!"&("B"&ТЕКСТ((ПОИСКПОЗ(A2;Лист1!A:A));0))&":"&("C"&ТЕКСТ((ПОИСКПОЗ(A2;Лист1!A:A))+6;0)));2;ЛОЖЬ)

или так
Code
=ВПР("Итог 1:";СМЕЩ(Лист1!B$1;ПОИСКПОЗ(A2;Лист1!A:A;)-1;;6;2);2;)
Автор - _Boroda_
Дата добавления - 14.09.2012 в 18:29
DV Дата: Пятница, 14.09.2012, 19:00 | Сообщение № 6
Группа: Друзья
Ранг: Обитатель
Сообщений: 286
Репутация: 194 ±
Замечаний: 0% ±

Excel 2010 RUS
И все таки без ДВССЫЛ и промежуточных значений:
Code
=ИНДЕКС(Лист1!C$2:C$36;ПОИСКПОЗ("Итог 1:";ИНДЕКС(Лист1!B$2:B$36;ПОИСКПОЗ(A2;Лист1!A$2:A$36;0)):Лист1!B$36;0)+ПОИСКПОЗ(A2;Лист1!A$2:A$36;0)-1)


упс.
Пока писал, уже 3-я ВПР появилась)))
К сообщению приложен файл: 1_2_1.xls (32.5 Kb)


Сообщение отредактировал DV - Пятница, 14.09.2012, 19:03
 
Ответить
СообщениеИ все таки без ДВССЫЛ и промежуточных значений:
Code
=ИНДЕКС(Лист1!C$2:C$36;ПОИСКПОЗ("Итог 1:";ИНДЕКС(Лист1!B$2:B$36;ПОИСКПОЗ(A2;Лист1!A$2:A$36;0)):Лист1!B$36;0)+ПОИСКПОЗ(A2;Лист1!A$2:A$36;0)-1)


упс.
Пока писал, уже 3-я ВПР появилась)))
Автор - DV
Дата добавления - 14.09.2012 в 19:00
ALARMus Дата: Понедельник, 17.09.2012, 15:55 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

А как можно делать ПОИСКПОЗ
если то что искать , к примеру, 234 (значение в А2),
а в искомом диапазоне у всех кодов вначале 0, т.е. 0234


Сообщение отредактировал ALARMus - Понедельник, 17.09.2012, 15:58
 
Ответить
СообщениеА как можно делать ПОИСКПОЗ
если то что искать , к примеру, 234 (значение в А2),
а в искомом диапазоне у всех кодов вначале 0, т.е. 0234
Автор - ALARMus
Дата добавления - 17.09.2012 в 15:55
Serge_007 Дата: Понедельник, 17.09.2012, 15:58 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Code
=ПОИСКПОЗ(234;--A1:A10;)
формула массива
или
Code
=ПОИСКПОЗ("*234";A1:A10;)

Code
=ПОИСКПОЗ("?234";A1:A10;)
простая
К сообщению приложен файл: ALARMus_44.xls (22.5 Kb)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Code
=ПОИСКПОЗ(234;--A1:A10;)
формула массива
или
Code
=ПОИСКПОЗ("*234";A1:A10;)

Code
=ПОИСКПОЗ("?234";A1:A10;)
простая
Автор - Serge_007
Дата добавления - 17.09.2012 в 15:58
ALARMus Дата: Понедельник, 17.09.2012, 16:04 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

а если в ПОИСКПОЗ не само значение в кавычках, а указание на ячейку, которая содержит без 0 в начале.
Я попробовал
ПОИСКПОЗ("0"&A2;D2:E4)


Сообщение отредактировал ALARMus - Понедельник, 17.09.2012, 16:11
 
Ответить
Сообщениеа если в ПОИСКПОЗ не само значение в кавычках, а указание на ячейку, которая содержит без 0 в начале.
Я попробовал
ПОИСКПОЗ("0"&A2;D2:E4)
Автор - ALARMus
Дата добавления - 17.09.2012 в 16:04
_Boroda_ Дата: Понедельник, 17.09.2012, 16:18 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 16715
Репутация: 6504 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Quote (ALARMus)
Я попробовал
ПОИСКПОЗ("0"&A2;D2:E4)

Все правильно, только точку с запятой забыли
ПОИСКПОЗ("0"&A2;D2:E4;)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Quote (ALARMus)
Я попробовал
ПОИСКПОЗ("0"&A2;D2:E4)

Все правильно, только точку с запятой забыли
ПОИСКПОЗ("0"&A2;D2:E4;)
Автор - _Boroda_
Дата добавления - 17.09.2012 в 16:18
ALARMus Дата: Понедельник, 17.09.2012, 16:20 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

; в оригинале есть.
dmn - на тестовом работало - перенес на рабочий файл не работает sad
 
Ответить
Сообщение; в оригинале есть.
dmn - на тестовом работало - перенес на рабочий файл не работает sad
Автор - ALARMus
Дата добавления - 17.09.2012 в 16:20
ALARMus Дата: Понедельник, 17.09.2012, 16:31 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

вот покрутил, что получилось

Если в А2 впишу код 0234 - то ПОИСКПОЗ(А2;диапазон;) - правильно показывает номер строки
А если в А2 оставлю 234 - то ПОИСКПОЗ("0"&A2;диапазон;) - пишет #Н/Д - при этом если нажать на кнопку функции то покажет что искать "0234" в диапазоне (и перечислен диапазон), в котором есть 0234
 
Ответить
Сообщениевот покрутил, что получилось

Если в А2 впишу код 0234 - то ПОИСКПОЗ(А2;диапазон;) - правильно показывает номер строки
А если в А2 оставлю 234 - то ПОИСКПОЗ("0"&A2;диапазон;) - пишет #Н/Д - при этом если нажать на кнопку функции то покажет что искать "0234" в диапазоне (и перечислен диапазон), в котором есть 0234
Автор - ALARMus
Дата добавления - 17.09.2012 в 16:31
ALARMus Дата: Понедельник, 17.09.2012, 16:46 | Сообщение № 13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

вот файлик
К сообщению приложен файл: A-001.xlsx (10.4 Kb)
 
Ответить
Сообщениевот файлик
Автор - ALARMus
Дата добавления - 17.09.2012 в 16:46
_Boroda_ Дата: Понедельник, 17.09.2012, 16:51 | Сообщение № 14
Группа: Админы
Ранг: Местный житель
Сообщений: 16715
Репутация: 6504 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
=ПОИСКПОЗ(--(9&A1);T)
0 и 9 вначале - это совершенно разные вещи. Если вначале числа 0 - это значит, что это не число, а текст, тогда ищем ТЕКСТ 0&A1. А если вначале 9, то это не текст, а ЧИСЛО, а мы ищем текст 9&A1. Для того, чтобы нормально искал, нужно преобразовать его в число двумя минусами, например.
или
=ПОИСКПОЗ(900000+A1;T)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение=ПОИСКПОЗ(--(9&A1);T)
0 и 9 вначале - это совершенно разные вещи. Если вначале числа 0 - это значит, что это не число, а текст, тогда ищем ТЕКСТ 0&A1. А если вначале 9, то это не текст, а ЧИСЛО, а мы ищем текст 9&A1. Для того, чтобы нормально искал, нужно преобразовать его в число двумя минусами, например.
или
=ПОИСКПОЗ(900000+A1;T)
Автор - _Boroda_
Дата добавления - 17.09.2012 в 16:51
ALARMus Дата: Понедельник, 17.09.2012, 16:56 | Сообщение № 15
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

а почему так ?
 
Ответить
Сообщениеа почему так ?
Автор - ALARMus
Дата добавления - 17.09.2012 в 16:56
Serge_007 Дата: Понедельник, 17.09.2012, 17:00 | Сообщение № 16
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Потому что при конкатенации (&) получается текстовая строка ("922553"), а не число (922553)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеПотому что при конкатенации (&) получается текстовая строка ("922553"), а не число (922553)
Автор - Serge_007
Дата добавления - 17.09.2012 в 17:00
ALARMus Дата: Понедельник, 17.09.2012, 17:20 | Сообщение № 17
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

так все же не все вышло гладко - с одной строкой все хорошо, но когда их много, то

Опишу:
для первых 3-х строк (различных значений) найдены соответствия в диапазоне, но если следующая строка №4 в диапазоне не находится, то почему то ПОИСКПОЗ ставит значение предыдущей (последней) найденной строки (в данном примере строки 3) и в итоге сумму берет из той же строки.
 
Ответить
Сообщениетак все же не все вышло гладко - с одной строкой все хорошо, но когда их много, то

Опишу:
для первых 3-х строк (различных значений) найдены соответствия в диапазоне, но если следующая строка №4 в диапазоне не находится, то почему то ПОИСКПОЗ ставит значение предыдущей (последней) найденной строки (в данном примере строки 3) и в итоге сумму берет из той же строки.
Автор - ALARMus
Дата добавления - 17.09.2012 в 17:20
Serge_007 Дата: Понедельник, 17.09.2012, 17:25 | Сообщение № 18
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Не надо описывать, надо прикладывать файл с проблемой

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеНе надо описывать, надо прикладывать файл с проблемой
Автор - Serge_007
Дата добавления - 17.09.2012 в 17:25
ALARMus Дата: Понедельник, 17.09.2012, 17:27 | Сообщение № 19
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

вот новая строка
видимо цепляется за частичное соответствие, а нужно полное совпадение
К сообщению приложен файл: 8381746.xlsx (12.2 Kb)


Сообщение отредактировал ALARMus - Понедельник, 17.09.2012, 17:28
 
Ответить
Сообщениевот новая строка
видимо цепляется за частичное соответствие, а нужно полное совпадение
Автор - ALARMus
Дата добавления - 17.09.2012 в 17:27
Serge_007 Дата: Понедельник, 17.09.2012, 17:34 | Сообщение № 20
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Опять точку с запятой (;) забыли?
А утверждали ведь, что
Quote (ALARMus)
; в оригинале есть.

Так надо:
Code
=ПОИСКПОЗ(--(9&A1);T;)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеОпять точку с запятой (;) забыли?
А утверждали ведь, что
Quote (ALARMus)
; в оригинале есть.

Так надо:
Code
=ПОИСКПОЗ(--(9&A1);T;)
Автор - Serge_007
Дата добавления - 17.09.2012 в 17:34
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Отобразить диапазон из других ячеек.
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!