Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1 2 След.
RSS
Проблема с вариантом создания нумерованного списка из Приемов
 
#1
13.10.2011 21:01:01
Есть таблица, в левом столбце которой для нумерации строк используется формула, предложенная в Приемах, вида:  
               
                =ЕСЛИ(ЕПУСТО(C4);"";СЧЁТЗ($C$4:C4))  
 
При вводе любого значения в ячейку С19 (первую под таблицей) таблица увеличивается и включает строку 19 в свой состав. При этом возникает сбой в формуле нумерации, расположенной в ячейке В18. (смотри пример)  
Есть ли способ это исправить, если есть - то как или, возможно, уже существует вообще другой способ автоматической нумерации строк.
 
 
 
#2
13.10.2011 21:03:06
На всякий случай добавляю файл примера в версии 97-2003
 
 
 
#3
13.10.2011 21:18:50
И что у Вас не работает? Сейчас проверил все работает, никакой ошибки не возникает.
 
 
 
#4
13.10.2011 21:26:50
странно...  
вот файл с зафиксированной ошибкой (смотри формулу в ячейке с красной заливкой)
 
 
 
#5
13.10.2011 22:06:07
у вас в настройках стоит - автоматическое расширение списков. Надо запретить авторасширение и тогда все коррекно
 
 
 
#6
13.10.2011 22:59:45
где можно это сделать?
 
 
 
#7
13.10.2011 23:08:57
использую Excel 2010
 
 
 
#8
13.10.2011 23:25:28
Если речь об отключении функции "Включать в таблицу новые строки и столбцы" (параметры - правописание - параметры автозамены - автоформат при вводе), то это не очень гуд, т.к. не хочется терять возможность автовключения нижней строки в таблицу при вводе в эту строку значения.
 
 
 
#9
14.10.2011 00:04:36
Попробуйте вот такого монстра:  
=ЕСЛИ(ЕПУСТО($C4);"";СЧЁТЗ(ДВССЫЛ(АДРЕС(СТРОКА($C$4);СТОЛБЕЦ()+1)&":"&АДРЕС(СТРОКА();СТОЛБЕЦ()+1))))
 
 
 
#10
14.10.2011 00:10:24
ок, сейчас!!!
 
 
 
#11
14.10.2011 00:14:18
Все отлично работает, но мозги уже не способны понять как это у Вас получилось. Огромное спасибо ,завтра постараюсь переварить этого "монстра"!!!  
Хотелось бы еще вопрос задать, правда он не совсем по этой теме.
 
 
 
#12
14.10.2011 00:31:32
Прикрепляю пример, в котором Ваша формула уже работает в полную силу.  
В столбце3 должны вводиться уникальные (неповторяющиеся) значения. Для этого использую в проверке данных формулу вида: =СЧЁТЕСЛИ(D$4:D$19;D4)=1  
Она нормально работает, но:  
в реале количество строк в данной таблице будет значением переменным (может их будет больше, может меньше), т.е. в какой-то момент запаса диапазона D$4:D$19 может не хватить.    
Поэтому пробовал диапазон D$4:D$19 в данной формуле представить как Таблица1[Столбец3], то есть так =СЧЁТЕСЛИ(Таблица1[Столбец3];D4)=1.
Если эту формулу вставляю в любую ячейку листа, то она работает нормуль (возвращает значение истина или ложь - смотри ячейку I3), а если я ее копирую в строку формул проверки данных, то Ексель выдает ошибку.  
В принципе это можно исправить или нет?
 
 
 
#13
14.10.2011 00:42:51
то есть требуется, чтобы в данный момент времени в каждой ячейке Столбца3 формула проверки данных использовала динамический диапазон Столбца3 (а не ограничивалась только диапазоном D$4:D$19)
 
 
 
#14
14.10.2011 00:44:55
Извиняюсь, что не авторизовался.
 
 
 
#15
14.10.2011 02:02:04
SerjVorotilov, посмотрите Лист1(2)
 
 
 
#16
14.10.2011 09:14:06
Наверное, что-то не так объяснил.  
В самих ячейках Столбца3 формул провеки данных быть не должно - там вручную будут вводиться целые числа от 1 до n с шагом 1. Нужно, что бы программа с помощью чего-либо не позволяла ввод в любую ячейку столбца3 значения, которое уже присутствует в этом столбце.  
При этом высота столбца может как уменьшаться, так и увеличиваться (за счет добавления или удаления новых строк в таблицу), поэтому не хочется использовать формулу содержащую ссылку на ограниченный диапазон, даже такую D$4:D$1048576.  
Вот и вопрос - можно ли это сделать в принципе или нет?
 
 
 
#17
14.10.2011 09:25:19
Важное условие!  
Чтобы при авторасширении таблицы (автовключении первой строки, находящейся под таблицей, в состав таблицы) условие проверки данных во вновь включенной строке продолжало выполняться, а во всех предыдущих строках бы диапазон проверки увеличивался на вновь добавленную строку.
 
 
 
#18
14.10.2011 11:25:12
Зачем Вам Список (Таблица?)  
Протяните формулу на максимально возможное количество строк и не мучайтесь.  
для фильтрации можно использовать Автофильтр.  
 
Если все же любите Списки:  
=ЕСЛИ(C4="";"";ИНДЕКС(СТРОКА($1:$100);СТРОКА(1:1)))
 
 
 
#19
14.10.2011 12:22:10
Список (а точнее каждый конкретный номер списка: 1, 2, 3, ... и т.д.) в моем случае является идентификатором - неповторимым значением, по которому с помощью функции ВПР я извлекаю из следующих столбцов конктретной строки нужную информацию (фамилию и др.). То есть с помощью списка я получаю адрес нужной строки.    
Тут мне больше подходит формула С.М.:  
 
=ЕСЛИ(ЕПУСТО($C4);"";СЧЁТЗ(ДВССЫЛ(АДРЕС(СТРОКА($C$4);СТОЛБЕЦ()+1)&":"&АДРЕС(СТРОКА();СТОЛБЕЦ()+1))))  
 
т.к.:  
1. При вводе очередного значения (фамилии) в Столбец2 Таблица автоматически растягивается и включает строку с вводимой фамилией в свой состав. Нумерация при этом продолжается (при использовании формулы =ЕСЛИ(ЕПУСТО(C4);"";СЧЁТЗ($C$4:C4)) в предпоследней строке формула менялась).  
2. При удалении строки из таблицы нумерация не сбивается, в отличие от формулы =ЕСЛИ(C4="";"";ИНДЕКС(СТРОКА($1:$100);СТРОКА(1:1))), что тоже важно, т.к. данные могут как добавляться в таблицу, так и удаляться из нее.  
 
Но за предложенный вариант - большое спасибо. Как говорится, все познается в сравнении.  
 
Остается открыты ммой вопрос по проверке данных...
 
 
 
#20
14.10.2011 12:50:22
Под словом "Список" я подразумевал встроенный инструмент Excel, который вы используете (меню Данные-Список-Создать в Excel-2003). В старших версиях это, если не ошибаюсь, "Таблица".  
 
Моя формула составлена без применения летучих функций.  
 
:)  
Понял, почему "лету-чих"! пересчитываются при каждом чихе :)  
 
По второму вопросу. Существует инструмент Проверка_данных. Меню Данные-Проверка-Параметры-Тип_данных-Другой, формула:  
=--ЕЧИСЛО(ПОИСКПОЗ(C2;$C$1:C1;))<>1
 
 
 
#21
14.10.2011 13:03:47
спасибо большое, если чем-то обидел Вас, то примите извинения, как говорится - и в мыслях не было.  
Сейчас на работе, все проверить времени нет, как что - сразу напишу.  
Еще раз огромное спасибо за помощь.
 
 
 
#22
14.10.2011 13:14:57
В столбце1 таблицы (В:В листа) формулу  
=ЕСЛИ(ЕПУСТО(C4);"";МАКС($B$3:B3)+1)
 
 
 
#23
14.10.2011 13:18:50
{quote}{login=}{date=14.10.2011 01:03}{thema=}{post}с...если чем-то обидел Вас{/post}{/quote}  
Где вы такое высмотрели? :)  
 
Михаил, там проблема со Списком, он неправильно вставляет формулу при добавлении строки.
 
 
 
#24
14.10.2011 13:38:26
vikttur, значит показалось)))  
при использовании Вашей формулы формула при добавлении строки работает нормально, а вот приудалении любой строки таблицы кроме последней - нумерация сбивается, чего нет при использовании формулы, предложенной С.М.:  
=ЕСЛИ(ЕПУСТО($C4);"";СЧЁТЗ(ДВССЫЛ(АДРЕС(СТРОКА($C$4);СТОЛБЕЦ()+1)&":"&АДРЕС(СТРОКА();СТОЛБЕЦ()+1))))  
 
Формулу Михаила С. еще не успел проверить.  
Подчеркиваю, что строки могут как добавляться в таблицу, так и удаляться из нее, поэтому важно, чтобы нумерация при этом не сбивалась и всегда была от 1 до n, step=1.
 
 
 
#25
14.10.2011 13:51:21
Об удалении не видел. Убираем ошибку:  
=ЕСЛИ(C4="";"";ИНДЕКС(СТРОКА($1:$98);СТРОКА()-3))  
 
Проанализируйте исходную и измененную формулы и вы поймете, из-за чего возникала ошибка
 
 
 
#26
14.10.2011 13:55:12
Даже и не думал, что существует такое количество решений.  
Формула, предложенная Михаилом С. оказалась наилучшей, т.к. даже при удалении первой строки она продолжает корректно работать, в отличии от формулы С.М. - при удалении именно 1 строки происходит ее сбой во всем столбце.
 
 
 
#27
14.10.2011 14:01:30
Строка 19:  
=ЕСЛИ(ЕПУСТО(C19);"";МАКС($B$3:B18)+1)  
заполняю С20 - циклическая ссылка в этой строке:  
=ЕСЛИ(ЕПУСТО(C20);"";МАКС($B$3:B20)+1)    
 
У Вас не так?
 
 
 
#28
14.10.2011 14:31:02
при заполнении С20 у меня в В20 так:  
=ЕСЛИ(ЕПУСТО(C20);"";МАКС($B$3:B19)+1),  
то есть диапазон функции МАКС на 1 меньше, чем номер заполняемой строки (в нашем случае - строка С20, а диапазон - $B$3:B19  
 
Эту Вашу формулу, vikttur, =ЕСЛИ(C4="";"";ИНДЕКС(СТРОКА($1:$98);СТРОКА()-3)), еще не успел по-тестить
 
 
 
#29
14.10.2011 17:41:37
{quote}{login=SerjVorotilov}{date=14.10.2011 01:55}{thema=}{post}Даже и не думал, что существует такое количество решений.  
Формула, предложенная Михаилом С. оказалась наилучшей, т.к. даже при удалении первой строки она продолжает корректно работать, в отличии от формулы С.М. - при удалении именно 1 строки происходит ее сбой во всем столбце.{/post}{/quote}  
SerjVorotilov, посмотрите Лист1(3) и Лист1(4)
 
 
 
#30
14.10.2011 18:13:04
С.М., спасибо за пример, очень удобно, что он с пояснениями, без них бы, наверное, вообще не разобрался.  
И все же остановил свой выбор на формуле Михаила С. - более понятно, более короче, без привязки к размеру таблицы (т.е. не надо прописывать конец диапазона в виде $C$3:$C$11), что особенно важно, т.к. размер в моем случае будет постоянно меняться.  
 
Для проверки ввода неповторяющихся целых значений буду использовать формулу =СЧЁТЕСЛИ(AB:AB;AB4)=1  
Жаль, что в Проверка_данных. Меню Данные-Проверка-Параметры-Тип_данных-Другой, формула нельзя использовать название столбца для обозначения диапазона (или динамический диапазон), поэтому приходится в формулу проверки включать весь столбец (AB:AB).  
Также неудобно, что в заголовок столбца таблицы нельзы вставить формулу, а только тупо написать его на клавиатуре(((  
 
В любом случае, еще раз всем отозвавшимся на мой призыв о помощи огромное человеческое спасибо - в одиночку бы не справился.
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх