Excel

Модуль класса(ClassModule) - на рис.2 Class1. В большинстве случаев создается специально для отслеживания событий различных объектов. Вряд ли понадобиться начинающим изучение VBA, хотя все зависит от поставленной задачи. В любом случае, перед работой с модулями классов лучше научиться хоть чуть-чуть работать с обычными модулями и самостоятельно писать процедуры. Создается: в окне проводника объектов щелкаем правой кнопкой мыши - Insert-Class Module. 

 Для того, чтобы создать новый модуль(Module), модуль класса(ClassModule) или пользовательскую форму(UserForm) надо просто в окне Проводника объектов(Project Explorer) щелкнуть правой кнопкой мыши, выбрать пункт Insert и затем тип добавляемого объекта(Module, ClassModule, UserForm).  
 

1.4 Как Excel воспринимает данные? 

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

           Вот например: занесите в ячейку число 43587, а затем присвойте этой ячейке формат - Дата-"ДД.ММ.ГГГГ"(кто не знает как это сделать: щелкаем правой кнопкой мыши по ячейке - Формат ячеек - вкладка Число; либо просто выделяем нужную ячейку - Ctrl+1). И что мы видим? Дату - 02.05.2019. Т.е. 43587 в переводе на дату равно 02.05.2019. Исходя из этого можно догадаться, что 43587 - это количество дней. В общем 1 - это одни целые сутки. Но ведь с 01.01.0001(от рождества Христова) прошло гораздо больше дней, чем 43587 - свыше 733000. Правильно. Цифра 1 для Excel равна дате 01.01.1900 года, а не как не первого января первого года. 01.01.1900 - это начальная точка отсчета времени для Excel. Однако здесь тоже не без сюрпризов. Для компьютеров под управлением Macintosh в Excel отсчет начинается с 01.01.1904. Для корректной совместимости с такими компьютерами в Excel предусмотрена возможность включения исчисления дат 1904. Для Excel 2003: Сервис – Параметры - Вычисления - Использовать систему дат 1904; для Excel 2007: Меню - Параметры Excel – Дополнительно - Использовать систему дат 1904. 

То же самое  и со временем, но с одной небольшой  разницей. Т.к. целые числа для  Excel это даты, то они уже не могут быть временем. Следовательно временем является дробная часть чисел. Например 0,5 будет равно 12:00, а 0,124 - 2:58:34. 

Следовательно, если записать в ячейку 43587,124, то в  перевод на дату/время это будет - 02.05.2019 2:58:34. 

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

2 Списки

        2.1 Списки Автозаполнения

        2.2 Выпадающие списки

        2.3 Связанные выпадающие списки

        2.4 Именованные диапазоны

        2.5 Динамические именованные диапазоны 

2.1 Списки автозаполнения 

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

рис.1 

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

рис.2 

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

рис.3 

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

рис.4 

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

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

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

2.2 Выпадающие списки 

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

рис.1 

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

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

рис.2 

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

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

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

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

рис.3 

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

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

рис.4 

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

рис.5 

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

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

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

2.3 Связанные выпадающие списки 

         Если Вы читаете эту страницу, то я предположу, что Вы уже  знаете, что такое выпадающий список и как его создать. Связанные списки, что же это такое? Это когда список значений одного выпадающего списка зависит от значения, выбранного в другом выпадающем списке. Непонятно? Разберем поконкретнее. Есть ячейка А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) 

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

2.4 Именованные диапазоны 

        Именованным диапазоном может  быть как одна отдельная ячейка, так и диапазон. Создать можно несколькими способами. Но независимо от метода создания есть общие правила для имен в Excel. 

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

Нельзя в качестве имени использовать зарезервированные  константы - R, C и RC(как прописные, так  и строчные). 

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

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

рис.1 

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

рис.2 

рис.3 

Способ третий - жмем Ctrl+F3, либо в 2007 Excel вкладка Формулы - Диспетчер имен - Создать(либо на той же вкладке сразу - Присвоить имя). Появляется Диспетчер создания имен(рис.3). Далее все так же как во втором способе, но необходимо еще указать Диапазон. Можно просто поставить курсор в поле Диапазон и затем просто выделить диапазон на листе, которому хотите присвоить имя.