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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск последней записи за месяц. - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск последней записи за месяц. (Формулы/Formulas)
Поиск последней записи за месяц.
Rioran Дата: Суббота, 07.06.2014, 14:27 | Сообщение № 1
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
Всем привет.

Думаю над задачей, как можно из списка разных дат найти последнюю запись в указанном месяце и прошу Вас, уважаемые форумчане, посодействовать.

В файле, например, если мы ищем последнюю запись за февраль 2014, формула должна вернуть номер строки 20-го февраля 2014, при этом если есть несколько 20-х чисел, нужно последнее.

Сейчас делаю пользовательскую функцию на VBA под это дело, но если есть возможность решить формулой - буду Вам благодарен. :)
К сообщению приложен файл: Date_task.xlsx (9.9 Kb)

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279
 
Ответить
СообщениеВсем привет.

Думаю над задачей, как можно из списка разных дат найти последнюю запись в указанном месяце и прошу Вас, уважаемые форумчане, посодействовать.

В файле, например, если мы ищем последнюю запись за февраль 2014, формула должна вернуть номер строки 20-го февраля 2014, при этом если есть несколько 20-х чисел, нужно последнее.

Сейчас делаю пользовательскую функцию на VBA под это дело, но если есть возможность решить формулой - буду Вам благодарен. :)
Автор - Rioran
Дата добавления - 07.06.2014 в 14:27
ikki Дата: Суббота, 07.06.2014, 14:35 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1906
Репутация: 504 ±
Замечаний: 0% ±

Excel 2003, 2010
формула массива
Код
=МАКС(ЕСЛИ((МЕСЯЦ($A$1:$A$18)=C2)*(ГОД($A$1:$A$18)=C1);СТРОКА($A$1:$A$18)))

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki
 
Ответить
Сообщениеформула массива
Код
=МАКС(ЕСЛИ((МЕСЯЦ($A$1:$A$18)=C2)*(ГОД($A$1:$A$18)=C1);СТРОКА($A$1:$A$18)))
Автор - ikki
Дата добавления - 07.06.2014 в 14:35
Rioran Дата: Суббота, 07.06.2014, 14:42 | Сообщение № 3
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
ikki, за февраль возвращает 11-е число

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279
 
Ответить
Сообщениеikki, за февраль возвращает 11-е число
Автор - Rioran
Дата добавления - 07.06.2014 в 14:42
ikki Дата: Суббота, 07.06.2014, 14:44 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1906
Репутация: 504 ±
Замечаний: 0% ±

Excel 2003, 2010
Цитата Rioran, 07.06.2014 в 14:27, в сообщении № 1
формула должна вернуть номер строки
кто писал?

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki

Сообщение отредактировал ikki - Суббота, 07.06.2014, 14:44
 
Ответить
Сообщение
Цитата Rioran, 07.06.2014 в 14:27, в сообщении № 1
формула должна вернуть номер строки
кто писал?
Автор - ikki
Дата добавления - 07.06.2014 в 14:44
Rioran Дата: Суббота, 07.06.2014, 14:46 | Сообщение № 5
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
ikki, то, что надо, спасибо! =) Даже макрос не успел дописать =)

Уточните, пожалуйста, что делает ЕСЛИ с массивом типа {0;0;1;1;1;1;1;0;0; ... }?

***

По 11-му числу - это я в шоке был =)

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279

Сообщение отредактировал Rioran - Суббота, 07.06.2014, 14:47
 
Ответить
Сообщениеikki, то, что надо, спасибо! =) Даже макрос не успел дописать =)

Уточните, пожалуйста, что делает ЕСЛИ с массивом типа {0;0;1;1;1;1;1;0;0; ... }?

***

По 11-му числу - это я в шоке был =)
Автор - Rioran
Дата добавления - 07.06.2014 в 14:46
ikki Дата: Суббота, 07.06.2014, 14:50 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1906
Репутация: 504 ±
Замечаний: 0% ±

Excel 2003, 2010
вычисляет массив и передаёт его функции МАКС.
для каждой единички (здесь она играет роль ИСТИНА) - номер строки, для каждого нолика (ЛОЖЬ) - ничего не делает, остается ЛОЖЬ

соответственно, МАКС выбирает максимальное значение из номеров строк и значений ЛОЖЬ

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki

Сообщение отредактировал ikki - Суббота, 07.06.2014, 14:51
 
Ответить
Сообщениевычисляет массив и передаёт его функции МАКС.
для каждой единички (здесь она играет роль ИСТИНА) - номер строки, для каждого нолика (ЛОЖЬ) - ничего не делает, остается ЛОЖЬ

соответственно, МАКС выбирает максимальное значение из номеров строк и значений ЛОЖЬ
Автор - ikki
Дата добавления - 07.06.2014 в 14:50
ikki Дата: Суббота, 07.06.2014, 14:56 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1906
Репутация: 504 ±
Замечаний: 0% ±

Excel 2003, 2010
вариант формулы (но тоже массивная)
Код
=МАКС(ЕСЛИ(ТЕКСТ($A$1:$A$18;"МГГГГ")=C2&C1;СТРОКА($A$1:$A$18)))

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki
 
Ответить
Сообщениевариант формулы (но тоже массивная)
Код
=МАКС(ЕСЛИ(ТЕКСТ($A$1:$A$18;"МГГГГ")=C2&C1;СТРОКА($A$1:$A$18)))
Автор - ikki
Дата добавления - 07.06.2014 в 14:56
Rioran Дата: Суббота, 07.06.2014, 15:02 | Сообщение № 8
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
ikki, интересно, щас попробую и этот =)

А подскажите, как надо вбивать долларовые гвозди, чтобы в зелёных ячейках получить, что надо? (Файл)
К сообщению приложен файл: 6186406.xlsx (10.3 Kb)

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279
 
Ответить
Сообщениеikki, интересно, щас попробую и этот =)

А подскажите, как надо вбивать долларовые гвозди, чтобы в зелёных ячейках получить, что надо? (Файл)
Автор - Rioran
Дата добавления - 07.06.2014 в 15:02
ikki Дата: Суббота, 07.06.2014, 15:04 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1906
Репутация: 504 ±
Замечаний: 0% ±

Excel 2003, 2010
для D2
Код
=МАКС(ЕСЛИ(ТЕКСТ($A$1:$A$18;"МГГГГ")=C2&$C$1;СТРОКА($A$1:$A$18)))

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki
 
Ответить
Сообщениедля D2
Код
=МАКС(ЕСЛИ(ТЕКСТ($A$1:$A$18;"МГГГГ")=C2&$C$1;СТРОКА($A$1:$A$18)))
Автор - ikki
Дата добавления - 07.06.2014 в 15:04
Rioran Дата: Суббота, 07.06.2014, 15:07 | Сообщение № 10
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
ikki, понял. Получается, месяц в этом случае, куда ссылаемся, будет динамической ссылкой и для каждой ячейки будет свой. А год шпорим намертво, чтобы для всех был одинаков.

Вариант с текстом изящнее и эстетически более приятен =)

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279
 
Ответить
Сообщениеikki, понял. Получается, месяц в этом случае, куда ссылаемся, будет динамической ссылкой и для каждой ячейки будет свой. А год шпорим намертво, чтобы для всех был одинаков.

Вариант с текстом изящнее и эстетически более приятен =)
Автор - Rioran
Дата добавления - 07.06.2014 в 15:07
Nic70y Дата: Суббота, 07.06.2014, 15:10 | Сообщение № 11
Группа: Друзья
Ранг: Экселист
Сообщений: 8984
Репутация: 2359 ±
Замечаний: 0% ±

Excel 2010
Код
=ПОИСКПОЗ(ДАТА(C$1;C2+1;1)-1;A$1:A$18;1)

не не пойдет (не внимательно прочел)

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Суббота, 07.06.2014, 15:12
 
Ответить
Сообщение
Код
=ПОИСКПОЗ(ДАТА(C$1;C2+1;1)-1;A$1:A$18;1)

не не пойдет (не внимательно прочел)
Автор - Nic70y
Дата добавления - 07.06.2014 в 15:10
ikki Дата: Суббота, 07.06.2014, 15:19 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1906
Репутация: 504 ±
Замечаний: 0% ±

Excel 2003, 2010
Цитата Rioran, 07.06.2014 в 15:07, в сообщении № 10
изящнее и эстетически более приятен
но, скорее всего, более медленный (имхо - преобразование в текст и сравнение строк по любому медленнее чем тупые проверка равенства и попарное перемножение)
впрочем, на небольших объемах незаметно

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki
 
Ответить
Сообщение
Цитата Rioran, 07.06.2014 в 15:07, в сообщении № 10
изящнее и эстетически более приятен
но, скорее всего, более медленный (имхо - преобразование в текст и сравнение строк по любому медленнее чем тупые проверка равенства и попарное перемножение)
впрочем, на небольших объемах незаметно
Автор - ikki
Дата добавления - 07.06.2014 в 15:19
krosav4ig Дата: Суббота, 07.06.2014, 23:52 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 997 ±
Замечаний: 0% ±

Excel 2007,2010,2013
еще вариант если даты будут отсортированы по возрастанию
Код
=ПОИСКПОЗ(0;$A$1:$A$18-ДАТА(C1;C2;ДЕНЬ($A$1:$A$18)))

или если в случайном порядке
Код
=МАКС(ЕСЛИ($A$1:$A$18-ДАТА(C1;C2;ДЕНЬ($A$1:$A$18));;СТРОКА($A$1:$A$18)))

или если нужно получить дату
Код
=МАКС(ЕСЛИОШИБКА(($A$1:$A$18-ДАТА(C1;C2;ДЕНЬ($A$1:$A$18)))^0;$A$1:$A$18))

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Воскресенье, 08.06.2014, 00:08
 
Ответить
Сообщениееще вариант если даты будут отсортированы по возрастанию
Код
=ПОИСКПОЗ(0;$A$1:$A$18-ДАТА(C1;C2;ДЕНЬ($A$1:$A$18)))

или если в случайном порядке
Код
=МАКС(ЕСЛИ($A$1:$A$18-ДАТА(C1;C2;ДЕНЬ($A$1:$A$18));;СТРОКА($A$1:$A$18)))

или если нужно получить дату
Код
=МАКС(ЕСЛИОШИБКА(($A$1:$A$18-ДАТА(C1;C2;ДЕНЬ($A$1:$A$18)))^0;$A$1:$A$18))
Автор - krosav4ig
Дата добавления - 07.06.2014 в 23:52
vikttur Дата: Воскресенье, 08.06.2014, 01:30 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Поиск строки в возрастающем диапазоне, не массивная:
Код
=ПОИСКПОЗ(КОНМЕСЯЦА(ДАТА(C1;C2;1);0);A1:A18)

Дата:
Код
=ПРОСМОТР(КОНМЕСЯЦА(ДАТА(C1;C2;1);0);A1:A18)


Сообщение отредактировал vikttur - Воскресенье, 08.06.2014, 01:36
 
Ответить
СообщениеПоиск строки в возрастающем диапазоне, не массивная:
Код
=ПОИСКПОЗ(КОНМЕСЯЦА(ДАТА(C1;C2;1);0);A1:A18)

Дата:
Код
=ПРОСМОТР(КОНМЕСЯЦА(ДАТА(C1;C2;1);0);A1:A18)
Автор - vikttur
Дата добавления - 08.06.2014 в 01:30
_Boroda_ Дата: Понедельник, 09.06.2014, 00:53 | Сообщение № 15
Группа: Админы
Ранг: Местный житель
Сообщений: 16691
Репутация: 6491 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
И еще для числа
Код
=ПРОСМОТР(ДАТАМЕС(C2&-C1;1)-1;A1:A18)

или, как у Виктора
Код
=ПОИСКПОЗ(КОНМЕСЯЦА(C2&-C1;0);A1:A18)

для номера строки
Код
=ПОИСКПОЗ(ДАТАМЕС(C2&-C1;1)-1;A1:A18)

если растягиваем вниз, то
Код
=ПРОСМОТР(ДАТАМЕС(C2&-C$1;1)-1;A$1:A$18)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеИ еще для числа
Код
=ПРОСМОТР(ДАТАМЕС(C2&-C1;1)-1;A1:A18)

или, как у Виктора
Код
=ПОИСКПОЗ(КОНМЕСЯЦА(C2&-C1;0);A1:A18)

для номера строки
Код
=ПОИСКПОЗ(ДАТАМЕС(C2&-C1;1)-1;A1:A18)

если растягиваем вниз, то
Код
=ПРОСМОТР(ДАТАМЕС(C2&-C$1;1)-1;A$1:A$18)
Автор - _Boroda_
Дата добавления - 09.06.2014 в 00:53
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск последней записи за месяц. (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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