Как изменить вид сводной таблицы в excel

Форматирование сводной таблицы Excel для планирования продаж

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

Настройка сводной таблицы в Excel

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

1. Клиент – ID клиента, полное или короткое наименование.
2. Товар – наименование товара или наименование группы товаров.
3. Период – промежуток времени с единицами измерения (годы, кварталы, месяцы, дни), за который были собраны статистические показатели.
4. Количество – количественные данные по продажам в соответствии с единицами измерения (шт., кг., л. и т.п.).
5. Сумма – количество умножено на цены проданных товаров (данный показатель необходим для проведения сложных вычислений при планировании).

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

Пример таблицы с статистическими данными скопируйте из этого фрейма на пустой рабочий лист Excel:

На основе исходных данных построим сводную таблицу, в которую подтянем данные таблицы планирования. Если Ваши исходные данные находятся во внешней базе данных таких как SQL или Access, то можно сразу построить сводную таблицу подключившись непосредственно к внешним источникам данных. Но если данные уже экспортированы в таблицу Excel (так как в нашем случаи), тогда выделите диапазон ячеек A1:E61 и выберите инструмент: «ВСТАВКА»-«Сводная таблица». В появившемся окне «Создание сводной таблицы» просто Нажмите кнопку ОК не меня параметров, установленных по умолчанию.

Сразу после создания нового листа со сводной таблицей присваиваем новые имена и для листа, и для сводной таблицы. Чтобы переименовать лист щелкните правой кнопкой мышки по ярлычку нового листа и выберите из контекстного меню опцию «Переименовать». Потом введите новое имя «Сводный отчет» и нажмите клавишу Enter для подтверждения переименования листа.

Чтобы задать новое имя для новой сводной таблицы, сделайте ее активной и выберите инструмент: «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«АНАЛИЗ»-«Сводная таблица». И в поле «Имя:» введите текст «стАнализ». Потом просто нажмите клавишу Enter для подтверждения нового имени:

Чтобы наша сводная таблица отображала нужные нам данные в соответственном порядке, упорядочиваем значения в полях:

Параметры полей значений в сводной таблице:

• в полю СТРОКИ – значения Клиент и Год;
• в полю КОЛОННЫ – значения Товар;
• в полю ЗНАЧЕНИЯ – Количество.

Автоматически созданная сводная таблица имеет весьма нечитабельный вид и очень плохо воспринимается для визуального анализа данных. Ее необходимо сформатировать.

Параметры сводной таблицы Excel

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

1. Активация. Перейдите курсором на любую ячейку в области сводной таблицы, чтобы сделать ее активной и получить доступ к дополнительной панели инструментов «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ».
2. Преобразование в классический вид. Выберите инструмент: «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«АНАЛИЗ»-«Сводная таблица»-«Параметры». В появившемся окне перейдите на закладку «Вывод» и активируйте галочкой опцию «Классический макет сводной таблицы (разрешено перетаскивание полей)».
3. Скрытие промежуточных итогов. Выберите инструмент: «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«КОНСТРУКТОР»-«Макет»-«Промежуточные итоги»-«Не показывать промежуточные суммы».
4. Изменение стиля. Выберите инструмент: «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«КОНСТРУКТОР»-«Стили»-«Очистить». Благодаря этому действию теперь видны линии границ, разделяющие клиентов.
5. Переименование названий столбцов. Текст в названии столбца «Сумма по полю…» можно отредактировать прямо в ячейке. Важно чтобы новые названия столбцов не совпадали с названиями полей. Для этого можно их просто взять в скобки «», ведь для Excel это уже разные значения.
6. Разделение разрядов для тысяч. Перейдите курсором на любую ячейку со значением в столбце количество и выберите инструмент: «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«АНАЛИЗ»-«Активное поле»-«Параметры поля». В появившемся окне «Параметры поля значений» на вкладке «Операция» нажмите на кнопку «Числовой формат». Откроется окно «Формат ячеек» где нужно в левой коленке выбрать опцию «Числовой», а в правой – активировать галочкой опцию «Разделитель групп разрядов ()» и установить значение 0 в поле «Число десятичных знаков:». ОК на всех открытых окнах.
7. Зафиксировать строки в таблице. Перейдите курсором в ячейку C5 и выберите инструмент: «Вид»-«Окно»-«Закрепить области». Теперь при горизонтальной или вертикальной прокрутке рабочего листа, пользователю будут всегда доступны основные показатели и инструменты управления сводной таблицей.

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

Изменение вида сводной таблицы, построение списков или структуры данных.

В ранних версиях «Эксель» конца прошлого века и начала текущего столетия сводные таблицы формировались в виде структуры и не всегда были удобны для обработки. Приходилось копировать данные из сводной таблицы и дополнительно их обрабатывать на других листах документа.

В современных версиях программы «Эксель» существует возможность изменять вид сводной таблицы при помощи, предусмотренной для этого опции «Макет».

Рассмотрим эту опцию на примере.

Создадим сводную таблицу.

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

Для изменения вида таблицы следует перейти во вкладку «Конструктор».

Выбрать пиктограмму «Макет отчета» .

Эта опция позволяет выбирать вид таблицы: в виде структуры, в сжатом виде, в виде таблицы. Так же можно выбрать опцию «повторять все подписи».

При выборе показа подписей сводная таблица приобретет вид обычной таблицы со списками значений размещенными друг напротив друга.

Добавить комментарий Отменить ответ

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Разработка макета и формата сводной таблицы Excel

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

Конфигурирование формы макета сводной таблицы

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

• Сжатой формы. Данная форма подразумевает отображение элементов из различных полей области строк в одном столбце, а чтобы различить элементы из разных полей, применяются специальные отступы. Подписи строк, используемые в сжатой форме, занимают меньше пространства на экране, в результате чего остается больший объем свободного пространства для числовой информации. Кнопки “Свернуть” и “Развернуть” отображаются в сжатом состоянии, что позволяет скрывать или отображать сведения. Сжатая форма упрощает процесс чтения сводной таблицы и экономит место. Таким образом, данный формат применяется как форма макета для сводных таблиц по умолчанию.

• Табличная форма. В данной форме на экране представлен один столбец для каждого индивидуального поля, а также выделяется специальная область для заголовков полей.

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

Принцип приведения сводной таблицы к табличной, структурной или сжатой форме

1. Щелкните мышкой в любом местоположении сводной таблицы.

На ленте будет открыта категория “Работа со сводными таблицами”.

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

• Дабы избежать выхода связанных данных за пределы экрана по горизонтали (что, свою очередь, минимизирует использование функции прокрутки), стоит выбрать команду “Показать в сжатой форме”

В визуальном представлении сжатой формы поля расположены в одном столбце и отображаются вместе с отступами, дабы продемонстрировать вложенность столбцов.

• Для отображения структуры данных в классическом представлении сводной таблицы, воспользуйтесь командой “Показать в форме структуры”
• Чтобы просмотреть всю информацию в ставшем привычном, более традиционном и устоявшемся формате таблицы, где можно с легкостью осуществлять копирование ячеек на другие листы, выберите функцию “Показать в табличной форме”.

Как изменить способ отображения подписей элементов в представлении макета?

1. В сводной таблице выполните выделение поля строки.

После того, как поле строки будет выбрано, на ленте окажется открытым раздел “Работа со сводными таблицами”.

Также в табличной форме или форме структуры доступен двойной щелчок на поле строки и быстрый переход к шагу 3.

Сводные таблицы в Excel: как создать? ⁠ ⁠

5 Данные из Сводных можно быстро визуализировать, построив график или диаграмму и даже создать не сложный интерактивный Дашборд (Dashboard).

Всё это делается в пару кликов мыши.

Главное правило при создании Сводной – исходные данные, по которым будет строится таблица, должны быть правильными. Что это значит?

Как правило, исходные данные должны иметь плоскую, иерархическую организацию. Создав правильную структуру исходной таблицы, в Сводной можно легко просмотреть информацию на любом из ее уровней. Структура часто используется при создании отчетов для представления руководителям разных уровней. Так, высшему руководству фирмы представляются только итоги по подразделениям, руководителям подразделений — итоги по отделам, а каждому руководителю отдела — все данные по его отделу.

Первое видео из серии Сводные таблицы в Excel, о том, как создать сводную таблицу, изменить вид, как группировать данные, как использовать фильтры в сводных, изменить источник исходных данных таблицы ⬇⬇⬇

661 пост 14.8K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

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

Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

Я, конечно, тут недавно, не знаю какие в сообществе порядки.

Но выкладывать кастрированный пост исключительно в целях рекламы своего YouTube-канала как-то некрасиво

Хорошее видео, спасибо. А будет разбор того, как делать сводные из нескольких таблиц?

Читать ещё на Пикабу

Вопрос к пикабушникам: хочу сделать бесплатное обучение массажу онлайн⁠ ⁠

Идея пришла уже около года назад, но решиться как-то это оформить в мысль решился вот только сейчас, сильно не пинайте.

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

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

Массажной деятельностью как таковой я начал заниматься в русле изучения и преподавания йоги года с 2003-2004, как к этому пришел — история отдельная, но потом оно как-то закрутилось, поехал в Индию в 2006, в силу специфики деятельности с 2012 года работа с испанскими отельными сетями, в Доминикане настраивал работу СПА-подразделений отеля Barcelo, потом в 2015 получил медицинское образование в Испании, работал (хотя скорее это было такой углубленной оплачиваемой стажировкой в Сообществе реабилитации и физической медицины Испании) там же со спортсменами по направлению мануальной ортопедической терапии. Но все время эта деятельность была любимым хобби, так как на постоянку это очень большие трудозатраты, не всегда эквивалентные получаемому доходу.

Ну, в общем, как все 2020 году накрылось, так эта деятельность вышла на первый план, ибо работа с клиентом/пациентом 1 на 1 возможна даже в период запрета на групповые занятия, дает живые, хотя и трудные, деньги на то, чтобы жить и содержать семью, ну и момент общения с пациентами, когда человек благодарен за результат и внимательное отношение, это совсем иное, чем работа с крупными компаниями и проектами, где, как ни крути, но соковыжималка именно психологическая.

Пошло поехало оно бодро, пошли люди, наладились мосты с испанцами по поводу открытия курсов по спортивной массажной терапии в России (таким, насколько я знаю, может похвастаться только Академия Физической Реабилитации и это был бы очень крутой проект), даже были проведены 52-часовые обучающие курсы, где прошедшие их люди с нуля научились делать массаж в неклассической (для наших краев) методике на весьма хорошем уровне — т.е. массаж после этого стал получаться сразу на вполне профессиональном уровне, одна девушка даже поехала после этого работать массажистом на круизном лайнере на протяжении нескольких месяцев и справилась, хотя и было нелегко, как рассказывает.

Но потом всем известные события февраля 2022 года, и снова здравствуйте. С испанцами все закрылось (а предполагалось, что они смогут инвестировать в этот проект необходимую сумму для запуска и развития), клиентов резко стало меньше. Окей, мы не унываем, пошли работать в открывающуюся клинику. «Холодные» клиенты, рекламные акции, небольшая на первых порах оплата, но дело идет, клиенты идут и они довольны. Но идут они не настолько, чтобы прокормить не только работающих врачей, но и весь остальной персонал. Три месяца напряженной (но, кстати, интересной, этого не отнять) работы и клинику сначала закрывают, потом продают. Ну вот те здрасте.

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

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

Ну, в общем, вот) Общий контекст широкими мазками.

Теперь к сути вопроса.

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

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

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

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

Монетизировать это не планирую (оно пусть будет или дорого или совсем бесплатно, так что быть именно второму варианту). Если появится пару клиентов ко мне на мануалку в Питере, отлично, wellcome, но это не цель.

Формат обучения, ну наверное, чат в Телеграмм. Я еще не вдавался как это делается, неясно, будет ли это вообще интересно, чистый эксперимент. Раз в неделю будет какой-то урок по видео, в течении недели сопровождение по нему в текстовом виде, ответы на вопросы.

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

Ну что, кому интересно и актуально? Что скажете?

Стартап с нуля — это очень просто. ⁠ ⁠

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

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

Мне пришла в голову замечательная идея:»Почему бы не сделать сайт». Ведь я, как программист с опытом, знаю как должно быть, как мама школьника — знаю, что надо, а сын подскажет как удобно!

В целом идея казалась жизнеспособной.

Первый вариант сайта был с один только тренажеров для развития устного счета. Можно было настраивать различные действия и считать на время. Сыну понравилось!

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

Так я и нашла себе новое хобби. За все время работы над проектом я добавила туда множество тренажеров на разные темы: развитие логики, внимания, памяти, тренажеры по математике.

Одним из последних был добавлен тренажер для решения дробей. В четвертом классе уже их изучают).

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

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

Сейчас могу сказать, что идея не самая простая, но реалистична.

Всем, кто заинтересовался жду в гости Mathsimple.ru

Начало работы с Excel⁠ ⁠

Наконец я нашел в себе силы и решил опубликовать первый пост. Все кто успешно использует Excel могут пропустить его, ибо он первый, и ориентирован на тех кто с Excel (Эксэ́ль) вообще не знаком и предпочитает писать на листочках и считать на калькуляторе. Разберу пример, который встретил на одном производстве.

Имеем небольшой склад где хранится все от батона до картона.

Здесь мы видим что 01.10.2010 на складе было некое перемещение материалов, которые кладовщик добросовестно отразил в отчете. Однако искушенный пользователь заметит некоторые ошибки введении отчетной деятельности. А именно:

— не все ячейки заполнены

-единицы измерения указаны в каждом случае

-материалы разных категорий находятся в одной группе

-единицы измерения разнятся

-неверно написана дата составления отчета

Другие ошибки не столько критичны, на них мы не будем заострять внимание.

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

Эти небольшие изменения уже экономят кучу времени на написании единиц измерения в каждой ячейки. Кроме этого, эти изменения позволяют нам применить функции Excel для автоматического расчета остатка. Для этого нужно выбрать первую ячейку столбца «!Конечный остаток» (I4) и перейти на строку формул.Нажав на строку мы можем вводить данные в ячейку.

Но сейчас мы будем вводить не данные а формулу, которая сама будет рассчитывать конечный остаток, и нам не придется каждый раз считать и записывать его вручную. В данном случае мы обойдемся простой арифметикой, не используя функции. Все формулы в Excel начинаются с =, после того как вы нажали = выберите первую ячейку столбца «Начальный остаток» и адрес этой ячейки автоматически добавится в формулу, далее нажимаете + чтобы прибавить приход, и нажимаете на первую ячейку столбца «Приход». Следующем шагом будет вычитание расхода, для этого нажмите — и выберите первую ячейку столбца «Расход». В итоге у вас должна получится вот такая формула

Если вы сделали все верно то нажав Enter вы получите конечный остаток, как видите ошибка допущенная при ручном расчете исключается, при использование формулы.

Теперь осталось перенести эту формулу на все ячейки столбца, для этого нужно установить курсор на правый нижний угол ячейки с формулой до появления черного крестика. Нажав на него перетягиваем

В итоге мы получили таблицу в которой нужно заполнить только приход и расход.

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

EXCEL — ЭТИ СТРАШНЫЕ МАКРОСЫ – НАЧАЛО⁠ ⁠

Я решил с двух ног ворваться в тему макросов.

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

Первым делом нужно включить вкладку «Разработчик». По умолчанию в Excel ее спрятали, чтобы не взорвать мозг юзерам. Идем в Параметры -> Настройка ленты -> Основные вкладки -> Разработчик (поставить галочку).

Теперь идем в эту вкладку, нажимаем «Записать макрос» выбираем имя жмакаем «ок». Все, теперь любые действия в Excel надежным образом записываются.

Давайте теперь что-то сделаем. На пример поменяем заливку ячейки А1, в ячейку A2 напишем значение «Мама, я программист», а в ячейке А3 пропишем формулу текущей даты «=Сегодня()»

Останавливаем запись макроса. Нажимаем иконку «Макросы», выбираем наш макрос как мы его обозвали, нажимаем кнопку «изменить».

Появляется окно Microsoft Visual Basic for Applications. Кстати оно также вызывается комбинацией клавиш (Alt + F11) У меня почему-то вызывается только левым Altом, а правым нет, видимо намекая на то что для написания макросов лучше иметь 2 руки (хотя я и одной нажать могу). Появился редактор языка VBA – это язык, который написан специально под офис чтобы на нем писать макросы. В основном окне видим саму эту запись, которую автоматически сделал Excel.

Sub Макрос2()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range(«A2»).Select
ActiveCell.FormulaR1C1 = «Мама, я программист»
Range(«A3»).Select
ActiveCell.FormulaR1C1 = «=TODAY()»
Range(«A4»).Select
End Sub

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

Теперь давайте разбираться что делает этот макрос

Sub Макрос2()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With

(Весь этот кусок от начала говорит нам о том, что с тем элементом что был выделен ранее происходит некоторое дерьмо, в том числе изменение цвета. Вот там, где Color = 255. Все остальное это параметры заливки, которые по итогу не менялись, но макрорекордер решил их тоже записать, на всякий. Это связано с внутренними особенностями работы excel как я понял. Вообще привыкайте к тому что макрорекордер пишет много того что потом вообще можно удалить. Конструкция With – End With позволяет делать несколько действий с одним объектом, на пример выше берется объект Selection.Interior, то есть фон выбранной области и ряду параметров этой заливки назначаются конкретные значения. То есть With нужен для облегчения записи кода, чтобы Selection.Interior не писать вначале каждой строчки.

Range(«A2»).Select –выделяем ячейку «A2»
ActiveCell.FormulaR1C1 = «Мама, я программист» – пишем в ячейку значение
Range(«A3»).Select – выделяем ячейку «А3»
ActiveCell.FormulaR1C1 = «=TODAY()» –пишем в ячейку формулу
Range(«A4»).Select – зачем то выделяем ячейку А4.
End Sub

Теперь при создании нового листа и запуске этого макроса он будет делать все то же самое.

Тут стоит понимать, что половину того что записал макрос можно опустить, так как нам важен результат, а не путь по которому к этому результату пришли, а макрорекордер записывает именно путь. На пример вместо всей конструкции With можно записать

так как мы точно знаем, что хотим поменять только цвет в ячейке А1. Также вместо того чтобы выделять ячейку, а потом в активной ячейке писать формулу можно сразу записать нужное значение в ячейку

Range(“A2”).Value = ”Мама, я программист”

или писать формулу как в третей ячейке

С формулами и значениями лично мне не понятно, как excel их интерпретирует, но в макрорекордре он записывает любой ввод в ячейку как ввод формулы. Благо лично у меня при написании макросов не возникает необходимости писать формулы в ячейки. На пример вместо вставки формулы как это было выше можно написать Range(“A3”).Value = Date(), тогда макрос вставит сразу текущую дату в ячейку как значение.

Опытные макроделы пишут макросы сразу без их записи макрорекордером, но это полезный инструмент для самостоятельного изучения при написании макросов: если не знаешь, что как делается в VBА то запускаешь и делаешь, потом смотришь что он там написал.

Теперь давайте напишем какой ни будь полезный макрос, я буду писать строчки и как можно подробнее их комментировать. Комментарии от программы отделяются символом «‘», он ставится вначале комментария. Прошу учесть, что я самоучка и многого не знаю, и просто напишу так ка делаю это сам, возможно есть более изящные решения. Напоминаю, что это просто ознакомительный опус для тех, кто не в зуб ногой.

Sub Colorization()
‘начало нашего макроса и его название
Dim x As Integer
‘объявляем переменную х типа интеджер, это тип для целых чисел от -32 768 до 32 767 (2 байта),
‘она нам нужна для перебора ячеек
For x = 1 To ActiveSheet.UsedRange.Rows.Count
‘перебираем х от 1 до конца использованной части листа, то есть не весь лист, а там где есть данные.
‘Тут цикл For повторяется от этой строки до строки Next x, которая прописана ниже
If Cells(x, 1).Value = «красный» Then Cells(x, 1).Interior.Color = RGB(255, 0, 0)
‘если значение в ячейке равно «красный» то закрашиваем ячейку в красный цвет. Функция If выполняет часть
‘после Then если условие между If и Then верно. Так как у нас необходимое действие занимает одну
‘строку можно писать в таком виде, если же действий несколько применяется конструкция:
‘If … Then
‘…
‘…
‘End If
If Cells(x, 1).Value = «зеленый» Then Cells(x, 1).Interior.Color = RGB(0, 255, 0)
‘как выше только в зеленый цвет
If Cells(x, 1).Value = «синий» Then Cells(x, 1).Interior.Color = RGB(0, 0, 255)
‘в синий цвет
Next x ‘берем следующее значение х, конец цикла For, который мы начали выше
End Sub ‘конец макроса
Как работает этот макрос: берет первый столбец, сначала 1 ячейку, смотрит что в ней написано, и если это равно «красный», «зеленый» или «синий», то красит фон ячейки в этот цвет, если нет по пропускает. Потом берет вторую и т. д. до конца активной части текущего листа.
Для проверки работы макроса нам нужен лист, где в первом столбце будут случайным образом прописаны цвета «красный», «зеленый», «синий». Запускаем макрос – когда он отработает ячейки будут раскрашены:

Некоторые пояснения: если не писать просто Cells то макрос будет делать все в активном листе активного окна. Но макрос может идти и в другие листы, файлы, даже в другие приложения офиса, но об этом не сегодня.

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

Итак, на этом пока все. Надеюсь теперь те, кто никогда не видел макросов получат о них начальное представление. Дальше буду писать про более практичное применение.