Excel

Введение

Excel - пожалуй, самая популярная сегодня программа электронных таблиц. Ею пользуются деловые люди и ученые, бухгалтеры и журналисты. С ее помощью ведут разнообразные списки, каталоги и таблицы, составляют финансовые и статистические отчеты, обсчитывают данные каких-нибудь опросов и состояние торгового предприятия, обрабатывают результаты научного эксперимента, ведут учет, готовят презентационные материалы. Для ведения домашней бухгалтерии Excel тоже вполне подходит.

Основное отличие  электронных таблиц от тех табличек, которые можно строить в Microsoft Word и других текстовых редакторах, состоит в том, что настоящие электронные таблицы оснащены возможностью производить вычисления. Ведь Word табличка - это просто способ расположения слов и чисел, вы не сможете попросить свой текстовый редактор, к примеру, посчитать сумму чисел по столбцу, а результат поместить в такую-то ячейку. То есть попросить-то сможете, а вот посчитать всего этого Word не сумеет. Зато Excel сумеет.

Excel может вычислять суммы по столбцам, строкам или любым иным группам ячеек, перемножать и делить, брать проценты; он сумеет посчитать среднее арифметическое, дисперсию или срок погашения кредита. В нем вообще можно использовать множество стандартных функций - финансовых, математических, логических, статистических.

Оформление таблиц может быть самым разнообразным (а это немаловажно, когда нужно  распечатать прайс-лист, справку  или презентационный материал), возможности  форматирования данных - как в хорошем  текст - процессоре: можно менять шрифты, начертания, выделять строки, столбцы или отдельные ячейки текста цветом, рамочками и линеечками, закрашивать области фоном или цветом, строить по табличным данным графики и диаграммы, вставлять в таблицу картинки и т.д. и т.п.

Программа Excel достаточно мощная, возможности ее, особенно в последних версиях, весьма обширны. Одних только математических, логических, бухгалтерских, статистических функций, которые Excel умеет выполнять над табличными данными, - более двухсот штук.

1 Основные понятия

        1.1 Что такое формула?

        1.2 Что такое макрос и где его искать?

        1.3 Что такое модуль? Какие бывают модули?

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

1.1 Что такое формула? 

          В первую очередь Excel, конечно же, таблица. Но таблицы можно рисовать и в Word. Главное преимущество Excel - это функции и формулы. Поистине мощный инструмент этого приложения и любой, кто начал пользоваться Excel, рано или поздно начинает использовать формулы для решения своих задач. Здесь я приведу основные понятия. Если Вы знаете что такое функция, где её найти и как записать в ячейку, то этот раздел Вам читать явно не надо. 

Функция - встроенный инструмент Excel, способный вернуть значение, в зависимости от переданного ему параметра.  

Вот пара примеров функций: 

ЕСЛИ(логическое выражение; значение если ИСТИНА; значение если ЛОЖЬ)

ВПР(Искомое значение; таблица; номер столбца; интервальный  просмотр) 

Однако не все  функции требуют обязательного  ввода параметров. Приведенные ниже функции не имеют ни одного параметра: 

ТДАТА() - возвращает текущее время и дату в формате даты и времени - 01.01.2001 10:00

СЕГОДНЯ() - возвращает текущую дату в формате даты - 01.01.2001

ИСТИНА() - возвращает логическое значение ИСТИНА

ЛОЖЬ() - возвращает логическое значение ЛОЖЬ

НД() - возвращает неопределенное значение #Н/Д

ПИ() - возвращает округленное до 15 знаков число Пи - 3,14159265358979

СЛЧИС() - возвращает равномерно распределенное случайное число большее или равное нулю и меньше единицы - 0,376514074162531 

    Полный  перечень доступных функций можно посмотреть, нажав на значок fx (диспетчер функций), расположенный чуть левее строки формул. 

Записать формулу  в ячейку можно двумя способами:

   -поставить ячейке знак "=" и вписать имя функции и ссылки на аргументы прямо в ячейке(например: =ЕСЛИ(A1=B1;A1;B1));

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

Есть в списке категория - "10 недавно использовавшихся". Она самая первая в диспетчере и выбирается по умолчанию. Список функций этой категории можно посмотреть и вызвать любую из списка функцию можно так же и не входя в диспетчер. Для этого в нужную ячейку вводим знак "=". В поле адреса ячейки(на рис. там написано - ПРОСМОТР) будет отображена последняя использовавшаяся функция. А если нажать на стрелочку чуть правее имени функции, то откроется выпадающий список с 10 последними использовавшимися функциями. Просто выбираем оттуда нужную и появиться окно ввода параметров функции.

функция или  формула всегда должны начинаться со знака "=", иначе Excel воспримет написанное Вами как текст 

Естественно, результат  можно получить, используя только одну функцию, но чаще всего используются именно всякие комбинации из нескольких функций. С помощью формул можно решить многие задачи, не прибегая к помощи Visual Basic for Application(VBA). 
 

1.2 Что такое макрос и где его искать? 

   Наверное, многие слышали это слово "макрос", но не все имеют точное представление что это, если заглянули на эту страничку. 

Макрос - это  макрокоманда(так звучит в правильном переводе с англ.языка), содержащая последовательность действий, записанных пользователем. Запись производится с  помощью встроенного в пакет Microsoft Office языка программирования - Visual Basic for Application(VBA). 

Зачем же нужны  макросы? А нужны они для того, чтобы избавить Вас от рутинного  выполнения одних и тех же действий. Например, Вам каждый день приходиться  копировать данные, расположенные в одном и том же месте в другое место по несколько раз. Скучно и утомительно. Вы просто можете записать один раз все эти действия макрорекордером, а в дальнейшем только вызывать записанный макрос и он все сделает за Вас. Что немаловажно, для использования макрорекордера и записи макроса не надо обладать никакими навыками программирования. Надо просто уметь нажать кнопку. А какую именно я сейчас и расскажу. 

Для пользователей  Excel 2003 запись макроса доступна из: Сервис-Макрос-Начать запись(рис.1) 

рис.1 

Для пользователей  Excel 2007 можно вынести на ленту панель "Разработчик" – Меню - Параметры Excl – Основные - Показывать вкладку "Разработчик" на ленте(рис.2) - и оттуда вызывать запись макроса, а можно записывать и непосредственно из строки состояния. 

рис.2 

Для этого нужно  убедиться в том, что в настройках Строки состояния стоит галочка  напротив "Запись макроса"(рис.3). Если данная галочка включена, то в  строке состояния будет значок, отображающий текущее состояние записи(Нет записи - рис.4, Идет запись - рис.5).

рис.3 

рис.4 

рис.5 

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

рис.6 

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

После записи макроса  Вы сможете запустить его из любой  версии Excel, нажав сочетание клавиш Alt+F8 и выбрав из списка(рис.7). 

рис.7 

Также это окно можно вызвать и через панель: в Excel 2007 - вкладка "Разработчик" - Макросы, в Excel 2003 – Сервис - Макрос- Макросы. Если Вы перед записью назначили макросу сочетание клавиш, то соответственно Вы также можете вызвать этот макрос и нажатием данного сочетания. 

Примечание: необходимо помнить, что макрос записывает АБСОЛЮТНО  ВСЕ Ваши действия - ошибки, прокручивание  экрана, переключение между листами, выделение ячеек и т.д. Так что  перед записью своих действий советую сначала тщательно продумать все свои действия, чтоб в дальнейшем записать только то, что нужно записать. Это сократит как сам код, так и время его выполнения. 
 

1.3 Что такое модуль? Какие бывают модули? 

    Любой  код VBA должен быть где-то записан. Для хранения кодов в VBA имеются модули. Имеется пять основных типов модулей. Модуль листа, модуль книги, стандартный модуль, модуль пользовательской формы, модуль класса. 

Модуль листа(Лист1 или Sheet1) - на рис.2: Лист1(Лист1),Лист2(Лист2),Лист3(Лист3). Для каждого листа книги имеется свой отдельный модуль. Попасть в модуль листа проще, чем в остальные модули. Для этого надо просто щелкнуть правой кнопкой мыши по ярлычку листа и выбрать из контекстного меню пункт Исходный текст(рис.1). 

рис.1 

Можно и более трудным путем пойти - через редактор VBA: Alt+F11 и в окне Проводника объектов дважды щелкнуть по объекту с именем листа(рис.2). 

рис.2 

Если данное окно у Вас не отражается нужно  нажать Ctrl+R либо в меню-View-Project Explorer(рис.3) 

рис.3 

В модуле листа содержаться встроенные событийные процедуры, каждая из которых отвечает за обработку определенного события. Названия данных процедур носят достаточно информативные имена и не думаю, что нуждаются в расшифровке. Посмотреть их можно так: выбираете в списке объектов(на рис.4 помечен 1) Worksheet, а в правом окне выбора процедур(на рис.4 помечен 2) Вы найдете все процедуры, доступные для выбранного листа. Процедуры, события для которых уже используются, выделены жирным шрифтом. 

рис.4 

Модуль книги(ЭтаКнига или ThisWorkbook) - на рис.2: ЭтаКнига. В модуль книги можно попасть только через редактор VBA описанным выше способом. Двойной щелчок по ЭтаКнига(ThisWorkbook). В модуле книги так же содержаться "встроенные" событийные процедуры. Так же как и для листа выбираете в списке объектов, только не Worksheet, а Workbook. В правом окне выбора процедур Вы найдете все процедуры, доступные для объекта ЭтаКнига. 

Примечание: для  всех кодов, приведенных на сайте, достаточно просто открыть необходимый модуль(книги или листа) и вставить предложенный код. Корректировка может понадобиться только в случаях, когда в модуле Листа или Книги Вашего файла уже имеется код в необходимой событийной процедуре. 
 

Стандартный модуль(Module) - на рис.2 Module1. В основном именно в стандартных модулях содержится большая часть кодов. Для стандартных модулей нет предопределенных событийных процедур, поэтому в них процедуры пишутся полностью вручную. Процесс создания описывать не буду - все это есть на куче других сайтов и в учебниках. Как создать такой модуль? Ведь изначально его нет. А создать просто: в окне проводника объектов щелкаем правой кнопкой мыши - Insert-Module. И все. При записи макрорекордером модули создаются автоматически. 

Модули форм(UserForm) - на рис.2 UserForm1. Содержаться внутри Пользовательской формы(UserForm) и её объектов. В Пользовательских формах в основном все завязано именно на событийных процедурах самой формы и на дочерних объектах этой формы(Кнопки, ТекстБоксы, КомбоБоксы и т.д.). Очень удобно использовать Пользовательские формы в своих приложения для, так сказать, общения с пользователем. Т.к. через формы очень удобно отслеживать действия пользователя и можно запретить доступ к листам с данными, путем их скрытия. Создается форма так же как и модуль: в окне проводника объектов щелкаем правой кнопкой мыши - Insert-UserForm. 

Модуль класса(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). Далее все так же как во втором способе, но необходимо еще указать Диапазон. Можно просто поставить курсор в поле Диапазон и затем просто выделить диапазон на листе, которому хотите присвоить имя.