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

Вход

Регистрация

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

 

= Мир MS Excel/Значение из последней заполненной ячейки - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Значение из последней заполненной ячейки (Формулы)
Значение из последней заполненной ячейки
RAN Дата: Понедельник, 25.11.2013, 14:05 | Сообщение № 1
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Мяу!
Помогите дописать формулу (динамически определить столбец).
К сообщению приложен файл: 2349526.xlsx (9.3 Kb)

Быть или не быть, вот в чем загвоздка!
 
Ответить
СообщениеМяу!
Помогите дописать формулу (динамически определить столбец).
Автор - RAN
Дата добавления - 25.11.2013 в 14:05
Nic70y Дата: Понедельник, 25.11.2013, 14:37 | Сообщение № 2
Группа: Друзья
Ранг: Экселист
Сообщений: 9050
Репутация: 2383 ±
Замечаний: 0% ±

Excel 2010
Код
=ИНДЕКС(G$3:M$4;ПОИСКПОЗ(A3;F$3:F$4;0);МАКС(ЕСЛИ(A3=F$3:F$4;ЕСЛИ(G$3:M$4<>"";СТОЛБЕЦ(G$3:M$4))))-6)
массивная пойдет?

ЮMoney 41001841029809
 
Ответить
Сообщение
Код
=ИНДЕКС(G$3:M$4;ПОИСКПОЗ(A3;F$3:F$4;0);МАКС(ЕСЛИ(A3=F$3:F$4;ЕСЛИ(G$3:M$4<>"";СТОЛБЕЦ(G$3:M$4))))-6)
массивная пойдет?
Автор - Nic70y
Дата добавления - 25.11.2013 в 14:37
AlexM Дата: Понедельник, 25.11.2013, 14:45 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Обычная формула (не массивная)
Код
=ВПР(A3;$F$3:$M$4;СЧЁТЗ(ИНДЕКС($F$3:$M$4;ПОИСКПОЗ(A3;$F$3:$F$4;);)))


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Понедельник, 25.11.2013, 14:48
 
Ответить
СообщениеОбычная формула (не массивная)
Код
=ВПР(A3;$F$3:$M$4;СЧЁТЗ(ИНДЕКС($F$3:$M$4;ПОИСКПОЗ(A3;$F$3:$F$4;);)))
Автор - AlexM
Дата добавления - 25.11.2013 в 14:45
_Boroda_ Дата: Понедельник, 25.11.2013, 14:49 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 16790
Репутация: 6557 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
С учетом того, что там цены. Тоже обычная. И работает в случае, если есть пустые значения.
Код
=1/ПРОСМОТР(2;ИНДЕКС(1/G$3:M$4;ПОИСКПОЗ(A3;F$3:F$4;);))
К сообщению приложен файл: 2349526_2.xlsx (10.1 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеС учетом того, что там цены. Тоже обычная. И работает в случае, если есть пустые значения.
Код
=1/ПРОСМОТР(2;ИНДЕКС(1/G$3:M$4;ПОИСКПОЗ(A3;F$3:F$4;);))
Автор - _Boroda_
Дата добавления - 25.11.2013 в 14:49
Nikkei Дата: Понедельник, 25.11.2013, 18:19 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
Монстр, конечно, и пропуски не учтет, но тоже работает.
Код
=OFFSET(INDIRECT(ADDRESS(MATCH(A3,F3:F4,0)+ROW(F3)-1,COLUMN(F3))),,COUNTA(F3:M3)-1)
К сообщению приложен файл: Otv1.xlsx (10.0 Kb)
 
Ответить
СообщениеМонстр, конечно, и пропуски не учтет, но тоже работает.
Код
=OFFSET(INDIRECT(ADDRESS(MATCH(A3,F3:F4,0)+ROW(F3)-1,COLUMN(F3))),,COUNTA(F3:M3)-1)
Автор - Nikkei
Дата добавления - 25.11.2013 в 18:19
Wasilich Дата: Понедельник, 25.11.2013, 18:56 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1232
Репутация: 326 ±
Замечаний: 0% ±

2003
Если построчно, и так можно:
Код
=ИНДЕКС(G3:M3;ПОИСКПОЗ(1E+306;G3:M3;1))
если сюда заглянуть.


Сообщение отредактировал Wasilic - Понедельник, 25.11.2013, 19:09
 
Ответить
СообщениеЕсли построчно, и так можно:
Код
=ИНДЕКС(G3:M3;ПОИСКПОЗ(1E+306;G3:M3;1))
если сюда заглянуть.
Автор - Wasilich
Дата добавления - 25.11.2013 в 18:56
Serge_007 Дата: Понедельник, 25.11.2013, 19:47 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата Wasilic, 25.11.2013 в 18:56, в сообщении № 6
если сюда заглянуть.
А если сюда?

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата Wasilic, 25.11.2013 в 18:56, в сообщении № 6
если сюда заглянуть.
А если сюда?
Автор - Serge_007
Дата добавления - 25.11.2013 в 19:47
RAN Дата: Понедельник, 25.11.2013, 19:53 | Сообщение № 8
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Всем спасибо.
Остановлюсь, пожалуй на формуле Алекса. Она мне как-то ближе.

Саш, разжуй, плз, работу твоей формулы.
Почему это
Код
ИНДЕКС(1/G$3:M$4;ПОИСКПОЗ(A3;F$3:F$4;);)

в ячейке возвращает число, а внутри ПРОСМОТРа - массив?
А уж почему ПРОСМОТР вместо 1 (ближайшего меньшего) вытягивает 0,25 - вообще не понял.

PS Я искал. Честно... Но поиск только макросы выдал. А кузнец нам не нужен. :(

Быть или не быть, вот в чем загвоздка!

Сообщение отредактировал RAN - Понедельник, 25.11.2013, 20:34
 
Ответить
СообщениеВсем спасибо.
Остановлюсь, пожалуй на формуле Алекса. Она мне как-то ближе.

Саш, разжуй, плз, работу твоей формулы.
Почему это
Код
ИНДЕКС(1/G$3:M$4;ПОИСКПОЗ(A3;F$3:F$4;);)

в ячейке возвращает число, а внутри ПРОСМОТРа - массив?
А уж почему ПРОСМОТР вместо 1 (ближайшего меньшего) вытягивает 0,25 - вообще не понял.

PS Я искал. Честно... Но поиск только макросы выдал. А кузнец нам не нужен. :(
Автор - RAN
Дата добавления - 25.11.2013 в 19:53
_Boroda_ Дата: Понедельник, 25.11.2013, 22:36 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16790
Репутация: 6557 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Цитата RAN, 25.11.2013 в 19:53, в сообщении № 8
Саш, разжуй, плз, работу твоей формулы

Жую (мням-мням):
Код
=1/ПРОСМОТР(2;ИНДЕКС(1/G$3:M$4;ПОИСКПОЗ(A3;F$3:F$4;);))

1. Ну, с ПОИСКПОЗ, я думаю, понятно – ищет номер строки
2. 1/G$3:M$4 – там, где нули или пусто даст ошибку #ДЕЛ/0!, а где не нули – число, обратное стоимости. Делается так для того, чтобы ошибку получить.
3. ИНДЕКС(1/G$3:M$4;ПОИСКПОЗ(A3;F$3:F$4;);) – из массива 2. выковыриваем строку 1., а дальше хитрость – если у ИНДЕКСА оба аргумента числа или один из аргументов опущен (если первым аргументом выступает строка или столбец), то ИНДЕКС показывает нам то, что находится на пересечении указанных строки и столбца, а вот если один из аргументов ИНДЕКСа равен нулю (или, что однохренственно, стоит точка с запятой, а за ней ничего нет – как у меня), то ИНДЕКС радует нас массивом. То есть, ИНДЕКС(A1:C5;3;0) даст нам массив А3;В3;С3. В нашей формуле мы получаем (если смотреть в моем файле) массив {1;1/2;1/3;1/2;#ДЕЛ/0!;1/6;#ДЕЛ/0!}
4. ПРОСМОТР из массива с ошибками ведет себя не совсем так, как написано в справке (там про ошибки скромно умалчивают) – она просто тупо выдает самое последнее значение из просматриваемого вектора, не являющееся ошибкой. То есть, для нас – 1/6
5. Остается только перевернуть найденную 1/6-ю обратно с головы на ноги, что мы и делаем, деля единицу на 1/6 – 1/ 1/6=6

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата RAN, 25.11.2013 в 19:53, в сообщении № 8
Саш, разжуй, плз, работу твоей формулы

Жую (мням-мням):
Код
=1/ПРОСМОТР(2;ИНДЕКС(1/G$3:M$4;ПОИСКПОЗ(A3;F$3:F$4;);))

1. Ну, с ПОИСКПОЗ, я думаю, понятно – ищет номер строки
2. 1/G$3:M$4 – там, где нули или пусто даст ошибку #ДЕЛ/0!, а где не нули – число, обратное стоимости. Делается так для того, чтобы ошибку получить.
3. ИНДЕКС(1/G$3:M$4;ПОИСКПОЗ(A3;F$3:F$4;);) – из массива 2. выковыриваем строку 1., а дальше хитрость – если у ИНДЕКСА оба аргумента числа или один из аргументов опущен (если первым аргументом выступает строка или столбец), то ИНДЕКС показывает нам то, что находится на пересечении указанных строки и столбца, а вот если один из аргументов ИНДЕКСа равен нулю (или, что однохренственно, стоит точка с запятой, а за ней ничего нет – как у меня), то ИНДЕКС радует нас массивом. То есть, ИНДЕКС(A1:C5;3;0) даст нам массив А3;В3;С3. В нашей формуле мы получаем (если смотреть в моем файле) массив {1;1/2;1/3;1/2;#ДЕЛ/0!;1/6;#ДЕЛ/0!}
4. ПРОСМОТР из массива с ошибками ведет себя не совсем так, как написано в справке (там про ошибки скромно умалчивают) – она просто тупо выдает самое последнее значение из просматриваемого вектора, не являющееся ошибкой. То есть, для нас – 1/6
5. Остается только перевернуть найденную 1/6-ю обратно с головы на ноги, что мы и делаем, деля единицу на 1/6 – 1/ 1/6=6
Автор - _Boroda_
Дата добавления - 25.11.2013 в 22:36
RAN Дата: Вторник, 26.11.2013, 09:20 | Сообщение № 10
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Цитата _Boroda_, 25.11.2013 в 14:49, в сообщении № 4
И работает в случае, если есть пустые значения.

Выплыл минус. Не работает, если последнее значение 0.

Быть или не быть, вот в чем загвоздка!
 
Ответить
Сообщение
Цитата _Boroda_, 25.11.2013 в 14:49, в сообщении № 4
И работает в случае, если есть пустые значения.

Выплыл минус. Не работает, если последнее значение 0.
Автор - RAN
Дата добавления - 26.11.2013 в 09:20
_Boroda_ Дата: Вторник, 26.11.2013, 09:25 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 16790
Репутация: 6557 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Цитата RAN, 26.11.2013 в 09:20, в сообщении № 10
Не работает, если последнее значение 0.

Читаем внимательно:
Цитата _Boroda_, 25.11.2013 в 14:49, в сообщении № 4
С учетом того, что там цены.

Цена не может быть нулем.

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата RAN, 26.11.2013 в 09:20, в сообщении № 10
Не работает, если последнее значение 0.

Читаем внимательно:
Цитата _Boroda_, 25.11.2013 в 14:49, в сообщении № 4
С учетом того, что там цены.

Цена не может быть нулем.
Автор - _Boroda_
Дата добавления - 26.11.2013 в 09:25
Матраскин Дата: Вторник, 26.11.2013, 09:56 | Сообщение № 12
Группа: Друзья
Ранг: Обитатель
Сообщений: 375
Репутация: 81 ±
Замечаний: 0% ±

20xx
вот зачем в банках при открытии счёта просят пару рублей.. "цена" не может быть 0 !!! макрос ломается...одной загадкой меньше :p

в интернете опять кто-то не прав

Сообщение отредактировал Матраскин - Вторник, 26.11.2013, 09:57
 
Ответить
Сообщениевот зачем в банках при открытии счёта просят пару рублей.. "цена" не может быть 0 !!! макрос ломается...одной загадкой меньше :p
Автор - Матраскин
Дата добавления - 26.11.2013 в 09:56
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Значение из последней заполненной ячейки (Формулы)
  • Страница 1 из 1
  • 1
Поиск:

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