Создание макроса в Microsoft Excel: 7 шагов

2024 Автор: John Day | [email protected]. Последнее изменение: 2024-01-30 11:51

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

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

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

Предупреждение перед началом:

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

Запасы

• Доступ к Microsoft Office и Excel
• Общее представление о том, как использовать Excel

Шаг 1: Шаг 1: скомпилируйте данные и начните запись макроса

Первое изображение:

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

Второе изображение:

Чтобы записать макрос, вам нужно будет активировать вкладку разработчика, для этого перейдите в меню «Файл»> «Параметры»> «Настроить ленту», затем выберите вкладку «Разработчик» и нажмите «ОК».

Третье изображение:

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

Шаг 2: Шаг 2: Создайте таблицу и шаблон для окончательного листа

Первое изображение:

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

Второе изображение:

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

Шаг 3: Шаг 3: Постройте первую контрольную фигуру

Этот шаг довольно прост; однако ошибка здесь может привести к тому, что контрольная цифра не будет работать в окончательном макросе. В поле рядом с ячейкой «Количество клиентов» создайте формулу «СЧЁТ», чтобы суммировать количество ячеек в одной строке таблицы. Я решил подсчитать ячейки, которые выделил желтым. Также приятно отметить, что моя полная формула также находится вверху изображения.

Шаг 4: Шаг 4: Создайте вторую контрольную фигуру

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

Первое изображение:

Перейдите на вторую вкладку в книге (скопированная вкладка)

В столбце J создайте новую строку с заголовками «Оставшаяся сумма». В ячейке J2 (выделенная ячейка выше) введите формулу = G2-H2 и скопируйте ее в остальные ячейки. Вернитесь к исходной вкладке с рабочим шаблоном.

Второе изображение:

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

Шаг 5: Шаг 5: Невзысканные денежные средства клиентом

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

Первое изображение:

Для начала вернитесь к скопированной вкладке, показанной выше. Для новой таблицы нам понадобится информация только для клиентов с непогашенным остатком. Сначала в столбцах «L» и «M» назовите их «Имя» и «Фамилия» соответственно. Затем в ячейке L2 введите формулу = IF ($ J2> 0, A2,””). В этой формуле написано, что если ячейка J2 больше 0, тогда отображается значение в ячейке A2, если оно не больше 0, оставьте ячейку пустой.

Убедитесь, что при ссылке на ячейку J2 вы ставите знак доллара ($) перед J, это позволяет нам правильно копировать данные в другие ячейки. Знак $ сохраняет ссылку в столбце «J», но позволяет соответствующим образом изменять строки. Затем скопируйте формулу в остальные ячейки столбцов «L» и «M» (обведены выше).

Второе изображение:

Чтобы точно определить денежную стоимость, мы используем значение, аналогичное названному ранее. На этот раз создайте формулу, которая гласит: = ЕСЛИ (J2> 0, J2,””). Единственная разница в этой формуле состоит в том, что если значение больше нуля, нам нужны данные из «Осталось количество». Скопируйте эти данные в остальные ячейки.

Третье изображение:

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

Четвертое изображение:

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

Шаг 6: Шаг 6: Завершите шаблон

Чтобы сделать данные о несобранных денежными средствами клиентом более профессиональными, отформатируйте данные в виде таблицы. Как только это будет завершено, добавьте заголовок в таблицу и нажмите «Объединить и центрировать», чтобы сделать его единообразным для таблицы. Если это еще не сделано, убедитесь, что числа отформатированы как «Бухгалтерский учет», чтобы показать знаки доллара и суммы в процентах. На этом макрос готов, и конечный продукт должен выглядеть примерно так:

Шаг 7: Шаг 7: завершите макрос

Перейдите на вкладку «Разработчик» на ленте и нажмите кнопку «Остановить запись». На этом макрос готов и готов к использованию!

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