Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Узнать номер строки n-ной пустой ячейки в столбце A с помощью встроенных функций EXCEL? (не макрос)
 
#1
10.05.2012 20:24:29
Здравствуйте, можно ли осуществить такое?  
 
Есть лист, в нём:  
 
Столбец А Столбец В  
Строка 1  
Строка 2 4141  
Строка 3 Парта  
Строка 4  
Строка 5 9977  
Строка 6  
Строка 7 Сигареты = ‘Здесь нужна формула, которая должна дать номер строки 3-й пустой ячейки в столбце А  
(6)  
 
 
Какую формулу надо написать в ячейку B7, чтобы получить номер строки 3-й пустой ячейки в столбце А ?  
 
При правильном решении формула должна дать результат 6   (номер строки ячейки A6)  
 
P.S. Можно ли написать такую формулу, не пользуясь макросами и UDF?
 
 
 
#2
10.05.2012 20:33:53
разумеется данные приведены условно, в реальности таблица гораздо больше и пустые ячейки могут встречаться в любом месте столбца А
 
 
 
#3
10.05.2012 22:34:31
Так?
 
 
 
#4
11.05.2012 06:52:45
=ПОИСКПОЗ(3;СЧЁТЕСЛИ(СМЕЩ($A$1;;;СТРОКА($A$1:$A$10));"");0)  
=НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(A1:A10;A1:A10)=0;СТРОКА(A1:A10));3)  
 
- обе формулы массивные.
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#5
11.05.2012 09:51:26
спасибо, сейчас посмотрю  
 
ShAM - ока адашмасам Тошкендансиз-а? ўзбеча гаплашасми?
 
 
 
#6
11.05.2012 12:28:10
{quote}{login= Б А Х Т И Ё Р}{date=11.05.2012 09:51}{thema=ShAM,  Владимир}{post}ShAM - ока адашмасам Тошкендансиз-а? ўзбеча гаплашасми?{/post}{/quote}  
Да, из Ташкента. Можно и на узбекском, но не здесь же. Думаю, это будет неуважением к большинству других участников форума, которые не знают язык.  
Если что пишите на мыло: shakirov@ubi.uz
 
 
 
#7
11.05.2012 17:57:07
ShAM - спасибо, я видел ваш e-mail на карте планеты, сёдня зашёл на сайт ubi.uz жаль там всё на английском :-( 'но вроде удалось понять вашу должность :-)  
 
если что буду писать, спасибо
 
 
 
#8
11.05.2012 17:58:55
спасибо формулы работают
 
 
 
#9
11.05.2012 22:57:20
Ещё такая формула вроде подходит.  
{=НАИМЕНЬШИЙ(ЕСЛИ(A:A="";СТРОКА(A:A);"");3)}  
Она рассчитана на весь столбец А, поэтому в таком виде её нужно вводить в ячейку любого другого столбца (во избежание циклической ссылки) ну или сократить диапазон до нужного.
 
 
 
#10
14.05.2012 09:33:49
спасибо, работает.  
 
но не могу понять как работает формула.  
в примере пустыми являются строки 1, 4 и 6  
как я понимаю, при расчете формулы сформировывается массив состоящий из чисел 1, 4 и 6  
но ведь 3-наименьший среди этих чисел - 1   ???  
 
а возвращает 6  
 
объясните плиз или дайте ссылку
 
 
 
#11
14.05.2012 09:43:26
Б А Х Т И Ё Р, формулу ShAM поняли, а с предложениием Gekan проблемы? :)  
 
Gekan, найдите существенные отличия между формулой ShAM и предложенной Вами.  
 
Ссылки на полный столбец лучше не применять, зачем лишние вычисления? Да и Excel-2003 такого не любит.  
В ЕСЛИ() можно опустить проверку на несоответствие условию:  
=НАИМЕНЬШИЙ(ЕСЛИ(A1:A20="";СТРОКА(A1:A20));3)  
 
Если в ячейке пусто, то в массив записывается номер строки:  
{1:ЛОЖЬ:ЛОЖЬ:4...}  
НАИМЕНЬШИЙ() выбирает третье значение по возрастанию. Логические значения (ЛОЖЬ), текстовые ("") не учитываются.  
 
Как вводить формулу массива:  
После введения формулы в ячейку, ввод завершается нажатием не просто Enter, а Ctrl+Shift+Enter. Формула при этом будет заключена в фигурные скобки - {}.
 
 
 
#12
14.05.2012 10:46:21
"... спасибо, работает ... но не могу понять как работает формула ... " - когда я писал это, то я имел ввиду формулы с функцией наименьший() и ShaMa, и Владимира, и Gekanа.  
 
vikttur - спасибо, после вашего ответа всё стало ясно 'вроде не было ничего сложного, но пока вы не объяснили детально... :-)  
 
 
P.S. Если у кого-то будут ещё варианты с другими функциями, которые здесь ещё не были использованы буду рад
 
 
 
#13
14.05.2012 10:52:12
{quote}{login= Б А Х Т И Ё Р}{date=14.05.2012 09:33}{thema= }{post}...массив состоящий из чисел 1, 4 и 6  
...ведь 3-наименьший среди этих чисел - 1   ???  
{/post}{/quote}  
 
ВЕДЬ число 1 - это ПЕРВОЕ НАИМЕНЬШЕЕ число среди чисел 1, 4 и 6  
 
теперь всё ясно
 
 
 
Страницы: 1
Читают тему
Наверх