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

Вход

Регистрация

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

 

= Мир MS Excel/Адреса ячеек - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин  
Мир MS Excel » Работа и общение » Мозговой штурм » Адреса ячеек (Одной формулой)
Адреса ячеек
ZORRO2005 Дата: Суббота, 16.06.2012, 20:51 | Сообщение № 1
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
Друзья, нужно вытащить адреса ячеек из диапазона по условию.
Решение нужно одной формулой.
К сообщению приложен файл: 1227568.xls (25.0 Kb)
 
Ответить
СообщениеДрузья, нужно вытащить адреса ячеек из диапазона по условию.
Решение нужно одной формулой.
Автор - ZORRO2005
Дата добавления - 16.06.2012 в 20:51
MCH Дата: Воскресенье, 17.06.2012, 01:10 | Сообщение № 2
Группа: Админы
Ранг: Старожил
Сообщений: 2003
Репутация: 751 ±
Замечаний: ±

Quote (ZORRO2005)
Подкину еще задачку "Адреса ячеек".

196 174


Сообщение отредактировал MCH - Воскресенье, 17.06.2012, 09:06
 
Ответить
Сообщение
Quote (ZORRO2005)
Подкину еще задачку "Адреса ячеек".

196 174
Автор - MCH
Дата добавления - 17.06.2012 в 01:10
ZORRO2005 Дата: Воскресенье, 17.06.2012, 22:06 | Сообщение № 3
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
186 164


Сообщение отредактировал ZORRO2005 - Воскресенье, 17.06.2012, 23:54
 
Ответить
Сообщение186 164
Автор - ZORRO2005
Дата добавления - 17.06.2012 в 22:06
kim Дата: Понедельник, 18.06.2012, 20:44 | Сообщение № 4
Группа: Друзья
Ранг: Обитатель
Сообщений: 371
Репутация: 160 ±
Замечаний: 0% ±

Excel 2010
Чего добиваемся? Скорости? Сокращения? Универсальности?
Если сокращать, для 2007-2010-ого - 138...

Сцяна - гэта тая ж прыступка. Проста трэба дарасьці да яе.
 
Ответить
СообщениеЧего добиваемся? Скорости? Сокращения? Универсальности?
Если сокращать, для 2007-2010-ого - 138...
Автор - kim
Дата добавления - 18.06.2012 в 20:44
Serge_007 Дата: Понедельник, 18.06.2012, 20:47 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Quote (kim)
Чего добиваемся? Скорости? Сокращения? Универсальности?

Обычно минимального кол-ва знаков smile если не указано иное

Quote (Serge_007)
для 2007-2010-ого - 138

Но стараемся что бы во всех версиях работало

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Quote (kim)
Чего добиваемся? Скорости? Сокращения? Универсальности?

Обычно минимального кол-ва знаков smile если не указано иное

Quote (Serge_007)
для 2007-2010-ого - 138

Но стараемся что бы во всех версиях работало
Автор - Serge_007
Дата добавления - 18.06.2012 в 20:47
kim Дата: Понедельник, 18.06.2012, 20:56 | Сообщение № 6
Группа: Друзья
Ранг: Обитатель
Сообщений: 371
Репутация: 160 ±
Замечаний: 0% ±

Excel 2010
Quote (Serge_007)
Но стараемся что бы во всех версиях работало

Тогда 158 156

Сцяна - гэта тая ж прыступка. Проста трэба дарасьці да яе.
 
Ответить
Сообщение
Quote (Serge_007)
Но стараемся что бы во всех версиях работало

Тогда 158 156
Автор - kim
Дата добавления - 18.06.2012 в 20:56
MCH Дата: Понедельник, 18.06.2012, 21:33 | Сообщение № 7
Группа: Админы
Ранг: Старожил
Сообщений: 2003
Репутация: 751 ±
Замечаний: ±

Уточняющий вопрос: всегда ссылаемся на диапазон B$2:D$13 - 174
или допустимо СТРОКА($2:$13), СТОЛБЕЦ(B:D) - 155
 
Ответить
СообщениеУточняющий вопрос: всегда ссылаемся на диапазон B$2:D$13 - 174
или допустимо СТРОКА($2:$13), СТОЛБЕЦ(B:D) - 155
Автор - MCH
Дата добавления - 18.06.2012 в 21:33
Serge_007 Дата: Понедельник, 18.06.2012, 21:44 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Quote (MCH)
всегда ссылаемся на диапазон B$2:D$13?

Мы вроде договорились "ЗАМЕНИТЕЛИ" не использовать?

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Quote (MCH)
всегда ссылаемся на диапазон B$2:D$13?

Мы вроде договорились "ЗАМЕНИТЕЛИ" не использовать?
Автор - Serge_007
Дата добавления - 18.06.2012 в 21:44
kim Дата: Понедельник, 18.06.2012, 21:56 | Сообщение № 9
Группа: Друзья
Ранг: Обитатель
Сообщений: 371
Репутация: 160 ±
Замечаний: 0% ±

Excel 2010
Quote (Serge_007)
Мы вроде договорились "ЗАМЕНИТЕЛИ" не использовать?

Что за "ЗАМЕНИТЕЛИ"? Когда договаривались?
Михаил, СТОЛБЕЦ(B:D) в 2003-ем не пройдет.

Сцяна - гэта тая ж прыступка. Проста трэба дарасьці да яе.
 
Ответить
Сообщение
Quote (Serge_007)
Мы вроде договорились "ЗАМЕНИТЕЛИ" не использовать?

Что за "ЗАМЕНИТЕЛИ"? Когда договаривались?
Михаил, СТОЛБЕЦ(B:D) в 2003-ем не пройдет.
Автор - kim
Дата добавления - 18.06.2012 в 21:56
Serge_007 Дата: Понедельник, 18.06.2012, 22:03 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Quote (kim)
Что за "ЗАМЕНИТЕЛИ"? Когда договаривались?

См. здесь

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Quote (kim)
Что за "ЗАМЕНИТЕЛИ"? Когда договаривались?

См. здесь
Автор - Serge_007
Дата добавления - 18.06.2012 в 22:03
MCH Дата: Понедельник, 18.06.2012, 22:03 | Сообщение № 11
Группа: Админы
Ранг: Старожил
Сообщений: 2003
Репутация: 751 ±
Замечаний: ±

Quote (kim)
Что за "ЗАМЕНИТЕЛИ"? Когда договаривались?

Это значит, что ссылаемся на исходный диапазон : МИН(B$2:D$13), СТРОКА(B$2:D$13), СТОЛБЕЦ(B$2:D$13) вместо МИН(B:D), СТРОКА($2:$13), СТОЛБЕЦ(B:D)

Quote (kim)
Михаил, СТОЛБЕЦ(B:D) в 2003-ем не пройдет.

Почему не пройдет? Результатом будет массив {2;3;4}, а вот СТРОКА(B:D) работать не будет


Сообщение отредактировал MCH - Понедельник, 18.06.2012, 22:06
 
Ответить
Сообщение
Quote (kim)
Что за "ЗАМЕНИТЕЛИ"? Когда договаривались?

Это значит, что ссылаемся на исходный диапазон : МИН(B$2:D$13), СТРОКА(B$2:D$13), СТОЛБЕЦ(B$2:D$13) вместо МИН(B:D), СТРОКА($2:$13), СТОЛБЕЦ(B:D)

Quote (kim)
Михаил, СТОЛБЕЦ(B:D) в 2003-ем не пройдет.

Почему не пройдет? Результатом будет массив {2;3;4}, а вот СТРОКА(B:D) работать не будет
Автор - MCH
Дата добавления - 18.06.2012 в 22:03
kim Дата: Понедельник, 18.06.2012, 22:13 | Сообщение № 12
Группа: Друзья
Ранг: Обитатель
Сообщений: 371
Репутация: 160 ±
Замечаний: 0% ±

Excel 2010
Сложно у нас как-то. Заменители какие-то...
А массив констант тоже нельзя подставлять?

Сцяна - гэта тая ж прыступка. Проста трэба дарасьці да яе.
 
Ответить
СообщениеСложно у нас как-то. Заменители какие-то...
А массив констант тоже нельзя подставлять?
Автор - kim
Дата добавления - 18.06.2012 в 22:13
Serge_007 Дата: Понедельник, 18.06.2012, 22:15 | Сообщение № 13
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Quote (kim)
массив констант тоже нельзя подставлять?

Можно, если они не связаны с исходным диапазоном

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Quote (kim)
массив констант тоже нельзя подставлять?

Можно, если они не связаны с исходным диапазоном
Автор - Serge_007
Дата добавления - 18.06.2012 в 22:15
ZORRO2005 Дата: Понедельник, 18.06.2012, 23:00 | Сообщение № 14
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
Quote (kim)
Чего добиваемся? Скорости? Сокращения? Универсальности?
Сокращения, универсальности, нестандартного подхода к решению, красоты решения и т.п.
Quote (Serge_007)
Цитата, kim писал(а):массив констант тоже нельзя подставлять? Можно, если они не связаны с исходным диапазоном
Все верно.

МСН, вскрывайся первым.


Сообщение отредактировал ZORRO2005 - Понедельник, 18.06.2012, 23:01
 
Ответить
Сообщение
Quote (kim)
Чего добиваемся? Скорости? Сокращения? Универсальности?
Сокращения, универсальности, нестандартного подхода к решению, красоты решения и т.п.
Quote (Serge_007)
Цитата, kim писал(а):массив констант тоже нельзя подставлять? Можно, если они не связаны с исходным диапазоном
Все верно.

МСН, вскрывайся первым.
Автор - ZORRO2005
Дата добавления - 18.06.2012 в 23:00
MCH Дата: Понедельник, 18.06.2012, 23:09 | Сообщение № 15
Группа: Админы
Ранг: Старожил
Сообщений: 2003
Репутация: 751 ±
Замечаний: ±

Quote (ZORRO2005)
МСН, вскрывайся первым.
К сообщению приложен файл: 7677248.xls (31.5 Kb)
 
Ответить
Сообщение
Quote (ZORRO2005)
МСН, вскрывайся первым.
Автор - MCH
Дата добавления - 18.06.2012 в 23:09
kim Дата: Понедельник, 18.06.2012, 23:16 | Сообщение № 16
Группа: Друзья
Ранг: Обитатель
Сообщений: 371
Репутация: 160 ±
Замечаний: 0% ±

Excel 2010
Решения врядли будут существенно отличаться (вобщем как и предполагал - придумать что-то новенькое сложновато)
=АДРЕС(НАИМЕНЬШИЙ(ЕСЛИ(МИН(B:D)=B$2:D$13;СТРОКА(A$2:A$13));СТРОКА(A1));ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(МИН(B:D)=B$2:D$13;{2;3;4}+СТРОКА(A$2:A$13)*12);СТРОКА(A1));12))
Правила кажысь нарушил smile

Сцяна - гэта тая ж прыступка. Проста трэба дарасьці да яе.
 
Ответить
СообщениеРешения врядли будут существенно отличаться (вобщем как и предполагал - придумать что-то новенькое сложновато)
=АДРЕС(НАИМЕНЬШИЙ(ЕСЛИ(МИН(B:D)=B$2:D$13;СТРОКА(A$2:A$13));СТРОКА(A1));ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(МИН(B:D)=B$2:D$13;{2;3;4}+СТРОКА(A$2:A$13)*12);СТРОКА(A1));12))
Правила кажысь нарушил smile
Автор - kim
Дата добавления - 18.06.2012 в 23:16
ZORRO2005 Дата: Понедельник, 18.06.2012, 23:50 | Сообщение № 17
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
Quote (kim)
Решения врядли будут существенно отличаться

Да, решения все похожи.
Пытался вначале сделать с помощью ЯЧЕЙКА("адрес";...), но Excel стал ругаться на кол-во уровней вложенности.
К сообщению приложен файл: 1539316.xls (26.5 Kb)


Сообщение отредактировал ZORRO2005 - Понедельник, 18.06.2012, 23:53
 
Ответить
Сообщение
Quote (kim)
Решения врядли будут существенно отличаться

Да, решения все похожи.
Пытался вначале сделать с помощью ЯЧЕЙКА("адрес";...), но Excel стал ругаться на кол-во уровней вложенности.
Автор - ZORRO2005
Дата добавления - 18.06.2012 в 23:50
kim Дата: Вторник, 19.06.2012, 00:06 | Сообщение № 18
Группа: Друзья
Ранг: Обитатель
Сообщений: 371
Репутация: 160 ±
Замечаний: 0% ±

Excel 2010
Если бы не пытался вводить сразу массив значений, а сделал растягиваемую формулу может быть и не ругался бы smile

Сцяна - гэта тая ж прыступка. Проста трэба дарасьці да яе.
 
Ответить
СообщениеЕсли бы не пытался вводить сразу массив значений, а сделал растягиваемую формулу может быть и не ругался бы smile
Автор - kim
Дата добавления - 19.06.2012 в 00:06
ZORRO2005 Дата: Вторник, 19.06.2012, 01:13 | Сообщение № 19
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
Quote (kim)
Если бы не пытался вводить сразу массив значений, а сделал растягиваемую формулу может быть и не ругался бы

Хорошо, тогда в продолжение темы такой вопрос.
Надо представить матрицу B2:D13 в виде вертикального массива:{82:80:90:64:4:45:32:22:"февраль":2:20:2:0:2:18:9:44:0:"январь":2:55:2:2:2:76:"январь":3:85:72:68:4:5:80:75:2:30}
Использовал ДВССЫЛ и АДРЕС
Code
=ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ(ЕСЛИ(B2:D13<>"~";СТРОКА(B2:D13));СТРОКА(B1:B36));ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(B2:D13<>"~";СТРОКА(B2:D13)*10+СТОЛБЕЦ(B2:D13));СТРОКА(B1:B36));10)))

Но так как диапазон смешанный, выдает ошибку.(Если бы были только числа, то Ч перед ДВССЫЛ помогает).

Хотелось бы придумать универсальную формулу покороче превращающую матрицу в вертикальный массив.
 
Ответить
Сообщение
Quote (kim)
Если бы не пытался вводить сразу массив значений, а сделал растягиваемую формулу может быть и не ругался бы

Хорошо, тогда в продолжение темы такой вопрос.
Надо представить матрицу B2:D13 в виде вертикального массива:{82:80:90:64:4:45:32:22:"февраль":2:20:2:0:2:18:9:44:0:"январь":2:55:2:2:2:76:"январь":3:85:72:68:4:5:80:75:2:30}
Использовал ДВССЫЛ и АДРЕС
Code
=ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ(ЕСЛИ(B2:D13<>"~";СТРОКА(B2:D13));СТРОКА(B1:B36));ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(B2:D13<>"~";СТРОКА(B2:D13)*10+СТОЛБЕЦ(B2:D13));СТРОКА(B1:B36));10)))

Но так как диапазон смешанный, выдает ошибку.(Если бы были только числа, то Ч перед ДВССЫЛ помогает).

Хотелось бы придумать универсальную формулу покороче превращающую матрицу в вертикальный массив.
Автор - ZORRO2005
Дата добавления - 19.06.2012 в 01:13
MCH Дата: Вторник, 19.06.2012, 07:31 | Сообщение № 20
Группа: Админы
Ранг: Старожил
Сообщений: 2003
Репутация: 751 ±
Замечаний: ±

Code
=ИНДЕКС(A:D;Ч(ИНДЕКС(НАИМЕНЬШИЙ((B2:D13<>"~")*СТРОКА($2:$13);СТРОКА(1:36));));Ч(ИНДЕКС(ОСТАТ(НАИМЕНЬШИЙ((B2:D13<>"~")*СТРОКА($2:$13)*5+СТОЛБЕЦ(B:D);СТРОКА(1:36));5);)))


Code
=ИНДЕКС(B2:D13;Ч(ИНДЕКС((СТРОКА(1:36)-1)/3+1;));Ч(ИНДЕКС(ОСТАТ(СТРОКА(1:36)-1;3)+1;)))


Code
=ИНДЕКС(B2:D13;Ч(ИНДЕКС((СТРОКА(СМЕЩ(A1;;;ЧСТРОК(B2:D13)*ЧИСЛСТОЛБ(B2:D13)))-1)/ЧИСЛСТОЛБ(B2:D13)+1;));Ч(ИНДЕКС(ОСТАТ(СТРОКА(СМЕЩ(A1;;;ЧСТРОК(B2:D13)*ЧИСЛСТОЛБ(B2:D13)))-1;ЧИСЛСТОЛБ(B2:D13))+1;)))


Code
=ИНДЕКС(B2:D13;Ч(ИНДЕКС((СТРОКА(ДВССЫЛ("1:"&СЧЁТЗ(B2:D13&"")))-1)/ЧИСЛСТОЛБ(B2:D13)+1;));Ч(ИНДЕКС(ОСТАТ(СТРОКА(ДВССЫЛ("1:"&СЧЁТЗ(B2:D13&"")))-1;ЧИСЛСТОЛБ(B2:D13))+1;)))


Сообщение отредактировал MCH - Вторник, 19.06.2012, 08:29
 
Ответить
Сообщение
Code
=ИНДЕКС(A:D;Ч(ИНДЕКС(НАИМЕНЬШИЙ((B2:D13<>"~")*СТРОКА($2:$13);СТРОКА(1:36));));Ч(ИНДЕКС(ОСТАТ(НАИМЕНЬШИЙ((B2:D13<>"~")*СТРОКА($2:$13)*5+СТОЛБЕЦ(B:D);СТРОКА(1:36));5);)))


Code
=ИНДЕКС(B2:D13;Ч(ИНДЕКС((СТРОКА(1:36)-1)/3+1;));Ч(ИНДЕКС(ОСТАТ(СТРОКА(1:36)-1;3)+1;)))


Code
=ИНДЕКС(B2:D13;Ч(ИНДЕКС((СТРОКА(СМЕЩ(A1;;;ЧСТРОК(B2:D13)*ЧИСЛСТОЛБ(B2:D13)))-1)/ЧИСЛСТОЛБ(B2:D13)+1;));Ч(ИНДЕКС(ОСТАТ(СТРОКА(СМЕЩ(A1;;;ЧСТРОК(B2:D13)*ЧИСЛСТОЛБ(B2:D13)))-1;ЧИСЛСТОЛБ(B2:D13))+1;)))


Code
=ИНДЕКС(B2:D13;Ч(ИНДЕКС((СТРОКА(ДВССЫЛ("1:"&СЧЁТЗ(B2:D13&"")))-1)/ЧИСЛСТОЛБ(B2:D13)+1;));Ч(ИНДЕКС(ОСТАТ(СТРОКА(ДВССЫЛ("1:"&СЧЁТЗ(B2:D13&"")))-1;ЧИСЛСТОЛБ(B2:D13))+1;)))
Автор - MCH
Дата добавления - 19.06.2012 в 07:31
Мир MS Excel » Работа и общение » Мозговой штурм » Адреса ячеек (Одной формулой)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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