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

Вход

Регистрация

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

 

= Мир MS Excel/Принадлежность ссылки к столбцу (диапазону) - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Принадлежность ссылки к столбцу (диапазону)
Принадлежность ссылки к столбцу (диапазону)
tanbraun Дата: Среда, 13.02.2013, 09:59 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Всем привет!
Помогите в решении задачи:
Есть область В1:В10 (или просто столбец В) в яч.А1 ссылка =$B$8 как прописать в яч. А2: если ссылка $B$8 принадлежит к области В1:В10 (или столбцу В);1;2
 
Ответить
СообщениеВсем привет!
Помогите в решении задачи:
Есть область В1:В10 (или просто столбец В) в яч.А1 ссылка =$B$8 как прописать в яч. А2: если ссылка $B$8 принадлежит к области В1:В10 (или столбцу В);1;2
Автор - tanbraun
Дата добавления - 13.02.2013 в 09:59
Serge_007 Дата: Среда, 13.02.2013, 10:06 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Напрямую формулами, извлекая текст ссылки, это возможно только начиная с Excel 2013
Для других версий это возможно макросами или макрофункциями

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеНапрямую формулами, извлекая текст ссылки, это возможно только начиная с Excel 2013
Для других версий это возможно макросами или макрофункциями
Автор - Serge_007
Дата добавления - 13.02.2013 в 10:06
Формуляр Дата: Среда, 13.02.2013, 10:24 | Сообщение № 3
Группа: Друзья
Ранг: Ветеран
Сообщений: 832
Репутация: 255 ±
Замечаний: 0% ±

Excel 2003, 2013
tanbraun,
если вы вручную вводите формулу в A1, то как вы можете не знать куда она попадает?!
Понятное дело, что это простыми средствами не решеается, поскольку никому это не нужно.
Сформулируйте задачу на более общем уровне.

Excel 2003 EN, 2013 EN

Сообщение отредактировал Формуляр - Среда, 13.02.2013, 10:24
 
Ответить
Сообщениеtanbraun,
если вы вручную вводите формулу в A1, то как вы можете не знать куда она попадает?!
Понятное дело, что это простыми средствами не решеается, поскольку никому это не нужно.
Сформулируйте задачу на более общем уровне.
Автор - Формуляр
Дата добавления - 13.02.2013 в 10:24
tanbraun Дата: Среда, 13.02.2013, 10:25 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Serge 007 у меня Excel 2013, подскажите какими формулами?
 
Ответить
СообщениеSerge 007 у меня Excel 2013, подскажите какими формулами?
Автор - tanbraun
Дата добавления - 13.02.2013 в 10:25
tanbraun Дата: Среда, 13.02.2013, 10:35 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Цитата (Формуляр)
tanbraun, если вы вручную вводите формулу в A1, то как вы можете не знать куда она попадает?!

Формуляр, язнаю куда она попадает, но для решения мне без разницы , что это будет весь столбец или область (указал для возможного упрощения решения)
 
Ответить
Сообщение
Цитата (Формуляр)
tanbraun, если вы вручную вводите формулу в A1, то как вы можете не знать куда она попадает?!

Формуляр, язнаю куда она попадает, но для решения мне без разницы , что это будет весь столбец или область (указал для возможного упрощения решения)
Автор - tanbraun
Дата добавления - 13.02.2013 в 10:35
Serge_007 Дата: Среда, 13.02.2013, 10:57 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (tanbraun)
подскажите какими формулами?

Для всех версий можно так (см. вложение, макросы должны быть разрешены):
Код
=(ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ОБЛАСТЬ;1;1;);"$";))="B")+(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ОБЛАСТЬ;1;1;);"$";);ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ОБЛАСТЬ;1;1;);"$";));)>0)*(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ОБЛАСТЬ;1;1;);"$";);ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ОБЛАСТЬ;1;1;);"$";));)<11)

Имя ОБЛАСТЬ:
Код
=ПОЛУЧИТЬ.ЯЧЕЙКУ(6;Лист1!$A$1)


Для Excel 2013 будет так (макросы не нужны):
Код
=(ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ТЕКСТФОРМУЛЫ(A1);1;1;);"$";))="B")+(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ТЕКСТФОРМУЛЫ(A1);1;1;);"$";);ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ТЕКСТФОРМУЛЫ(A1);1;1;);"$";));)>0)*(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ТЕКСТФОРМУЛЫ(A1);1;1;);"$";);ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ТЕКСТФОРМУЛЫ(A1);1;1;);"$";));)<11)

Код
=(LEFT(SUBSTITUTE(REPLACE(FORMULATEXT(A1),1,1,),"$",))="B")+(--SUBSTITUTE(SUBSTITUTE(REPLACE(FORMULATEXT(A1),1,1,),"$",),LEFT(SUBSTITUTE(REP LACE(FORMULATEXT(A1),1,1,),"$",)),)>0)*(--SUBSTITUTE(SUBSTITUTE(REPLACE(FORMULATEXT(A1),1,1,),"$",),LEFT(SUBSTITUTE(REPLACE(FORMULATEXT(A1),   1 ,1,),"$",)),)<11)
К сообщению приложен файл: tanbraun.xls (37.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (tanbraun)
подскажите какими формулами?

Для всех версий можно так (см. вложение, макросы должны быть разрешены):
Код
=(ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ОБЛАСТЬ;1;1;);"$";))="B")+(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ОБЛАСТЬ;1;1;);"$";);ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ОБЛАСТЬ;1;1;);"$";));)>0)*(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ОБЛАСТЬ;1;1;);"$";);ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ОБЛАСТЬ;1;1;);"$";));)<11)

Имя ОБЛАСТЬ:
Код
=ПОЛУЧИТЬ.ЯЧЕЙКУ(6;Лист1!$A$1)


Для Excel 2013 будет так (макросы не нужны):
Код
=(ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ТЕКСТФОРМУЛЫ(A1);1;1;);"$";))="B")+(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ТЕКСТФОРМУЛЫ(A1);1;1;);"$";);ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ТЕКСТФОРМУЛЫ(A1);1;1;);"$";));)>0)*(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ТЕКСТФОРМУЛЫ(A1);1;1;);"$";);ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(ТЕКСТФОРМУЛЫ(A1);1;1;);"$";));)<11)

Код
=(LEFT(SUBSTITUTE(REPLACE(FORMULATEXT(A1),1,1,),"$",))="B")+(--SUBSTITUTE(SUBSTITUTE(REPLACE(FORMULATEXT(A1),1,1,),"$",),LEFT(SUBSTITUTE(REP LACE(FORMULATEXT(A1),1,1,),"$",)),)>0)*(--SUBSTITUTE(SUBSTITUTE(REPLACE(FORMULATEXT(A1),1,1,),"$",),LEFT(SUBSTITUTE(REPLACE(FORMULATEXT(A1),   1 ,1,),"$",)),)<11)
Автор - Serge_007
Дата добавления - 13.02.2013 в 10:57
tanbraun Дата: Среда, 13.02.2013, 11:46 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Serge 007, формула с областью (в примере) работает, а вот формула для Excel 2013 (кирилица) у меня не работает (выдает #ИМЯ?).
 
Ответить
СообщениеSerge 007, формула с областью (в примере) работает, а вот формула для Excel 2013 (кирилица) у меня не работает (выдает #ИМЯ?).
Автор - tanbraun
Дата добавления - 13.02.2013 в 11:46
Serge_007 Дата: Среда, 13.02.2013, 11:48 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (tanbraun)
Excel 2013 (кирилица)

у меня нет (а под рукой сейчас вообще нет никакой версии 2013), поэтому проверьте правильность написание названия функции ТЕКСТФОРМУЛЫ()

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (tanbraun)
Excel 2013 (кирилица)

у меня нет (а под рукой сейчас вообще нет никакой версии 2013), поэтому проверьте правильность написание названия функции ТЕКСТФОРМУЛЫ()
Автор - Serge_007
Дата добавления - 13.02.2013 в 11:48
tanbraun Дата: Среда, 13.02.2013, 12:01 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Такой формулы вообще нет.
Я извиняюсь, понимая, что дал не все условия.
Ссылок (как в яч. А1) может быть не одна, а несколько, и каждую отдельно нужно проверить на принадлежность к столбцу.
 
Ответить
СообщениеТакой формулы вообще нет.
Я извиняюсь, понимая, что дал не все условия.
Ссылок (как в яч. А1) может быть не одна, а несколько, и каждую отдельно нужно проверить на принадлежность к столбцу.
Автор - tanbraun
Дата добавления - 13.02.2013 в 12:01
Serge_007 Дата: Среда, 13.02.2013, 12:07 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (tanbraun)
Такой формулы вообще нет

Естественно нет. Есть такая ФУНКЦИЯ. Проверьте в Excel как правильно должно писаться её название на русском языке

Цитата (tanbraun)
Ссылок (как в яч. А1) может быть не одна

Количество не играет роли

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (tanbraun)
Такой формулы вообще нет

Естественно нет. Есть такая ФУНКЦИЯ. Проверьте в Excel как правильно должно писаться её название на русском языке

Цитата (tanbraun)
Ссылок (как в яч. А1) может быть не одна

Количество не играет роли
Автор - Serge_007
Дата добавления - 13.02.2013 в 12:07
tanbraun Дата: Среда, 13.02.2013, 12:28 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Эта функция называется Ф.Текст.

Все работает и не зависемо от количества ссылок.
Очень выручили, большая вам благодарность!
 
Ответить
СообщениеЭта функция называется Ф.Текст.

Все работает и не зависемо от количества ссылок.
Очень выручили, большая вам благодарность!
Автор - tanbraun
Дата добавления - 13.02.2013 в 12:28
tanbraun Дата: Среда, 13.02.2013, 13:18 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Serge 007 еще один вопросик: а как сделать, чтобы при растягивании яч. А2 также выдавало правильный результат, при условии, что когда скопируем яч. А1 и А2,А3... не пришлось менять ссылку.
К сообщению приложен файл: _2.xlsx (8.5 Kb)
 
Ответить
СообщениеSerge 007 еще один вопросик: а как сделать, чтобы при растягивании яч. А2 также выдавало правильный результат, при условии, что когда скопируем яч. А1 и А2,А3... не пришлось менять ссылку.
Автор - tanbraun
Дата добавления - 13.02.2013 в 13:18
Serge_007 Дата: Среда, 13.02.2013, 14:41 | Сообщение № 13
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
В А2:А5 надо прописать формулу:
Код
=(ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(Ф.ТЕКСТ(A1);1;1;);"$";))="B")+(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(Ф.ТЕКСТ(A1);1;1;);"$";);ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(Ф.ТЕКСТ(A1);1;1;);"$";));)>0)*(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(Ф.ТЕКСТ(A1);1;1;);"$";);ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(Ф.ТЕКСТ(A1);1;1;);"$";));)<11)
и копировать диапазон А1:А5 вниз целиком

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеВ А2:А5 надо прописать формулу:
Код
=(ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(Ф.ТЕКСТ(A1);1;1;);"$";))="B")+(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(Ф.ТЕКСТ(A1);1;1;);"$";);ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(Ф.ТЕКСТ(A1);1;1;);"$";));)>0)*(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(Ф.ТЕКСТ(A1);1;1;);"$";);ЛЕВСИМВ(ПОДСТАВИТЬ(ЗАМЕНИТЬ(Ф.ТЕКСТ(A1);1;1;);"$";));)<11)
и копировать диапазон А1:А5 вниз целиком
Автор - Serge_007
Дата добавления - 13.02.2013 в 14:41
tanbraun Дата: Среда, 13.02.2013, 15:18 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Тоесть формулу только прописывать, по другому никак?
(просто у меня область А2:А5 постоянно меняется и каждый раз прописывать формулу не очень удобно)
 
Ответить
СообщениеТоесть формулу только прописывать, по другому никак?
(просто у меня область А2:А5 постоянно меняется и каждый раз прописывать формулу не очень удобно)
Автор - tanbraun
Дата добавления - 13.02.2013 в 15:18
Serge_007 Дата: Среда, 13.02.2013, 15:36 | Сообщение № 15
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Приложите нормальный пример того что есть и того что необходимо получить

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеПриложите нормальный пример того что есть и того что необходимо получить
Автор - Serge_007
Дата добавления - 13.02.2013 в 15:36
tanbraun Дата: Среда, 13.02.2013, 16:59 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Нужно чтобы при добавлении строк и при копировании всего диапазона А11:I17, не надо было менять ссылки. (материалов может быть несколько)
К сообщению приложен файл: _3.xlsx (16.9 Kb)
 
Ответить
СообщениеНужно чтобы при добавлении строк и при копировании всего диапазона А11:I17, не надо было менять ссылки. (материалов может быть несколько)
Автор - tanbraun
Дата добавления - 13.02.2013 в 16:59
Serge_007 Дата: Среда, 13.02.2013, 17:43 | Сообщение № 17
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
В Вашем "примере" и так всё будет корректно работать при добавлении строк при закреплении $H$11, а про то куда копировать диапазон Вы скромно умолчали

Чем такая формула не подходит?
Код
=ВПР(B12;L$6:Q$18;ПОИСКПОЗ(H$11;L$5:Q$5;);)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеВ Вашем "примере" и так всё будет корректно работать при добавлении строк при закреплении $H$11, а про то куда копировать диапазон Вы скромно умолчали

Чем такая формула не подходит?
Код
=ВПР(B12;L$6:Q$18;ПОИСКПОЗ(H$11;L$5:Q$5;);)
Автор - Serge_007
Дата добавления - 13.02.2013 в 17:43
tanbraun Дата: Четверг, 14.02.2013, 08:18 | Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Если закрепить яч $H$11, а затем скопировать А10:I16 в низ, то надо менять ссылку $H$11 на $H$18.

Формула
Цитата (Serge_007)
=ВПР(B12;L$6:Q$18;ПОИСКПОЗ(H$11;L$5:Q$5;);)
хороша, но при одинаковых ценах на дуб и березу она выдает произвольный результат.
 
Ответить
СообщениеЕсли закрепить яч $H$11, а затем скопировать А10:I16 в низ, то надо менять ссылку $H$11 на $H$18.

Формула
Цитата (Serge_007)
=ВПР(B12;L$6:Q$18;ПОИСКПОЗ(H$11;L$5:Q$5;);)
хороша, но при одинаковых ценах на дуб и березу она выдает произвольный результат.
Автор - tanbraun
Дата добавления - 14.02.2013 в 08:18
Serge_007 Дата: Четверг, 14.02.2013, 10:17 | Сообщение № 19
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (tanbraun)
надо менять ссылку $H$11 на $H$18
Зачем? Каким образом это отображено в Вашем
Цитата (Serge_007)
нормальном примере того что есть и того что необходимо получить
?

Цитата (tanbraun)
при одинаковых ценах на дуб и березу она выдает произвольный результат
Не произвольный, а первый слева

Тогда так (формула массива):
Код
=ВПР(B12;L$6:Q$18;ПОИСКПОЗ(H$11&$A$5;L$5:Q$5&L$4:Q$4;);)
К сообщению приложен файл: tanbraun2_.xls (50.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (tanbraun)
надо менять ссылку $H$11 на $H$18
Зачем? Каким образом это отображено в Вашем
Цитата (Serge_007)
нормальном примере того что есть и того что необходимо получить
?

Цитата (tanbraun)
при одинаковых ценах на дуб и березу она выдает произвольный результат
Не произвольный, а первый слева

Тогда так (формула массива):
Код
=ВПР(B12;L$6:Q$18;ПОИСКПОЗ(H$11&$A$5;L$5:Q$5&L$4:Q$4;);)
Автор - Serge_007
Дата добавления - 14.02.2013 в 10:17
tanbraun Дата: Четверг, 14.02.2013, 11:50 | Сообщение № 20
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Спасибо Сергей, то что надо.
Но если в функции ВПР (интервальный просмотр) и ПОИСКПОЗ (тип сопоставления) не поставить 0, то при растяжке на 3-й строке выдает результат НД.
 
Ответить
СообщениеСпасибо Сергей, то что надо.
Но если в функции ВПР (интервальный просмотр) и ПОИСКПОЗ (тип сопоставления) не поставить 0, то при растяжке на 3-й строке выдает результат НД.
Автор - tanbraun
Дата добавления - 14.02.2013 в 11:50
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Принадлежность ссылки к столбцу (диапазону)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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