Например, в столбце А у нас содержатся значения, на основе которых нужно составить список выбора для других полей.
Какую формулу нужно вписать в "Данные - Проверка - Источник", чтобы в выпадающем списке присутствовали только непустые значения (или только уникальные значения - в этом случае в списке будет присутствовать только одно пустое значение, которое мешать особо не будет).
Интуитивно чувствую, что задача несложно решается с LOOKUP/VLOOKUP. Не знаю, как в Excel, но в OO Calc в справке примеров нет...
2003/XP/2000 - [решено] Excel: формула для получения списка выбора с непустыми значениями
-
a_axe
Re: 2003/XP/2000 - [решено] Excel: формула для получения списка выбора с непустыми значениями
Цитата CyraxZ:
Какую формулу нужно вписать в "Данные - Проверка - Источник", чтобы в выпадающем списке присутствовали только непустые значения
CyraxZ,
1. если данные в столбце А идут подряд без пустых ячеек, в "Данные - Проверка - Источник" вбейте формулу =СМЕЩ(A2;0;0;СЧЁТЗ(A2:A100);1) (проверяемый диапазон соответственно будет A2:A100, исходя из предположения, что в А1 у вас заголовок таблицы).
2. если в столбце А среди заполненных ячеек встречаются пустые - введите дополнительный столбец (для определенности пусть будет "B", либо любой удобный для вас).
В ячейку B2 вбейте =ЕСЛИОШИБКА(ДВССЫЛ("A"&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($A$2:$A$100);"";СТРОКА($A$2:$A$100));СТРОКА(A1)));"") и нажмите ctrl+shift+enter, чтобы формула ввелась как формула массива (формула должна выделиться фигурными скобочками), затем протащите на 100 ячеек вниз за крестик в правом нижнем углу ячейки (или сколько нужно - в формулах сейчас заложено 100). В столбце В отобразятся подряд значения непустых ячеек столбца А из диапазона строк 2-100. Соответственно в проверку данных вбейте формулу по п.1, но как аргумент используйте ячейки столбца В.
Какую формулу нужно вписать в "Данные - Проверка - Источник", чтобы в выпадающем списке присутствовали только непустые значения
2003/XP/2000 - [решено] Excel: формула для получения списка выбора с непустыми значениями
CyraxZ,
1. если данные в столбце А идут подряд без пустых ячеек, в "Данные - Проверка - Источник" вбейте формулу =СМЕЩ(A2;0;0;СЧЁТЗ(A2:A100);1) (проверяемый диапазон соответственно будет A2:A100, исходя из предположения, что в А1 у вас заголовок таблицы).
2. если в столбце А среди заполненных ячеек встречаются пустые - введите дополнительный столбец (для определенности пусть будет "B", либо любой удобный для вас).
В ячейку B2 вбейте =ЕСЛИОШИБКА(ДВССЫЛ("A"&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($A$2:$A$100);"";СТРОКА($A$2:$A$100));СТРОКА(A1)));"") и нажмите ctrl+shift+enter, чтобы формула ввелась как формула массива (формула должна выделиться фигурными скобочками), затем протащите на 100 ячеек вниз за крестик в правом нижнем углу ячейки (или сколько нужно - в формулах сейчас заложено 100). В столбце В отобразятся подряд значения непустых ячеек столбца А из диапазона строк 2-100. Соответственно в проверку данных вбейте формулу по п.1, но как аргумент используйте ячейки столбца В.
-
CyraxZ
Re: 2003/XP/2000 - [решено] Excel: формула для получения списка выбора с непустыми значениями
Цитата:
1. если данные в столбце А идут подряд без пустых ячеек, в "Данные - Проверка - Источник" вбейте формулу =СМЕЩ(A2;0;0;СЧЁТЗ(A2:A100);1) (проверяемый диапазон соответственно будет A2:A100, исходя из предположения, что в А1 у вас заголовок таблицы).
Да, идут подряд, без пустых ячеек.
В Open Office Calc эта формула будет выглядеть так (ссылки поставил абсолютные + данные начинаются с ячейки А1 вниз):
Код:
1. если данные в столбце А идут подряд без пустых ячеек, в "Данные - Проверка - Источник" вбейте формулу =СМЕЩ(A2;0;0;СЧЁТЗ(A2:A100);1) (проверяемый диапазон соответственно будет A2:A100, исходя из предположения, что в А1 у вас заголовок таблицы).
Да, идут подряд, без пустых ячеек.
В Open Office Calc эта формула будет выглядеть так (ссылки поставил абсолютные + данные начинаются с ячейки А1 вниз):
Код:
Код: Выделить всё
OFFSET($A$1;0;0; COUNTA($A$1:$A$100); 1)