Microsoft Excel:

  Таблицы и VBA. Справочник.
  Вопросы и Ответы. Советы. Примеры.
Меню FAQ | Ввод данных


Rambler's Top100


Counter CO.KZ

    [1] [1/2] [2] [3]

    Проверка данных
  1. Как проверить и при необходимости запретить ввод данных в ячейку ? 2004
  2. Как при проверке данных воспользоваться массивом констант ? 16.06.2007
  3. Как запретить ввод чисел в ячейки ? 14.04.2007
  4. Как запретить ввод чисел, дат, логических значений, а также текста, содержащего лишние пробелы (более одного пробела) ? 17.04.2007
  5. Как запретить только ввод текста, содержащего лишние пробелы (более одного пробела) ? 17.04.2007
  6. Как запретить ввод дат, не попадающих в заданный интервал ? 15.03.2012
  7. Как запретить ввод текста, содержащего определённые символы ? 06.05.2007
  8. Как запретить ввод текста, не соответствующего определённому шаблону ? 17.04.2007
  9. Как запретить ввод текста, который не может быть именем листа или книги ? 16.06.2007
  10. Как найти ячейки, содержащие данные не соответствующие условиям проверки вводимых данных ? 06.04.2007
  11. Как выделить ячейки содержащие проверку вводимых данных ? 06.04.2007
  12. Как создать небольшой выпадающий список, без использования ячеек в качестве источника данных ? 05.05.2007
  13. Kак создать выпадающий список, содержащий список всех нужных формул, и использовать его для ввода формулы ? 12.05.2007
  14. Как отобразить выпадающий список используя горячие клавиши ? 16.01.2007
  15. Как создать два выпадающих списка, где при выборе значения в одном из списков, во втором списке исходный диапазон изменится в зависимости от выбранных данных ? 24.12.2006
  16. Как создать выпадающий список, где после выбора значения, новый список будет начинаться со следующего значения в исходном списке ? 16.01.2007
  17. Как создать связанные выпадающие списки, где при выборе заголовка нужной таблицы, во всех остальных списках будут находиться данные этой таблицы ? 16.01.2007
    Примечания
  18. Как отобразить отдельно взятое примечание ? 21.05.2007
  19. Как выделить все примечания в рабочем листе.
    Как определить наличие примечаний в рабочем листе ?
    03.04.2005
  20. Как определить наличие примечаний в рабочей книге ? 31.07.2006
  21. Как осуществить просмотр всех примечаний в рабочей книге ? 31.07.2006
  22. Как удалить сразу все примечания из рабочего листа ? 04.04.2005
  23. Kак найти нужный текст в примечаниях рабочего листа ? 06.01.2007
  24. Как создать примечание с рисунком ? 07.12.2006
  25. Как распечатать таблицу вместе с примечаниями ? 21.12.2006
    Прочее
  26. Как вывести в ячейке название дня в виде текста ? Лидер голосования 24.08.2005
  27. Как вывести в ячейке название месяца в виде текста ? Лидер голосования 24.08.2005
  28. Как определить последний день и последнее число любого месяца ? 21.03.2006
  29. Как создать всплывающую подсказку к ячейке ? 19.10.2006
  30. Как создать всплывающее изображение, при наведении курсора мышки на ячейку ? 07.12.2006
  31. Как можно создать скриншот ячеек в виде рисунка ? 2004
  32. Как создать скриншот ячеек, который изменялся бы вместе с данными и форматом этих ячеек ? 30.09.2007
    [1] [1/2] [2] [3]


  • Ответ : Актуально для MS Excel 97, 2000, XP
    Выделите ячейку или диапазон ячеек для которых нужна проверка данных.
    В меню Данные выберите команду Проверка, далее закладку Параметры и в списках Тип данных и Значение в разделе Условие проверки выбираете нужный Вам тип проверки и ограничения на ввод данных в ячейку (ячейки)
    Примечание : Выбрав закладку Сообщение об ошибке Вы сможете создать своё предупреждение об ошибке.
    Данный метод действует только при непосредственном вводе данных и не работает при копировании или заполнении ячеек средствами VBA.

  • Ответ : Актуально для MS Excel 97, 2000, XP
    Для того, чтобы обойти это ограничение, достаточно вместо формулы, содержащей массив констант или оператор пересечения, использовать именованную формулу, которая будет ссылаться на первоначальную формулу. Пример создания и использования такой формулы, для проверки вводимого текста, можно найти в следующих вопросах [FAQ76], [FAQ79]
  • Ответ : Актуально для MS Excel 97, 2000, XP
    Выделите ячейку или диапазон ячеек для которых нужна проверка данных.
    В меню Данные выберите команду Проверка, далее закладку Параметры и в списке Тип данных выберите Другой. Затем, в поле Формула введите одну из перечисленных формул (где A1 - это ссылка на нужную ячейку) и нажмите кнопку ОК.

    =ЕТЕКСТ(A1)
    =НЕ(ЕЧИСЛО(A1))


    Комментарий :
  • В ряде случаев, запрет будет касаться не только чисел, например, логические значения ИСТИНА/ЛОЖЬ не являются текстом ([1])
  • Дата, по сути, также является числом, поэтому подобное ограничение также касается и дат ([1], [2])
  • Ответ : Актуально для MS Excel 97, 2000, XP
    Выделите ячейку или диапазон ячеек для которых нужна проверка данных.
    В меню Данные выберите команду Проверка, далее закладку Параметры и в списке Тип данных выберите Другой. Затем, в поле Формула введите следующую формулу (где A1 - это ссылка на нужную ячейку) и нажмите кнопку ОК.

    =A1=СЖПРОБЕЛЫ(A1)

  • Ответ : Актуально для MS Excel 97, 2000, XP
    Выделите ячейку или диапазон ячеек для которых нужна проверка данных.
    В меню Данные выберите команду Проверка, далее закладку Параметры и в списке Тип данных выберите Дата (если кроме даты предполагается также и ввод времени) или Целое число (если необходимо запретить ввод времени)
    Затем, в поле Начальная дата или Минимум введите минимальную дату, а в поле Конечная дата или Максимум, соответственно, максимальную.

    Комментарий : Кроме ввода констант, допускается также использование формул, например, если необходимо разрешить только ввод дат текущего года, то :

    Начальная дата: =ДАТА(ГОД(ТДАТА());1;1)
    Конечная дата: =ДАТА(ГОД(ТДАТА());12;31)

    Кроме того, если в списке Тип данных Вы выберите Другой, то в поле Формула также можно применить формулу (где A1 - это ссылка на нужную ячейку)

    =И(A1>=ДАТА(ГОД(ТДАТА());1;1);A1<=ДАТА(ГОД(ТДАТА());12;31))

    Пример #2 разрешающий ввод даты и времени за период с 01/01/2012 00:00:00 по 31/12/2012 23:59:59

    =И(A1>=40909;A1<41275)
  • Ответ : Актуально для MS Excel 97, 2000, XP
    Выделите ячейку или диапазон ячеек для которых нужна проверка данных.
    В меню Данные выберите команду Проверка, далее закладку Параметры и в списке Тип данных выберите Другой. Затем, в поле Формула введите одну из перечисленных формул (где A1 - это ссылка на нужную ячейку) и нажмите кнопку ОК.

    =ЕСЛИ(ЕТЕКСТ(A1);A1=СЖПРОБЕЛЫ(A1);ИСТИНА)
    =ЕСЛИ(ЕНЕТЕКСТ(A1);ИСТИНА;A1=СЖПРОБЕЛЫ(A1))
    =ЕСЛИ(НЕ(ЕТЕКСТ(A1));ИСТИНА;A1=СЖПРОБЕЛЫ(A1))

  • Ответ : Актуально для MS Excel 97, 2000, XP
    Для того, чтобы запретить ввод с клавиатуры данных, которые содержат "ненужные" буквы, проделайте следующее :

    Выделите ячейку или диапазон ячеек для которых нужна проверка данных. Для примера, пусть это будет ячейка A1 или диапазон A1:E1
    В меню Вставка выберите пункт Имя, далее выберите команду Присвоить. В диалоговом окне Присвоение имени в поле Имя введите нужное имя, например ПРОВЕРКА, а в поле Формула введите первую формулу, если Вы хотите запретить ввод перечисленных в формуле букв, причём, с учётом регистра. Или вторую формулу, если регистр символов не важен и нужно запретить как "А", так и "a" После ввода формулы нажмите кнопку ОК.

    =СЧЁТ(НАЙТИ({"А";"б";"В"};A1))=0
    =СЧЁТ(ПОИСК({"А";"Б";"В"};A1))=0


    В меню Данные выберите команду Проверка. После чего, в появившемся стандартном диалоговом окне выберите закладку Параметры, в списке Тип данных выберите Другой, а в поле Формула введите именованную формулу =ПРОВЕРКА, затем, снимите "флажок" Игнорировать пустые ячейки и нажмите кнопку ОК.
  • Ответ : Актуально для MS Excel 97, 2000, XP
    Для того, чтобы разрешить ввод с клавиатуры только тот текст, который соответствует некому шаблону, в данном случае, это внешняя ссылка (см.ниже), содержащая имя книги, листа и адрес ячейки (в виде абсолютной ссылки в стиле A1) проделайте следующее :

    [Архив.xls]Январь2000!$A$1
    'C:\Мои документы\[Книга.xls]Лист1'!$IV$12345

    Выделите ячейку или диапазон ячеек для которых нужна проверка данных. Затем, в меню Данные выберите команду Проверка. После чего, в появившемся стандартном диалоговом окне выберите закладку Параметры, в списке Тип данных выберите Другой, а в поле Формула введите любую из двух нижеприведённый формул (где A1 - это ссылка на нужную ячейку) и нажмите кнопку ОК.

    =ЕЧИСЛО(ПОИСК("[*.xls]*!$*$*";A1))
    =НЕ(ЕОШИБКА(ПОИСК("[*.xls]*!$*$*";A1)))


    Комментарий : Подобное ограничение можно использовать, например, для минимизации ошибок при вводе, но не стоит всецело на него полагаться, т.к. его довольно легко "обойти"
  • Ответ : Актуально для MS Excel 97, 2000, XP
    Для того, чтобы запретить ввод с клавиатуры текста, который не может быть использован в качестве имени листа или книги, т.к. содержит недопустимые символы и/или количество символов превышает лимит, проделайте следующее :

    Выделите ячейку или диапазон ячеек для которых нужна проверка данных. Для примера, пусть это будет ячейка A1
    В меню Вставка выберите пункт Имя, далее выберите команду Присвоить. В диалоговом окне Присвоение имени в поле Имя введите нужное имя, например ПРОВЕРКА, а в поле Формула введите любую из первых двух формул, если Вы хотите запретить ввод некорректного имени листа. Или любую из двух последних формул, если речь идёт о имени книги. После ввода формулы нажмите кнопку ОК.

    =И(ДЛСТР(A1)<32;СЧЁТ(ПОИСК({":";"\";"/";"~?";"~*";"[";"]"};A1))=0)
    =((ДЛСТР(A1)<=31)*(СЧЁТ(НАЙТИ({":";"\";"/";"?";"*";"[";"]"};A1))=0))

    =И(ДЛСТР(A1)<=255;СЧЁТ(ПОИСК({":";"\";"/";"~?";"~*";"""";"[";"]";"|"};A1))=0)
    =((ДЛСТР(A1)<256)*(СЧЁТ(НАЙТИ({":";"\";"/";"?";"*";"""";"<";">";"|"};A1))=0))


    После чего, в меню Данные выберите команду Проверка. В появившемся стандартном диалоговом окне, выберите закладку Параметры, в списке Тип данных выберите Другой, а в поле Формула введите именованную формулу =ПРОВЕРКА, затем снимите "флажок" Игнорировать пустые ячейки и нажмите кнопку ОК.
  • Ответ : Актуально для MS Excel 97, 2000, XP
    В меню Сервис выберите пункт Зависимости и команду Панель зависимостей. Теперь, кликните кнопку Обвести неверные данные, которая расположена на стандартной панели инструментов Зависимости.
    Примечание : Если возникнут трудности с поиском помеченных ячеек, то выделите, используя этот совет, все ячейки содержащие проверку данных. После этого, Вы сможете перемещаться в выделенном диапазоне, используя этот совет, и искать нужные ячейки.
    Комментарий : Используя этот способ, можно пометить только первые 255 ячеек содержащих "неправильные" данные. Для того, чтобы найти все ячейки, скорректируйте данные первых найденных ячеек в соответствии с наложенными условиями проверки и повторите всё заново.
  • Ответ : Актуально для MS Excel 97, 2000, XP
    В меню Правка выберите команду Перейти [F5, CTRL+G], далее нажмите кнопку Выделить. Установите переключатель на проверка данных и нажмите кнопку ОК.
  • Ответ : Актуально для MS Excel 97, 2000, XP
    Выделите ячейку или диапазон ячеек для которых нужно создать выпадающий список.
    Затем, в меню Данные выберите команду Проверка, нажмите на закладку Параметры и в списке Тип данных выберите Список. После чего, в поле Источник, используя разделитель, введите необходимые данные, например :

    Понедельник;Вторник;Среда;Четверг;Пятница;Суббота;Воскресенье

    Комментарий : Количество вводимых символов, включая разделитель, пробелы и т.д., не должно превышать 255.
  • Ответ : Актуально для MS Excel 97, 2000, XP
    Выделите нужную ячейку и нажмите комбинацию клавиш ALT + [cтрелка вниз]
  • Ответ : Актуально для MS Excel 97, 2000, XP
    Выделите ячейку для создания первого выпадающего списка.
    В меню Данные выберите команду Проверка, нажмите на закладку Параметры и в списке Тип данных выберите Список. Затем, в поле Источник введите любую из следующих формул :

    =ЕСЛИ(ЕПУСТО($C$2);$A$2:$A$101;$A$2:ИНДЕКС($A$2:$A$101;ПОИСКПОЗ($C$2;$A$2:$A$101;0)))
    =ЕСЛИ(ЕПУСТО($C$2);$A$2:$A$101;СМЕЩ($A$2;;;ПОИСКПОЗ($C$2;$A$2:$A$101;0)))


    Теперь, выделите вторую ячейку, повторите всё вышесказанное, но введите уже другую формулу (любую из представленных) :

    =ЕСЛИ(ЕПУСТО($B$2);$A$2:$A$101;ИНДЕКС($A$2:$A$101;ПОИСКПОЗ($B$2;$A$2:$A$101;0)):$A$101)
    =ЕСЛИ(ЕПУСТО($B$2);$A$2:$A$101;СМЕЩ($A$2;ПОИСКПОЗ($B$2;$A$2:$A$101;0)-1;0; ЧСТРОК($A$2:$A$101)-ПОИСКПОЗ($B$2;$A$2:$A$101;0)+1))


    Предполагается, что :
  • Диапазон $A$2:$A$101 содержит исходные данные.
  • В ячейке $B$2 будет располагаться первый список.
  • В ячейке $C$2 будет располагаться второй список.

    Комментарий : Данный способ изначально предназначен для работы с числовыми значениями, в т.ч. и датами. Список должен быть отсортирован и содержать уникальные значения (желательно)
  • Ответ : Актуально для MS Excel 97, 2000, XP
    Выделите ячейку, в которой предполагается создать выпадающий список.
    В меню Данные выберите команду Проверка, нажмите на закладку Параметры и в списке Тип данных выберите Список. Затем, в поле Источник введите формулу :

    =ЕСЛИ(ЕПУСТО($B$2);$A$2:$A$101;ИНДЕКС($A$2:$A$101;ПОИСКПОЗ($B$2;$A$1:$A$100;0)):$A$101)


    Предполагается, что :
  • Диапазон $A$2:$A$101 содержит исходные данные.
  • В ячейке $B$2 будет располагаться выпадающий список.

    Примечание : Для восстановления первоначального списка, т.е. списка содержащего данные всех ячеек, достаточно удалить данные ячейки $B$2

    Комментарий : Исходный список должен содержать уникальные значения.
  • Ответ : Актуально для MS Excel 97, 2000, XP
    1. Расположите исходные списки так, чтобы они составляли диапазон смежных ячеек, где заголовки будут расположены в одной строке.
    2. Затем, выделите все ячейки этой таблицы, включая заголовки и в меню Вставка выберите пункт Имя и команду Создать.
    3. Если всё проделано правильно, то в появившемся диалоговом окне будет установлен "флажок" напротив в строке выше, и Вы можете нажать кнопку ОК, предварительно убрав все остальные "флажки"
    4. Теперь выделите ячейку, например $F$1, в которой будет располагаться список всех исходных таблиц, и в меню Данные выберите команду Проверка. Затем, нажмите на закладку Параметры и в списке Тип данных выберите Список. После чего, в поле Источник введите формулу, например =$A$1:$D$1
    5. Теперь выделите все зависимые ячейки и в меню Данные выберите команду Проверка, нажмите на закладку Параметры и в списке Тип данных выберите Список, а в поле Источник введите формулу =ДВССЫЛ($F$1)

    Предполагается, что :
  • Диапазон $A$1:$D$1 содержит заголовки всех исходных списков.
  • В ячейке $F$1 будет располагаться выпадающий список, содержащий имена всех списков.

    Комментарий : Будьте внимательны при создании заголовков Вашей таблицы, т.к. данные в этих заголовках, должны соответствовать правилу присвоения имен ячейкам. В противном случае, созданные имена ячеек не будут в точности совпадать с заголовками столбцов, и это негативно повлияет на работоспособность данного примера.
  • Ответ :
    Выделите ячейку, примечание к которой нужно отобразить на экране. Затем щёлкните правой кнопкой мышки и в появившемся контекстном меню выберите команду Отобразить примечание.

    Комментарий :
    Данный совет не будет работать при сгруппированных листах. Кроме того, чтобы нужное примечание всегда отображалось на экране, необходимо проделать следующее : в меню Сервис выберите команду Параметры, нажмите на закладку Вид. Установите переключатель напротив только индикатор в разделе Примечания и нажмите кнопку ОК.
  • Ответ :
    Вариант I. В меню Правка выберите команду Перейти, далее нажмите кнопку Выделить. Установите переключатель на примечания и нажмите кнопку ОК.
    После этого будут выделены все ячейки, содержащие примечания. Если таковых не окажется, то Вы получите сообщение следующего содержания : Не найдено ни одной ячейки, удовлетворяющей указанным условиям.

    Вариант II. Выделите все ячейки рабочего листа, для этого нажмите кнопку, находящуюся на пересечении заголовков строк и столбцов. Затем, не перемещая курсор мышки, кликните правой кнопкой мышки. Если в рабочем листе есть примечания, то в появившемся контекстном меню будет команда Удалить примечание
  • Ответ :
    В меню Вид выберите пункт Панели инструментов и команду Рецензирование. Если кнопки Предыдущее примечание и Следующее примечание заблокированы, то это означает отсутствие примечаний во всей рабочей книге.

    Комментарий : Данный совет не будет работать при сгруппированных листах.
  • Ответ :
    В меню Вид выберите пункт Панели инструментов и команду Рецензирование. Теперь используя кнопки Предыдущее примечание и Следующее примечание Вы можете осуществить просмотр всех примечаний в рабочей книге.
    Примечание : Если рабочий лист защищён, то для просмотра текста примечаний кликните кнопку Отобразить все примечания
  • Ответ :
    Вариант I. Выделите все примечания [См. выше]
    В меню Правка выберите пункт Очистить, далее выберите команду Примечания.
    Вариант II. Вы можете обойтись без выделения примечаний, однако при этом Вам не будет известно, имелись или нет примечания в рабочем листе.
    Совет : Если Вы хотите удалить примечания только в нужном диапазоне, то в начале необходимо выделить именно этот диапазон ячеек.
    Вариант III. Используйте команду Удалить примечание [См. выше > Вариант II]
  • Ответ :
    В меню Правка выберите команду Найти [CTRL+F], затем в поле Что : введите искомый текст, а в списке Область поиска : выберите примечания и нажмите кнопку Найти далее.

    Комментарий :
  • При поиске допускается использование символов подстановки ? и *, например, при поиске б?p будут найдены бар, бур, бор, набор, а при поиске б*н будут найдены кабан, баран, работник
  • Можно осуществить поиск с учётом регистра символов (Учитывать регистр) т.е. поиске слова рубль не будут учитываться слова Рубль, РУБЛЬ, руБль и наоборот, а также поиск полного совпадения искомого текста и содержимого ячейки (Ячейка целиком)
    Совет : Если Вы хотите осуществить поиск только в нужном диапазоне, то в начале необходимо выделить именно этот диапазон ячеек.
  • Особенности MS Excel XP
    Вместо поля Что: используйте поле со списком Найти:
  • Ответ :
    Вариант I. Выделите нужную ячейку. Затем в меню Данные выберите команду Проверка и нажмите на закладку Сообщение для ввода. Теперь в поле Заголовок введите заголовок подсказки, а в поле Сообщение введите основной текст и нажмите кнопку ОК.

    Комментарий :
    При сортировке ячеек, содержащих такие подсказки, последние не перемещаются вместе с данными, так что будьте внимательны.

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

    Комментарий :
    Для того, чтобы примечания отображались на экране только при выделении нужной ячейки проделайте следующее : в меню Сервис выберите команду Параметры, нажмите на закладку Вид. Установите переключатель напротив только индикатор в разделе Примечания и нажмите кнопку ОК.
  • Ответ :
    Вариант I. В меню Вид выберите пункт Панели инструментов и команду Элементы управления. Теперь нажмите кнопку, например, Надпись и разместите выбранный элемент управления в нужном месте рабочего листа, при необходимости изменив его размер. Затем кликните правой кнопкой мышки и в контекстном меню выберите команду Свойства, после чего :
    - удалите текст, который отображается напротив поля Caption
    - напротив поля BackStyle выберите 0 - fmBackStyleTransparent
    - напротив поля MousePointer выберите 99 - fmMousePointerCustom
    - кликните мышкой небольшую серую кнопку в левой части поля MouseIcon и выберите файл, содержащий нужную иконку.
    Теперь нажмите на кнопку Выход из режима конструктора, которая расположена на панели инструментов Элементы управления

    Вариант II. Выделите нужную ячейку. В меню Вставка выберите команду Примечание. Удалите ненужный текст, затем подведите курсор мышки к любому краю примечания, при этом курсор должен измениться, и кликните левой кнопкой мышки. В контекстном меню выберите команду Формат примечания. В диалоговом окне выделите закладку Цвета и линии и в разделе Заливка кликните список Цвет : и команду Заливка. Теперь выделите закладку Рисунок, нажмите кнопку Рисунок, выберите файл с нужным изображением и нажмите кнопки OK и OK.

    Комментарий :
  • Размер используемого файла влияет на размер рабочей книги, в которой находятся подобные примечания.
  • При необходимости, Вы можете изменить размер примечания.
  • См. выше
  • Ответ :
    Выделите диапазон ячеек которые Вы хотите скопировать в виде рисунка.
    Нажмите и удерживайте клавишу SHIFT, в меню Правка выберите команду Копировать рисунок, далее отпустите клавишу SHIFT и нажмите кнопку ОК.
    Выберите место для копирования рисунка. Если Вы выбрали Excel, то Нажмите и удерживайте клавишу SHIFT, в меню Правка выберите команду Вставить рисунок, далее отпустите клавишу SHIFT.

    Примечание : Убедитесь, что столбец имеет ширину равную максимальному значению ячейки, т.к. при создании рисунка за образец берётся ширина столбца, а не размер данных в ячейке. Не забывайте также, что стандартная сетка, которая не выводится на печать также копируется в рисунок.
  • Ответ :
    Выделите диапазон ячеек. Затем, в меню Правка выберите команду Копировать. Выберите место, где должен располагаться скриншот выделенного диапазона. Нажмите и удерживайте клавишу SHIFT, в меню Правка выберите команду Копировать рисунок, потом отпустите клавишу SHIFT.
    Воспроизведение любых опубликованных здесь материалов возможно только с письменного разрешения автора : Microsoft Excel 95, 97, 2000, XP

  • © 2004-2016 Климов П.Ю. Все права защищены. WebDesign & Error's Klimoff