Как часто Вы набирали однотипные данные в ячейки? Например, наименование товара. Можно, конечно, скопировать одну ячейку и вставить в нужную строку. Но что делать, если таблица большая и даже чтобы найти нужное наименование приходится тратить по полминуты и более? Да проще вбить заново. Но так можно допустить опечатку и один и тот же товар будет записан по разному. В дальнейшем это может усложнить работу с данными. Данная надстройка призвана обелегчить Вам жизнь: сократить время, потраченное на нудную, однообразную работу, а вместе с тем повысить ее качество и эффективность.

Как это работает.
На листе жмете Ctrl+Enter и рядом с ячейкой появляется список, который позволяет не только выбирать, но и производить поиск по интересующим Вас данным. Для ввода данных достаточно нажать просто Enter, а для закрытия формы — Esc. Вот перечень всего, что умеет вызванный список:

• Не содержит повторов (уникальный). Легко выявить однотипные данные;
• Отсортирован по возрастанию. Возможность быстро найти то, что нужно;
• После вызова сразу готов к поиску/выбору из списка. Лишние движения ни к чему;
• Позволяет искать с использованием специальных подстановочных символов (*,?,~ и т.п.);
• Осуществлять быстрый поиск по «шаблону». Если ячейка, из которой был вызван список, содержит информацию, поиск будет произведен по ней;
• Появляется рядом с текущей/активной ячейкой и не «убегает» за пределы экрана;
• Навигация привычными стандартными клавишами: Up [Вверх], Down [Вниз], Page Up [На страницу Вверх ], Page Down [На страницу вниз];
• Корректная работа со всеми типами данных: строки, даты, числа;
• Обработка ошибок формул листа (спасибо EducatedFool). Ни каких пустых строк в списке;
• Обработка защиты ячеек листа (спасибо EducatedFool). В защищенные ячейки ввод запрещен;
• Информация об общем количестве списка и найденных по запросу элементах;
• Быстрый вызов Ctrl+Enter;
• Быстрое закрытие по Esc;
• Быстрый ввод выбранных данных по Enter.

Помимо всего вышеперечисленного, позволяет сэкономить на размере файла за счет формирования списка «на лету», который создается в разы быстрее, если данные упорядочены или частично упорядочены по возрастанию.

Откуда берутся данные? По умолчанию из активного столбца. Вместе с тем, предусмотрена «настройка» индивидуального формирования списка для каждой книги, листа, столбца, строки и даже ячейки : ) Заранее знать кому и что нужно, просто не возможно.

Следует отметить, что под «настройкой» подразумевается написание программного кода под конкретную задачу, т.е. Ваши нужды. Поэтому данная услуга является платной, и составляет 50-100 руб. в зависимости от сложности. Если Вам необходима какая-то модификация/изменение возможностей программы, цена будет варьироваться в пределах 200-300 руб. А если Вам ничего не надо, пользуйтесь на здоровье ; )

Для «настройки» и/или модификации программы, просьба писать на nerv-net@yandex.ru с указанием Ваших нужд, сроков реализации и информации, которая потребуется для внесения изменений.

Скачать надстройку »

Author: nerv
Last Update: 29.11.2011
На случай, если у кого-то возникнет непреодолимое желание отблагодарить автора: Яндекс Деньги — 41001156540584, WebMoney — R219241762337

Excel обладает очень неплохим инструментом для проверки введенных данных. В их число входит создание выпадающего списка. В этом случае в одной ячейке может содержаться несколько значений, организованных в виде списка(рис.1).

рис.1

Теперь разберем поподробней.

Необходимо выбрать ячейку(можно даже несколько сразу), в которую поместить этот список. В меню выбираем Данные — Проверка данных. Появляется форма(рис.2).

рис.2

Выбираем вкладку Параметры, Тип данных — Список.

Можно ввести список значений вручную в поле «Источник:«(значения в этом случае необходимо заносить через «точку-с-запятой», как на рис.2).

А можно воспользоваться именованным либо обычным диапазоном.

При использовании обычного диапазона в поле «Источник:» просто указываем диапазон со значениями. Для этого ставим курсор мыши в это поле и затем выделяем необходимый диапазон со значениями. Перед адресом диапазона Excel сам поставит знак равно. На рис.3 показан пример с выбором значений из диапазона =$A$1:$A$10.
рис.3

При использовании именованного диапазона необходимо сначала его создать. Создание именованных диапазонов см. здесь.

После создания именованного диапазона в поле «Источник:» вписываем имя этого диапазона. Перед именем ставим знак равно. На рис.4 используется именованный диапазон «Список1«.

рис.4

Распространить изменения на другие ячейки с тем же условием — данная галочка понадобится уже после создания Проверки данных в ячейках. Нужна она для следующего: у Вас на листе есть много разных проверок данных. Так вот, когда Вы хотите изменить тип или условия проверки, то Вы можете выделить лишь одну ячейку на листе, изменить необходимые параметры, поставить данную галку и нажать Ок. Внесенные в Проверку данных изменения будут применены для всех ячеек, в которых были такие же условия.

На вкладке «Сообщение для ввода» можно написать текст, который будет отображаться при активации ячейки с проверкой данных(рис.5).

рис.5

Вкладка «Сообщение об ошибке«. Здесь можно указать следует отображать сообщение об ошибочном вводе или нет, и сам тип выводимого сообщения об ошибке.

Останов, Сообщение — можно ввести только значение из выпадающего списка. Различается только вид сообщения.

Предупреждение — помимо выбора из списка есть возможность  ввести и другое значение, которое отсутствует в списке.

Скачать пример »

Именованным диапазоном может быть как одна отдельная ячейка, так и диапазон. Создать можно несколькими способами. Но независимо от метода создания есть общие правила для имен в Excel. Для чего это может быть нужно: обращение к именованному диапазону гораздо удобнее, чем прописывание адреса в формулах и VBA. Например, Вы ссылаетесь на диапазон A1:C10 в своем коде VBA. Затем у Вы переместили эти данные в другое место листа(скажем в диапазон D2:F11). Теперь Вам придется идти в код и менять адрес диапазона. А если бы Вы задали имя диапазону A1:C10(к примеру «Диапазон1«), то в коде ничего менять не пришлось бы. Вам надо было бы просто обращаться в коде к имени диапазона и это никак не влияло бы на его адрес.

Как обратиться к именованному дипазону.

Через VBA:

    MsgBox Range("Диапазон1").Address
    MsgBox [Диапазон1].Address

	MsgBox Range("Диапазон1").Address
	MsgBox [Диапазон1].Address

В формулах:

1. =СУММ(Диапазон1)
2. =ВПР(«Критерий»;Диапазон1;2;0)

Если Вы при указании диапазона в формуле просто выделяете именованный диапазон, то его имя автоматически подставится в формулу вместо адреса.

Некоторые ограничения, накладываемые на создание имен:

• В качестве имени диапазона не может быть использованы словосочетания, содержащие пробел. Вместо него лучше использовать нижнее подчеркивание _ или точку(например «Name_1«, «Name.1«);
• Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (\). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания;
• Нельзя в качестве имени использовать зарезервированные константы — R, C и RC(как прописные, так и строчные). А нельзя потому, что данные буквы используются самим Excel для адресации ячеек при использовании стиля R1C1. Имена не могут быть такими же, как ссылки на ячейки, например, B$100 или R1C1;
• Длина имени не может превышать 255 символов.

Итак, создаем:

Способ первый — обычно при создании простого именованного диапазона я использую именно его. Выделяем ячейку или группу ячеек, имя которым хотим присвоить. Затем щелкаем левой кнопкой мыши в окне адреса(рис.1) и вписываем имя. Жмем Enter. Диапазон создан.

рис.1

Способ второй-  Выделяем ячейку или группу ячеек. Жмем правую кнопку мыши для вызова контекстного меню ячеек. Выбираем пункт — Имя диапазона(рис.2). Появляется диспетчер создания имен(рис.3). В поле Имя вписываете имя диапазона, в поле Область выбираете область действия создаваемого диапазона — Книга, либо Лист. При выборе Лист, созданный именованный диапазон будет доступен только из выбранного листа. При выборе Области Книга созданный диапазон можно будет использовать из любого листа данной книги. В поле Примечание можно записать пометку о созданном диапазоне, например для каких целей Вы планируете его использовать. Позже эту информацию можно будет посмотреть из диспетчера имен(о нем далее). Диапазон — при данном способе создания в этом поле автоматически проставляется адрес выделенного ранее диапазона. Его можно изменить.

рис.2

рис.3

Способ третий — жмем Ctrl+F3, либо

в 2007 Excel вкладка Формулы-Диспетчер имен-Создать(либо на той же вкладке сразу — Присвоить имя);
в 2003 Excel - Вставка-Имя-Присвоить.

Появляется Диспетчер создания имен(рис.3). Далее все так же как во втором способе, но необходимо еще указать Диапазон. Можно просто поставить курсор в поле Диапазон и затем просто выделить диапазон на листе, которому хотите присвоить имя.

Очень часто при использовании связки Выпадающий список-Именованный диапазон возникает проблема: а что будет, когда я добавлю в свой диапазон новые данные? Ведь для того, чтобы они отобразились в списке, необходимо будет изменить адрес именованного диапазона. Либо указать заранее расширенный диапазон. Тогда появляются лишние пустые значения в списке, что, согласитесь, тоже не очень-то красиво и не совсем удобно. И вот здесь помогут так называемые Динамические диапазоны. Создав вместо обычного именованного диапазона динамический, Вам не придется каждый раз менять адрес диапазона для отображения в списке всех добавленных значений. И отображаться будут только значения, никаких пустых строк.
Вызываем Диспетчер создания имен и в поле Диапазон пишем формулу:

=СМЕЩ(Лист2!$A$1;;;СЧЁТЗ(Лист2!$A$1:$A$1000);)

Столбец, конечно свой указываете, в примере это столбец А. Если думаете, что у Вас может быть более 1000 значений, то увеличиваете значение $A$1000 на необходимое количество строк. Хотя мне лично страшно представить себе такой выпадающий список. Но динамический диапазон может ведь пригодиться и для других целей.

Примечание: созданный таким образом диапазон нельзя использовать в составе функции ДВССЫЛ при создании зависимых выпадающих списков(про зависимые выпадающие списки можно почитать тут). В смысле использовать можно, но результата не будет.

Скачать пример »

Если Вы читаете эту страницу, то я предположу, что Вы уже знаете, что такое выпадающий список и как его создать. Связанные списки, что же это такое? Это когда список значений одного выпадающего списка зависит от значения, выбранного в другом выпадающем списке. Непонятно? Разберем поконкретнее. Есть ячейка А1. В ней создан выпадающий список со значениями: Список1, Список2, Список3, Список4, Список5. Есть ячейка В1. В ней тоже есть список. Но нам надо, чтобы список ячейки В1 менялся в зависимости от того, какой список мы выберем в ячейке А1. Т.е. выбрали Список1 — в В1 появился выпадающий список Список1, содержащий значения: Значение1_1, Значение1_2, Значение1_3, Значение1_4, Значение1_5. Выбрали Список2 — в В1 появился выпадающий список Список2, содержащий значения: Значение2_1, Значение2_2, Значение2_3, Значение2_4, Значение2_5. И т.д.

Для осуществления этого нам потребуется создать все эти списки. Создали. В ячейке А1 создаем список списков. А в ячейке В1…Те же операции как и при создании списков: Данные-Проверка данных-Список. Но теперь нам вместо прямого указания имени списка надо указать ссылку на него. В этом нам поможет функция ДВССЫЛ. Просто прописываем эту формулу в поле «Источник:» =ДВССЫЛ($A1).

Список может находится на другом листе(в данном случае надо в формуле либо указать ссылку на лист =ДВССЫЛ(«Лист1!»&$A$1), либо при создании списка задать область действия — Книга — как? смотри здесь) или даже в другой книге. Поподробнее о списках из другой книги. Допустим книга называется «Книга со списком«. И на Лист1 в этой книге и находится нужный нам список. Как в этом случае создать ссылку на нужный список? Тот, имя которого мы выберем в ячейке A1? Очень просто. Мы пишем такую формулу:

=ДВССЫЛ("'[Книга со списком.xls]Лист1'!"&$A$1)

Здесь, правда, не обошлось и без ложки дегтя… Даже двух. Дело в том, что обе книги должны быть открыты. Если Вы закроете книгу со списками, то получите ошибку. И список работать не будет. Так же связанные списки не будут работать с динамическими именованными диапазонами. Жаль, конечно, но таковы особенности функции ДВССЫЛ.

Скачать пример »

Думаю все знают такой прием в Excel, как автозаполнение ячеек путем протягивания мышью крестика? Если еще нет, то расскажу поподробней. Допустим Вы хотите заполнить строку или столбец днями недели(Понедельник, Вторник и т.д.). Что Вы для этого делаете? Правильно, Вы в каждую ячейку вписываете вручную все эти дни. Но в Excel есть прекрасная возможность упростить этот процесс. Для выполнения подобной операции Вам потребуется заполнить лишь первую ячейку. Пишем в неё — Понедельник. Теперь выделяем эту ячейку и ведем курсор мыши к нижнему правому углу ячейки. Курсор приобретет вид черного крестика(рис.1).

рис.1

Как только курсор стал крестиком, жмем левую кнопку мыши и удерживая её тянем вниз(если надо заполнить строки) или вправо(если надо заполнить столбцы) на необходимое количество ячеек. Теперь все захваченные нами ячейки заполнены днями недели. И не одним Понедельником, а по порядку(рис.2).

рис.2

Но это не все. Если вместо левой кнопки мыши, зажать правую и протянуть, то по завершении Excel выдаст меню, в котором будет предложено выбрать метод заполнения: Копировать ячейки, Заполнить, Заполнить только форматы, Заполнить только значения, Заполнить по дням, Заполнить по рабочим дням, Заполнить по месяцам, Заполнить по годам, Линейное приближение, Экспоненциальное приближение, Прогрессия — см.рис.3. Серым шрифтом выделены неактивные пункты меню — те, которые нельзя применить к выделенным ячейкам. Выбираете необходимый пункт и любуетесь результатом.

рис.3

Но и это еще не все. Наряду со встроенными в Excel списками автозаполнения, можно создать и свои списки. Например, Вы часто заполняете шапку таблицы словами: Дата, Артикул, Цена, Сумма. Можно их вписывать каждый раз или копировать откуда-то, но можно сделать и по-другому. Если Вы используете Excel 2003, то переходите — Сервис-Параметры-Вкладка «Списки«. Для Excel 2007 это — Меню-Параметры Excel-вкладка Основные-кнопочка «Изменить списки«. В результате перед Вами что-то вроде этого(рис.4)

рис.4

Выбираете пункт НОВЫЙ СПИСОК — ставите курсор в поле Элементы списка и заносите туда через запятую наименования столбцов, как показано на рис.4. Нажимаем Добавить.

Так же можно воспользоваться полем «Импорт списка из ячеек«. Активируем поле выбора, щелкнув в нем мышкой. Выбираем диапазон ячеек со значениями, из которых хотим создать список. Жмем Импорт. В поле Списки появиться новый список из значений указанных ячеек.

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

Проверка данных является неплохим инструментом Excel для контроля за вносимыми на листах изменениями, не прибегая к помощи VBA. С её помощью можно ограничить ввод в ячейку, разрешив вводить только даты либо время, либо только числа. Да к тому же еще и задать диапазон дат либо предел чисел(к примеру от 1 до 10).Применений, я думаю, можно придумать массу: для корректной работы многих формул требуются корректные исходные данные. Следовательно, мы можем с помощью Проверки данных разрешить пользователю вводить только тот тип и диапазон данных, который может обработать формула, не возвращая значение ошибки. Разберем поподробней.

Сей чудесный инструмент находится: Данные-Проверка данных. Должна появиться такая вот формочка(рис.1)

рис.1

Для начала надо выделить ячейку(или диапазон ячеек) для которой хотим установить проверку. Затем идем в Данные-Проверка данных.

Первая вкладочка — Параметры

В поле Тип данных — мы выбираем собственно тип данных, которые планируем хранить в ячейке. Всего доступно 8 типов: Любое значение, Целое число, Действительное, Список, Дата, Время, Длина текста, Другой. Любое значение — установлен по умолчанию, проверка не осуществляется. Про пункт Списки и то как их создавать можно посмотреть и почитать в разделе Списки — Выпадающие списки. Поэтому рассмотрим мы в данной статье оставшиеся 6 типов.

Целое число — говорит само за себя. После установки такой проверки в ячейку можно будет внести только целое число. Т.е. число, не имеющее дробного остатка(9,1 например).Действительное — тоже, что и в предыдущем пункте, но ввести можно любое число — хоть целое, хоть дробное.

Примечание: применив данные типы проверок, внести в ячейку можно только число. Занести текст Excel уже не разрешит. Однако стоит помнить, что даты и время Excel воспринимает и хранит именно как ЧИСЛОВЫЕ значения, поэтому ввод даты и времени тоже будет разрешен, если он не противоречит остальным условиям проверки(см.ниже). Но для проверки Целое число разрешен будет ввод только даты, т.к. время Excel хранит как дробное значение(кроме 24:00:00 и 00:00:00, которые Excel воспринимает как 1 и 0 соответственно). Более подробно о восприятии Excel-ем данных читайте в статье Как Excel воспринимает данные?.

Дата — тоже все ясно. В ячейке может содержаться только значение даты. Здесь тот же нюанс, что и проверкой на числа, только в обратную сторону.

Время — тоже думаю не нуждается в лишних комментариях.

Длина текста — позволяет ограничить количество вводимых в ячейку символов. Данные могут являться и числом и текстом и временем. Чем угодно, только должны соответствовать остальным условиям проверки.

Я уже упоминал про остальные условия и если посмотреть на картинку, то можно увидеть еще два поля: Значение, Минимум и Максимум. Так вот. Поле Значение имеет так же несколько пунктов: Между, Вне, Равно, Больше, Меньше, Больше или равно, Меньше или равно.

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

А теперь самый интересный тип проверки — Другой. Здесь нам предоставляется возможность более широко контролировать ввод данных. Для заполнения есть только одно поле — Формула. В него необходимо записать формулу и при каждом вводе значений в ячейку, Excel проверит введенное выражение на ИСТИНУ(если быть точнее — он сопоставит введенные данные с формулой). Если значение в ячейке при сопоставлении с формулой проверки является ИСТИНОЙ, то оно введется, а если ЛОЖЬ, то будет выдано окно с сообщением об ошибке(рис.2).

рис.2

Приведу простой пример. В ячейку А1 введите число 1. Выделяете ячейку В1-назначаете проверку данных-Другой. В поле Формула вписываете: =B1=A1. Теперь в ячейку <В1 можно вписать только значение, которое полностью идентично значению в ячейке А1.

Вкладка Сообщение для ввода

Здесь можно написать текст, который будет отображаться при активации ячейки с проверкой данных(рис.3).
рис.3

Вкладка Сообщение об ошибке
Здесь можно указать следует отображать сообщение об ошибочном вводе или нет, и сам тип выводимого сообщения об ошибке.

Останов, Сообщение — можно ввести только разрешенные проверкой значения. Различается только вид сообщения(пример сообщения Останов приведен на рис.2).

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

Но так же необходимо помнить, что какое бы условие на проверку Вы не поставили — значение в ячейке можно удалить, нажав кнопку Del. И от этого никак не уйти — такой проверку сделали разработчики…