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

Вход

Регистрация

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

 

= Мир MS Excel/Вывести первую строку и последнюю строку (из диапазона) - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вывести первую строку и последнюю строку (из диапазона) (Формулы/Formulas)
Вывести первую строку и последнюю строку (из диапазона)
stalber Дата: Суббота, 15.12.2018, 15:57 | Сообщение № 1
Группа: Проверенные
Ранг: Обитатель
Сообщений: 370
Репутация: 7 ±
Замечаний: 40% ±

Excel 2007
Добрый день, подскажите пожалуйста формулу.

Диапазон зелёным цветом выделен.
Первая строка оранжевым.
Последняя строка синим.
К сообщению приложен файл: zadacha_1512201.xlsx (12.0 Kb)


Сообщение отредактировал stalber - Суббота, 15.12.2018, 15:59
 
Ответить
СообщениеДобрый день, подскажите пожалуйста формулу.

Диапазон зелёным цветом выделен.
Первая строка оранжевым.
Последняя строка синим.
Автор - stalber
Дата добавления - 15.12.2018 в 15:57
Светлый Дата: Суббота, 15.12.2018, 18:45 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Добрый день!
Массивные формулы:
Код
=ИНДЕКС(C:C;МИН(ЕСЛИ((C1:C60<>0)*(СТРОКА(1:60)<=МАКС(($A1:$Q60<>0)*СТРОКА(1:60)))*(СТРОКА(1:60)>МАКС(($A1:$Q60<>0)*СТРОКА(1:60))-$T24);СТРОКА(1:60))))
Код
=ИНДЕКС(C:C;МАКС(ЕСЛИ((C1:C60<>0)*(СТРОКА(1:60)<=МАКС(($A1:$Q60<>0)*СТРОКА(1:60)))*(СТРОКА(1:60)>МАКС(($A1:$Q60<>0)*СТРОКА(1:60))-$T24);СТРОКА(1:60))))

Программировать проще, чем писать стихи.
 
Ответить
СообщениеДобрый день!
Массивные формулы:
Код
=ИНДЕКС(C:C;МИН(ЕСЛИ((C1:C60<>0)*(СТРОКА(1:60)<=МАКС(($A1:$Q60<>0)*СТРОКА(1:60)))*(СТРОКА(1:60)>МАКС(($A1:$Q60<>0)*СТРОКА(1:60))-$T24);СТРОКА(1:60))))
Код
=ИНДЕКС(C:C;МАКС(ЕСЛИ((C1:C60<>0)*(СТРОКА(1:60)<=МАКС(($A1:$Q60<>0)*СТРОКА(1:60)))*(СТРОКА(1:60)>МАКС(($A1:$Q60<>0)*СТРОКА(1:60))-$T24);СТРОКА(1:60))))
Автор - Светлый
Дата добавления - 15.12.2018 в 18:45
gling Дата: Суббота, 15.12.2018, 19:07 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2519
Репутация: 674 ±
Замечаний: 0% ±

2010
Здравствуйте. С формулами в диспетчере имен.
К сообщению приложен файл: 0492357.xlsx (13.6 Kb)

ЯД-41001506838083

Сообщение отредактировал gling - Суббота, 15.12.2018, 19:57
 
Ответить
СообщениеЗдравствуйте. С формулами в диспетчере имен.
Автор - gling
Дата добавления - 15.12.2018 в 19:07
gling Дата: Суббота, 15.12.2018, 19:55 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2519
Репутация: 674 ±
Замечаний: 0% ±

2010
Немного перемудрил с формулами в диспетчере имен. Не нужно искать остаток, достаточно записать так
Код
ПОИСКПОЗ(2;1/(Диап<>0);1)
Файл в предыдущем сообщении заменил.

ЯД-41001506838083

Сообщение отредактировал gling - Суббота, 15.12.2018, 19:58
 
Ответить
СообщениеНемного перемудрил с формулами в диспетчере имен. Не нужно искать остаток, достаточно записать так
Код
ПОИСКПОЗ(2;1/(Диап<>0);1)
Файл в предыдущем сообщении заменил.
Автор - gling
Дата добавления - 15.12.2018 в 19:55
stalber Дата: Суббота, 15.12.2018, 22:16 | Сообщение № 5
Группа: Проверенные
Ранг: Обитатель
Сообщений: 370
Репутация: 7 ±
Замечаний: 40% ±

Excel 2007
Светлый, благодарю за помощь, но из-за того что мне нужно 160 таких формул, эксель очень тормозит.

Qlinq, благодарю за помощь, запутался, можно получить формулы не используя диспетчер имён? В примере я вывел числа подряд, а мне порой не всегда подряд нужны, а там через столбец или через два столбца, а формула там =первая нет привязки к конкретному столбцу.
 
Ответить
СообщениеСветлый, благодарю за помощь, но из-за того что мне нужно 160 таких формул, эксель очень тормозит.

Qlinq, благодарю за помощь, запутался, можно получить формулы не используя диспетчер имён? В примере я вывел числа подряд, а мне порой не всегда подряд нужны, а там через столбец или через два столбца, а формула там =первая нет привязки к конкретному столбцу.
Автор - stalber
Дата добавления - 15.12.2018 в 22:16
Светлый Дата: Суббота, 15.12.2018, 22:31 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Немного причесал формулу:
Код
=ИНДЕКС(C:C;МИН(ЕСЛИ((C1:C60<>0)*(ABS(МАКС(($A1:$Q60<>0)*СТРОКА(1:60))-($T24-1)/2-СТРОКА(1:60))<$T24/2);СТРОКА(1:60))))

Программировать проще, чем писать стихи.
 
Ответить
СообщениеНемного причесал формулу:
Код
=ИНДЕКС(C:C;МИН(ЕСЛИ((C1:C60<>0)*(ABS(МАКС(($A1:$Q60<>0)*СТРОКА(1:60))-($T24-1)/2-СТРОКА(1:60))<$T24/2);СТРОКА(1:60))))
Автор - Светлый
Дата добавления - 15.12.2018 в 22:31
gling Дата: Суббота, 15.12.2018, 22:40 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2519
Репутация: 674 ±
Замечаний: 0% ±

2010
Цитата stalber, 15.12.2018 в 22:16, в сообщении № 5 ()
не используя диспетчер имён
Можно, но это тоже формулы массива, и тормозить будут так же.
Код
=ЕСЛИОШИБКА(ИНДЕКС(ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1)-19):ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1));ПОИСКПОЗ(1;1/(ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1)-19):ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1))<>0);));)
Код
=ЕСЛИОШИБКА(ИНДЕКС(ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1)-19):ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1));ПОИСКПОЗ(2;1/(ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1)-19):ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1))<>0);1));)
К сообщению приложен файл: 4417739.xlsx (14.7 Kb)

ЯД-41001506838083
 
Ответить
Сообщение
Цитата stalber, 15.12.2018 в 22:16, в сообщении № 5 ()
не используя диспетчер имён
Можно, но это тоже формулы массива, и тормозить будут так же.
Код
=ЕСЛИОШИБКА(ИНДЕКС(ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1)-19):ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1));ПОИСКПОЗ(1;1/(ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1)-19):ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1))<>0);));)
Код
=ЕСЛИОШИБКА(ИНДЕКС(ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1)-19):ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1));ПОИСКПОЗ(2;1/(ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1)-19):ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1))<>0);1));)
Автор - gling
Дата добавления - 15.12.2018 в 22:40
Светлый Дата: Суббота, 15.12.2018, 22:55 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
А так ещё быстрее будет работать:
Код
=ИНДЕКС(C:C;МИН(ЕСЛИ((C1:C60<>0)*(ABS(ПОИСКПОЗ(9^9;C:C;1)-($T24-1)/2-СТРОКА(1:60))<$T24/2);СТРОКА(1:60))))
Или даже так:
Код
=ИНДЕКС(C:C;МИН(ЕСЛИ((C1:C60<>0)*(ABS(ПОИСКПОЗ(9^9;C1:C60;1)-($T24-1)/2-СТРОКА(1:60))<$T24/2);СТРОКА(1:60))))
*
Код
=ИНДЕКС(C:C;МИН(ЕСЛИ((C1:C60<>0)*(ABS((ПОИСКПОЗ(9^9;C:C;1)-СТРОКА(1:60))*2-$T24+1)<$T24);СТРОКА(1:60))))

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Суббота, 15.12.2018, 23:22
 
Ответить
СообщениеА так ещё быстрее будет работать:
Код
=ИНДЕКС(C:C;МИН(ЕСЛИ((C1:C60<>0)*(ABS(ПОИСКПОЗ(9^9;C:C;1)-($T24-1)/2-СТРОКА(1:60))<$T24/2);СТРОКА(1:60))))
Или даже так:
Код
=ИНДЕКС(C:C;МИН(ЕСЛИ((C1:C60<>0)*(ABS(ПОИСКПОЗ(9^9;C1:C60;1)-($T24-1)/2-СТРОКА(1:60))<$T24/2);СТРОКА(1:60))))
*
Код
=ИНДЕКС(C:C;МИН(ЕСЛИ((C1:C60<>0)*(ABS((ПОИСКПОЗ(9^9;C:C;1)-СТРОКА(1:60))*2-$T24+1)<$T24);СТРОКА(1:60))))
Автор - Светлый
Дата добавления - 15.12.2018 в 22:55
Светлый Дата: Воскресенье, 16.12.2018, 16:00 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Наверно, можно ещё сократить, но этого достаточно:
Код
=СМЕЩ(C1;МИН(ЕСЛИ((C1:C60<>0)*(ABS((ПОИСКПОЗ(9^9;C:C;1)-СТРОКА(1:60))*2-$T24+1)<$T24);СТРОКА(1:60)-1));)
Код
=СМЕЩ(C1;МАКС(((C1:C60<>0)*(ABS((ПОИСКПОЗ(9^9;C:C;1)-СТРОКА(1:60))*2-$T24+1)<$T24)*СТРОКА(1:60))-1);)

Программировать проще, чем писать стихи.
 
Ответить
СообщениеНаверно, можно ещё сократить, но этого достаточно:
Код
=СМЕЩ(C1;МИН(ЕСЛИ((C1:C60<>0)*(ABS((ПОИСКПОЗ(9^9;C:C;1)-СТРОКА(1:60))*2-$T24+1)<$T24);СТРОКА(1:60)-1));)
Код
=СМЕЩ(C1;МАКС(((C1:C60<>0)*(ABS((ПОИСКПОЗ(9^9;C:C;1)-СТРОКА(1:60))*2-$T24+1)<$T24)*СТРОКА(1:60))-1);)
Автор - Светлый
Дата добавления - 16.12.2018 в 16:00
_Boroda_ Дата: Понедельник, 17.12.2018, 09:52 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Если со СМЕЩ, то такие варианты
Массивная
Код
=ИНДЕКС(C:C;МИН(ЕСЛИ(СМЕЩ(C2;СЧЁТ(C:C)-20;;20);СТРОКА(СМЕЩ(C2;СЧЁТ(C:C)-20;;20)))))

Немассивная
Код
=ЕСЛИОШИБКА(-1/ПРОСМОТР(;-1/СМЕЩ(C2;СЧЁТ(C:C)-20;;20));)


Без СМЕЩ
Код
=ИНДЕКС(C:C;МИН(ЕСЛИ(C99:ИНДЕКС(C:C;СЧЁТ(C:C)-18);СТРОКА(C99:ИНДЕКС(C:C;СЧЁТ(C:C)-18)))))

Код
=ЕСЛИОШИБКА(-1/ПРОСМОТР(;-1/C99:ИНДЕКС(C:C;СЧЁТ(C:C)-18));)
К сообщению приложен файл: 3509967.xlsx (17.6 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕсли со СМЕЩ, то такие варианты
Массивная
Код
=ИНДЕКС(C:C;МИН(ЕСЛИ(СМЕЩ(C2;СЧЁТ(C:C)-20;;20);СТРОКА(СМЕЩ(C2;СЧЁТ(C:C)-20;;20)))))

Немассивная
Код
=ЕСЛИОШИБКА(-1/ПРОСМОТР(;-1/СМЕЩ(C2;СЧЁТ(C:C)-20;;20));)


Без СМЕЩ
Код
=ИНДЕКС(C:C;МИН(ЕСЛИ(C99:ИНДЕКС(C:C;СЧЁТ(C:C)-18);СТРОКА(C99:ИНДЕКС(C:C;СЧЁТ(C:C)-18)))))

Код
=ЕСЛИОШИБКА(-1/ПРОСМОТР(;-1/C99:ИНДЕКС(C:C;СЧЁТ(C:C)-18));)
Автор - _Boroda_
Дата добавления - 17.12.2018 в 09:52
stalber Дата: Суббота, 02.03.2019, 18:35 | Сообщение № 11
Группа: Проверенные
Ранг: Обитатель
Сообщений: 370
Репутация: 7 ±
Замечаний: 40% ±

Excel 2007
Подскажите пожалуйста, вот ниже формулы они выводят последнее число из диапазона, а теперь понадобилось предпоследнее.

gling [vba]
Код
=ЕСЛИОШИБКА(ИНДЕКС(ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1)-19):ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1));ПОИСКПОЗ(2;1/(ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1)-19):ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1))<>0);1));)
[/vba]

Светлый [vba]
Код
=СМЕЩ(C1;МАКС(((C1:C60<>0)*(ABS((ПОИСКПОЗ(9^9;C:C;1)-СТРОКА(1:60))*2-$T24+1)<$T24)*СТРОКА(1:60))-1);)
[/vba]

_Boroda_ [vba]
Код
=ЕСЛИОШИБКА(-1/ПРОСМОТР(;-1/C99:ИНДЕКС(C:C;СЧЁТ(C:C)-18));)
[/vba]
К сообщению приложен файл: vopros02032019.xlsx (13.2 Kb)
 
Ответить
СообщениеПодскажите пожалуйста, вот ниже формулы они выводят последнее число из диапазона, а теперь понадобилось предпоследнее.

gling [vba]
Код
=ЕСЛИОШИБКА(ИНДЕКС(ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1)-19):ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1));ПОИСКПОЗ(2;1/(ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1)-19):ИНДЕКС(C:C;ПОИСКПОЗ(9^9;C:C;1))<>0);1));)
[/vba]

Светлый [vba]
Код
=СМЕЩ(C1;МАКС(((C1:C60<>0)*(ABS((ПОИСКПОЗ(9^9;C:C;1)-СТРОКА(1:60))*2-$T24+1)<$T24)*СТРОКА(1:60))-1);)
[/vba]

_Boroda_ [vba]
Код
=ЕСЛИОШИБКА(-1/ПРОСМОТР(;-1/C99:ИНДЕКС(C:C;СЧЁТ(C:C)-18));)
[/vba]
Автор - stalber
Дата добавления - 02.03.2019 в 18:35
stalber Дата: Понедельник, 04.03.2019, 03:34 | Сообщение № 12
Группа: Проверенные
Ранг: Обитатель
Сообщений: 370
Репутация: 7 ±
Замечаний: 40% ±

Excel 2007
Подскажите пожалуйста, тема актуальна (последнее сообщение).
 
Ответить
СообщениеПодскажите пожалуйста, тема актуальна (последнее сообщение).
Автор - stalber
Дата добавления - 04.03.2019 в 03:34
stalber Дата: Вторник, 05.03.2019, 13:35 | Сообщение № 13
Группа: Проверенные
Ранг: Обитатель
Сообщений: 370
Репутация: 7 ±
Замечаний: 40% ±

Excel 2007
Спасибо тема решена http://www.excelworld.ru/forum/2-41013-1
 
Ответить
СообщениеСпасибо тема решена http://www.excelworld.ru/forum/2-41013-1
Автор - stalber
Дата добавления - 05.03.2019 в 13:35
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вывести первую строку и последнюю строку (из диапазона) (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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