Как делать формулы в эксель: Занятие 4 Использование простых формул и функций

Содержание

Как создавать формулы в Excel

Excel — это мощный инструмент управления данными, который может выполнять множество сложных расчетов и анализов. Одной из самых полезных функций Excel является возможность создания формул. Формулы — это уравнения, которые позволяют пользователям выполнять вычисления с данными, введенными в ячейки. В этой статье мы рассмотрим все основы формул Excel, от понимания концепции формул до освоения передовых методов проверки и отладки формул.

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

Важно отметить, что формулы Excel могут также включать ссылки на ячейки, что позволяет выполнять вычисления с использованием данных из других ячеек. Например, если вы хотите сложить значения в ячейках A1 и A2, вы можете использовать формулу =A1+A2. Это упрощает выполнение сложных расчетов и их автоматическое обновление по мере изменения данных в электронной таблице.

Основные функции формул Excel для начинающих

Excel имеет множество встроенных функций, облегчающих выполнение вычислений. К наиболее часто используемым функциям относятся: СУММ, СРЗНАЧ, СЧЁТ, МАКС, МИН, ОКРУГЛ и ЕСЛИ. Функция СУММ суммирует диапазон ячеек, а функция СРЗНАЧ вычисляет среднее значение диапазона. Функция COUNT дает количество ячеек в диапазоне, содержащих числовые значения, а функции MAX и MIN возвращают наибольшее и минимальное значения в диапазоне. Функция ОКРУГЛ округляет число до указанного количества знаков после запятой. Функция ЕСЛИ позволяет выполнять различные вычисления в зависимости от того, является ли условие истинным или ложным.

Важно отметить, что Excel также имеет более сложные функции формул, которые можно использовать для более сложных вычислений. К ним относятся такие функции, как ВПР, ГПР, ИНДЕКС, ПОИСКПОЗ и СУММЕСЛИ. Функция ВПР позволяет искать определенное значение в таблице и возвращать соответствующее значение в той же строке. Функция HLOOKUP работает аналогичным образом, но ищет значения в строке, а не в столбце. Функция ИНДЕКС возвращает значение ячейки в указанной строке и столбце, а функция ПОИСКПОЗ ищет указанное значение в диапазоне и возвращает его позицию. Функция СУММЕСЛИ суммирует значения в диапазоне, соответствующем заданным критериям. Изучение этих расширенных функций может значительно повысить вашу способность работать с Excel и выполнять более сложные вычисления.

Как вводить формулы в Excel

Вводить формулы в Excel очень просто. Щелкните ячейку, в которой должна появиться формула, затем введите знак равенства (=), а затем формулу. Например, чтобы сложить значения в ячейках A1 и A2, введите в ячейку «=A1+A2». Вы также можете использовать ссылки на ячейки в формулах. Ссылка на ячейку — это буква столбца и номер строки ячейки, например A1, B2, C3 и т. д. Ссылки на ячейки можно использовать в формулах для выполнения вычислений на основе значений в других ячейках. Например, чтобы умножить значение в ячейке A1 на значение в ячейке B1, введите в ячейку «=A1*B1».

Excel также имеет множество встроенных функций, которые можно использовать в формулах. Эти функции могут выполнять сложные вычисления и экономить ваше время. Чтобы использовать функцию, введите имя функции, за которым следует открывающая скобка, затем введите аргументы функции, разделенные запятыми. Например, чтобы найти среднее значение в ячейках с A1 по A5, введите в ячейку «=СРЗНАЧ(A1:A5)». Некоторые часто используемые функции включают SUM, AVERAGE, MAX, MIN и COUNT.

Распространенные ошибки, которых следует избегать при создании формул в Excel

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

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

Как использовать ссылки на ячейки в формулах Excel

Ссылки на ячейки — это мощный инструмент в формулах Excel. Использование ссылки на ячейку позволяет написать формулу один раз и применить ее к нескольким ячейкам, что делает ее более эффективной и менее подверженной ошибкам. Чтобы использовать ссылку на ячейку в формуле, просто введите букву столбца, а затем номер строки ячейки. Например, чтобы использовать значение в ячейке B2 в формуле, введите в формулу «B2». Вы также можете использовать относительные и абсолютные ссылки на ячейки в формулах. Относительная ссылка изменяется в зависимости от местоположения формулы, а абсолютная ссылка остается неизменной независимо от местоположения формулы. Чтобы сделать ссылку на ячейку абсолютной, добавьте знак доллара ($) перед буквой столбца и/или номером строки.

Важно отметить, что при копировании и вставке формул со ссылками на ячейки Excel автоматически корректирует ссылки на ячейки в зависимости от нового местоположения формулы. Это может быть полезно, но также может привести к ошибкам, если вы не будете осторожны. Чтобы избежать этого, вы можете использовать абсолютные ссылки или использовать функцию «специальной вставки», чтобы вставлять только значения, а не формулы. Кроме того, вы можете использовать именованные диапазоны, чтобы сделать ваши формулы более читабельными и понятными. Просто выберите ячейки, которые вы хотите назвать, перейдите на вкладку «Формулы» и нажмите «Определить имя». Затем вы можете использовать это имя в своих формулах вместо ссылки на ячейку.

Советы по написанию сложных формул Excel

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

Как использовать функции в формулах Excel

Excel имеет множество встроенных функций, облегчающих выполнение сложных вычислений. Вы можете использовать такие функции, как ЕСЛИ, СУММЕСЛИ, СЧЁТЕСЛИ, ВПР и ИНДЕКС, чтобы найти конкретные значения или выполнить вычисления на основе условия. Каждая функция имеет свой собственный набор аргументов или входных данных, которые определяют, как работает функция. Аргументы могут включать ссылки на ячейки, значения или другие функции. Чтобы использовать функцию, введите имя функции, а затем ее аргументы в формуле.

Овладение искусством условного форматирования в Excel

Условное форматирование — это мощный инструмент в Excel, позволяющий визуально форматировать ячейки на основе их содержимого. Условное форматирование можно использовать для выделения ячеек, содержащих определенные значения, для форматирования ячеек на основе их диапазонов значений или для отображения гистограмм для указания относительного размера значений. Чтобы применить условное форматирование, выберите ячейки, к которым вы хотите его применить, затем нажмите кнопку «Условное форматирование» на вкладке «Главная» на ленте. Оттуда вы можете выбрать один из множества вариантов форматирования.

Расширенные методы аудита формул в Excel

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

Как отлаживать и устранять ошибки в формулах Excel

Даже самые опытные пользователи Excel могут столкнуться с ошибками при создании формул. Некоторые распространенные ошибки включают #DIV/0!, #N/A!, #VALUE!, #REF!, #NAME?, #NUM! и #NULL!. Каждое сообщение об ошибке указывает на конкретную проблему с формулой, такую ​​как деление на ноль или ссылка на недопустимую ячейку. Чтобы устранить ошибки, используйте инструменты проверки ошибок Excel или функции аудита формул, чтобы найти проблему.

Сочетания клавиш для экономии времени при работе с формулами в Excel

В Excel имеется множество сочетаний клавиш, облегчающих работу с формулами. Например, вы можете использовать клавишу F2 для редактирования ячейки, клавишу Ctrl+Z для отмены изменений или комбинацию клавиш Ctrl+Enter для одновременного применения формулы к нескольким ячейкам. Вы также можете использовать клавиши Ctrl+Shift+стрелки для выбора больших диапазонов ячеек или клавиши Alt+Equals для автоматического добавления диапазона ячеек.

Как создавать пользовательские функции в Excel для повышения эффективности

Если вам приходится снова и снова выполнять одни и те же вычисления, вы можете создать свои собственные пользовательские функции в Excel. Чтобы создать пользовательскую функцию, используйте Visual Basic для приложений (VBA) для написания и сохранения кода, а затем используйте функцию в своих формулах. Это может сэкономить время и повысить эффективность вашей работы.

Использование именованных диапазонов для упрощения формул Excel

Именованные диапазоны — это способ присвоения описательного имени ячейке или диапазону ячеек. Вы можете использовать именованные диапазоны в формулах вместо ссылок на ячейки, что упрощает чтение и понимание формулы. Чтобы создать именованный диапазон, выберите ячейки, которым вы хотите присвоить имя, затем щелкните «Поле имени» на вкладке «Формулы» на ленте. Оттуда вы можете присвоить имя диапазону.

Рекомендации по документированию формул Excel

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

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

Как создавать макросы Excel и автоматизировать работу с электронными таблицами

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

Макросы Excel: советы по началу работы

Мы покажем вам, как написать свой первый макрос. Как только вы увидите, как легко автоматизировать задачи с помощью макросов, вы никогда не вернетесь назад.

Сначала несколько советов о том, как подготовить данные для макросов:

  • Всегда начинайте макрос с исходной позиции (используйте комбинацию клавиш  Ctrl+ Home , чтобы быстро добраться туда).
  • Используйте клавиши со стрелками для навигации: Вверх, Вниз, Вправо, Влево, Конец, Домой и т. д., а также клавиши быстрого доступа для ускорения перемещения.
  • Держите свои макросы небольшими и сосредоточенными на конкретных задачах. Это лучше всего подходит для тестирования и редактирования (при необходимости). Вы всегда можете объединить эти мини-макросы в один БОЛЬШОЙ макрос позже, когда они будут усовершенствованы.
  • Макросы требуют «относительных» адресов ячеек, что означает, что вы «указываете» на ячейки, а не жестко задаете фактический (или «абсолютный») адрес ячейки (например, A1, B19, C20 и т. д.) в макросе. Электронные таблицы являются динамическими, что означает, что они постоянно меняются, что означает изменение адресов ячеек.
  • Фиксированные значения и статическая информация, такая как имена, адреса, идентификационные номера и т. д., обычно вводятся заранее и не являются частью вашего макроса. Поскольку эти данные редко изменяются (а если и изменяются, то просто для добавления или удаления новой записи), включить эту функцию в макрос практически невозможно.
  • Сначала управляйте своими данными: добавляйте, редактируйте или удаляйте записи, а затем вводите обновленные значения. Затем вы можете выполнить свой макрос.

Почему проще начинать с мини-макросов

В этом примере у нас есть владелец магазина, который расширил свою территорию с одного магазина до дюжины в 12 разных крупных городах. Теперь она генеральный директор, она годами вела свои собственные книги, что было непростой задачей для одного магазина, а теперь у нее их 12. Она должна собирать данные из каждого магазина и объединять их, чтобы отслеживать состояние всей своей компании.

Мы создали несколько мини-макросов для выполнения следующих задач:

  1. Соберите и объедините данные из ее 12 магазинов в одну рабочую книгу в трехмерной электронной таблице Master.
  2. Организуйте и отсортируйте данные.
  3. Введите формулы для расчета объединенных данных.

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

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

загрузить

Образец рабочей тетради Excel Macros

Этот образец рабочей тетради Excel поможет вам попрактиковаться в создании и использовании макросов. JD Sartain

Подготовка: мастер-таблица

Если вы создаете свои электронные таблицы с нуля, начните с мастер-таблицы. Введите формулу даты в A1 и местоположение магазина в B1. См. снимок экрана ниже.

Введите эту формулу даты в ячейку A1: = Сегодня() . Теперь в этой ячейке всегда отображается сегодняшняя дата. Быть уверенным; однако, что местонахождение вашего магазина (название филиала и номер) введены в B1.

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

3. Затем введите имена полей (и/или любую другую информацию, относящуюся к полям) в строке 3 (например, от A3 до J3 или столько полей, сколько требуется для вашей электронной таблицы).

Совет: Если данные длинные, информацию в отдельных ячейках можно обтекать текстом. Например, вы можете поместить всю контактную информацию магазина в одну ячейку и перенести строки. Нажмите Alt+ Введите , чтобы вставить дополнительные строки в ячейки.

4. Затем введите статические данные в столбец A. Это запись информации в вашей электронной таблице, которая редко меняется. Если в вашем бизнесе используются номера продуктов или идентификационные коды, которые уникальны, поскольку для каждого продукта существует только один код, введите их в столбец A, начиная со строки 4 (не переходите к строке 5). Другие статические поля данных могут включать описание продукта, цену продукта, процент налога с продаж и т. д.

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

  • Столбец A является основным навигационным столбцом. Макрос перемещается и перемещается по электронной таблице на основе исходной позиции (A1) и столбца A. Макрос завершится ошибкой, если вы проигнорируете это правило, поскольку пустые строки нарушают действие клавиш направления.
  • Если вы решите позже создать несколько/реляционных таблиц для сводных отчетов, у вас должно быть уникальное ключевое поле для соединения связанных таблиц. Ознакомьтесь с нашим руководством по сводным таблицам Excel для получения дополнительной информации.

JD Sartain / IDG Worldwide

Сначала создайте основную электронную таблицу.

5. Обычно описание продукта находится в столбце B, количество проданных товаров — в столбце C, цена продукта — в столбце D, дополнительные расходы — в E, скидки — в F, налог с продаж — в G, а итоговые суммы — в H. Итоговые значения столбцов находятся вверху строки 2, помните? Отформатируйте ширину столбцов на основе длины имен полей и отрегулируйте высоту строки до 20 во всех строках. Измените выравнивание сверху/снизу на Center, выберите предпочитаемое выравнивание (слева, справа, по центру), а затем отформатируйте «стили» электронной таблицы по своему усмотрению.

6. После настройки базы данных master ничего не перемещайте. Если вам нужно добавить поля, используйте команду Вставить столбец . Например, если вы хотите добавить второй налог с продаж, поместите курсор в любом месте столбца H (Итого) и щелкните вкладку: Главная > Вставить > Вставить столбцы листа . Новый столбец становится новым столбцом H, а столбец Totals перемещается в I. Этот процесс не влияет на макрос.

7. Тот же процесс применяется к строкам. Обычно я бы предостерег вас вставлять строки «внутри» активной области базы данных. Например, если в формуле указано =СУММ(B3:B20) и вы вставляете или используете строку за пределами диапазона формулы, например B21, данные новой записи не включаются в формулу и, следовательно, не вычисляются.

8. Теперь мы настроим этот диапазон формул. Введите следующие формулы в строке 2 (это одноразовая задача):

C2: =СУММ(C4:C500)

E2: =СУММ(E4:E500)

F2: =СУММ(F 4:F500)

G2: =СУММ(G4:G500)

h3: =СУММ(h5:H500)

Затем введите в эти столбцы следующие формулы (тоже разовое событие):

E4: =SUM(C4*D4) , затем скопируйте из E4 вниз в E5:E500

F4: =СУММ(E4*10%) , текущий процент скидки в вашем магазине, затем скопируйте с F4 вниз на F5:E500

G4: =СУММ(E4-F4)*6,25 , где 6,25 – налог с продаж в вашем регионе, затем скопируйте из G 4 вниз к G5:G500

h5: =SUM(E4-F4+G4) , затем скопируйте из h5 вниз в H5:E500

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

JD Sartain / IDG по всему миру

Введите формулы для расчета столбцов и строк.

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

10. После определения и настройки электронной таблицы со структурой, размещением статических данных и правильными формулами сделайте 12 копий на листах со 2 по 13. Отредактируйте вкладки внизу, чтобы указать отдельные магазины. Измените имя вкладки sheet1 на Master, потому что это ваш основной файл базы данных.

11. Измените данные о местоположении в строке 1, чтобы определить информацию о магазине (которая соответствует магазину на вкладке) во всех 12 электронных таблицах. Затем отправьте электронную копию электронной таблицы каждого филиала каждому менеджеру магазина; например, отправьте ведомость из Бостона в Бостон, ведомость из Далласа — в Даллас и т. д.

Их копии включают формулы электронных таблиц, которые работают в их отдельных электронных таблицах (но не формулы объединенных электронных таблиц в рабочей книге).

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

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

JD Sartain / IDG по всему миру

Скопируйте основную таблицу 12 раз, затем назовите вкладки.

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

Программирование макросов

Макрос1: сбор и объединение данных

1. Откройте папку базы данных и откройте электронную таблицу под названием MasterDB.xlsx

2. Откройте одну из новых электронных таблиц магазина, например, с названием BostonDB.xlsx

3. Переместите курсор обратно на MasterDB, чтобы она стала активной.

4. Выберите вкладку Разработчик и щелкните Запись макроса или нажмите ALT+L+R . В поле «Имя макроса» написано Macro1, и это хорошее имя.

5. Введите комбинацию клавиш (если хотите) в поле Shortcut_key (введите букву M) (вы можете создать кнопку в меню ленты позже).

JD Sartain / IDG по всему миру

Диалоговое окно записи макроса, клавиша быстрого доступа к макросу

6. В поле Store Macro In щелкните стрелку вниз и выберите Personal Macro Workbook из списка, затем щелкните OK .

Теперь вы записываете макрос.

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

1. Вернитесь к электронной таблице BostonDB, затем щелкните правой кнопкой мыши вкладку Boston . Во всплывающем меню выберите Переместить или скопировать …

2. В диалоговом окне Переместить или скопировать установите флажок Копировать .

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

4. Выберите из списка « MasterDB.xlsx ».

5. Во втором диалоге: Перед Листом выберите первую электронную таблицу в списке под названием « Master », затем нажмите OK .

8. Excel скопирует лист и переместит курсор в MasterDB. Обратите внимание на новую вкладку с надписью «Бостон2». Убедитесь, что информация в ячейке A1 показывает номер магазина, за которым следует недавняя дата (в данном примере 29.09.18). Если да, вы можете идти.

9. Щелкните правой кнопкой мыши вкладку исходной электронной таблицы Boston и выберите Удалить во всплывающем меню.

10. Excel предупреждает в диалоговом окне: Вы не можете отменить . . . Удалить или отменить? Если вы уверены, что хотите удалить его, нажмите Удалить . Почему? Потому что вы хотите заменить его на НОВЫЙ бостонский лист, который вам прислал бостонский менеджер.

11. Переместите вкладку Boston2 между вкладками Master и Chicago. Если вы сохраните «2» от Boston2, будет легче быстро определить, какие листы обновлялись каждый месяц.

13. Нажмите Ctrl+ Home , чтобы переместить курсор в ячейку A1 и повторно ввести эту формулу даты: =СЕГОДНЯ() , (если эта формула отсутствует), нажмите клавишу Enter

JD Sartain / IDG Worldwide

Записать макрос, объединить данные, удалить повторяющиеся листы

Выполнение макроса

1. Снова выберите вкладку «Разработчик» и нажмите Остановить запись или нажмите ALT+ T+ M+ R .

2. Сохраните главный файл, затем сохраните файл BostonDB.

3. Вернитесь к электронной таблице MasterDB и запустите макрос: Нажмите Ctrl+ M .

ПРИМЕЧАНИЕ: Помните, что знак «плюс» означает «одновременное» нажатие комбинации клавиш; то есть Ctrl+ Shift+ J означает: Нажмите и удерживайте клавиши Ctrl и Shift левой рукой, затем нажмите клавишу J правой рукой, затем отпустите все три клавиши одновременно. Тире (или дефис) означает последовательное нажатие комбинации клавиш, например End-Down, что означает нажатие клавиши End и отпускание, затем нажатие стрелки вниз и отпускание. Они НЕ взаимозаменяемы, поэтому следите за знаками.

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

ПРИМЕЧАНИЕ. Доступны только сочетания клавиш: Ctrl+ M (которое вы уже использовали), Ctrl+ Shift+ M , Ctrl+ J и Ctrl+ Shift+ J . Поскольку сочетания клавиш в дефиците, а комбинации символов в любом случае не имеют никакого логического смысла, лучшим решением для ваших мини-макросов являются кнопки макросов в меню ленты с понятными именами, например, Boston для макроса Boston и Dallas для макроса Dallas. Ознакомьтесь с другими инструкциями по работе с макросами Excel, где есть раздел с подробными инструкциями о том, как создавать, называть и использовать макросы.

Макрос2: Организация и сортировка данных

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

Следуйте инструкциям по записи макроса (4, 5, 6 в разделе «Макрос1» выше), чтобы создать следующий макрос. Назовите макрос Macro2 и используйте Ctrl+ Shift-M для ярлыка (позже вы можете создать кнопку в меню ленты). Этот макрос влияет на все электронные таблицы в MasterDB, поэтому убедитесь, что этот файл открыт и активен.

1. Нажмите Ctrl+ Home [чтобы переместить курсор на A1].

2. Трижды нажмите клавишу со стрелкой вниз.

3. Нажмите Shift-End-Down-End-Right [Удерживая нажатой клавишу Shift, нажмите клавишу End и отпустите, нажмите стрелку вниз и отпустите, нажмите клавишу End и отпустите, нажмите стрелку вправо и отпустите].

4. Выберите Данные > Сортировка . В диалоговом окне Sort выберите Номер модели из раскрывающегося списка в Сортировать по , затем выберите Значения  в поле Сортировать по , а затем выберите A-Z  в поле Сортировать Заказ и нажмите OK .

JD Sartain / IDG по всему миру

Макрос сортирует электронную таблицу по номеру модели

5. Нажмите Ctrl+ Home .

6. Щелкните следующую вкладку внизу, чтобы получить доступ к следующей электронной таблице (например, Чикаго после Бостона), и повторите все шаги, описанные выше: 1–6, а затем продолжите выполнение следующих инструкций ниже. Помните, макрос записывает все эти шаги.

7. Перейдите на вкладку Основная электронная таблица, нажмите Ctrl+ Home .

8. Выберите вкладку «Разработчик» (в меню ленты) и нажмите Остановить запись или нажмите ALT+ T+ M+ R .

9. Сохраните главный файл, MasterDB.

10. Когда курсор все еще находится в электронной таблице MasterDB, запустите макрос: Нажмите Ctrl+ Shift+ M .

Макрос3: введите формулы

Формулы для электронных таблиц отдельных магазинов уже установлены. Вы ввели их еще на шаге № 9раздела «Подготовка» выше. Эти формулы предназначены для основной электронной таблицы, которая вычисляет все остальные и объединяет общие итоги в одну «основную» таблицу. Мы используем макрос для этого процесса, а не делаем это вручную 12 раз.

Следуйте инструкциям по записи макроса (4, 5, 6 в разделе «Макрос1» выше), чтобы создать следующий макрос. Назовите макрос Macro3 и используйте Ctrl+ J для ярлыка (позже вы можете создать кнопку в меню ленты). Этот макрос влияет на все электронные таблицы в MasterDB, поэтому убедитесь, что этот файл открыт и активен.

1. Нажмите Ctrl+ Home [чтобы переместить курсор на A1].

2. Нажмите Вниз-Вправо-Вправо

3. =СУММ(Бостон:Денвер!C2) Введите [Введите эту формулу в ячейку C2, где вкладки с именами Бостон и Денвер представляют собой первую и последнюю вкладки электронной таблицы в вашей книге. Это, конечно, за исключением Мастера, потому что вы вычисляете все значения в ячейке C2 от первой вкладки Бостон до последней вкладки Денвер и вводите итоги в ячейку C2 Мастера. Затем нажимается клавиша Enter).

4. Стрелка вверх, Ctrl+ C [Перемещает курсор обратно в ячейку C2 и копирует эту формулу]

5. Вправо-Вправо-Shift- Вправо-Вправо-Вправо Введите [дважды перемещает курсор вправо и останавливается на ячейке E2, нажмите и удерживайте клавишу Shift, перемещаясь вправо три раза, что выделяет ячейки от E2 до h3, затем нажмите клавишу Enter].

JD Sartain / IDG по всему миру

Введите + рассчитать формулы в Master для нескольких листов

6. [Пока эти ячейки выделены, нажмите] Shift+ Ctrl+ 4

7. ALT+ T+ M+ R [Нажмите эти клавиши одновременно или выберите вкладку Разработчик и нажмите Остановить запись].

Сохранение, копирование и распространение

1. Ctrl+ Home

2. Сохраните мастер-файл, MasterDB.

3. Отправьте копии MasterDB всем менеджерам магазинов.

Отладка макросов

Отладка макросов в Excel на самом деле проще, чем вы думаете. Печать и чтение макросов поможет вам больше узнать о том, как работает макроязык Excel (Visual Basic). Вам не нужно быть программистом, чтобы отлаживать макросы, и чем больше вы знаете, тем легче это становится.

1. Сначала создайте простой макрос, чтобы вы могли видеть, как выглядят различные инструкции в Visual Basic.

2. Выберите вкладку «Разработчик», затем щелкните значок/кнопку «Запись макроса».

3. Введите нажатия клавиш, которые выполняют задачу, которую вы автоматизируете, затем щелкните значок/кнопку Macro Stop.

4. Чтобы просмотреть макрос, щелкните значок/кнопку «Макросы» или нажмите Alt+ F8 .

5. Появится диалоговое окно Macros , отображающее все макросы, сохраненные в области «Все открытые рабочие книги».

6. Найдите свой макрос, нажмите, чтобы выбрать его, затем нажмите кнопку «Вступить».

7. Код вашего макроса появляется в окне отладки макроса Visual Basic

Первая строка гласит: Sub macroname()

Sub означает подпрограмму, которая предупреждает Excel о том, что за ней следует макрос. «Sub» начинает макрос, а «End Sub» завершает его. Следующая строка показывает имя макроса (я назвал его xdebug().

Sub xdebug()

В следующем разделе говорится следующее:

Выберите диапазон C3. Затем введите формулу =SUM(RC[-1]*10%) в этой ActiveCell

Следующие команды аналогичны:

Выберите диапазон D3. Затем введите формулу =СУММ(RC[-2]*32%) в этой ActiveCell

Выберите диапазон E3. Затем введите формулу = СУММ (RC [-3]: RC [-1]) в этой ActiveCell

Далее говорится:

Выберите диапазон от C3 до E3 и скопируйте

Выберите диапазон от C4 до E12 и вставьте

Application.CutCopyMode = False [ это означает очистить буфер обмена]

Затем продолжается:

Выберите диапазон E4

Нажмите клавишу End, затем спуститесь на одну строку

Выберите диапазон E13. Затем введите формулу =СУММ(R[-10]C:R[-1]C) в этой ActiveCell

Следующие строки представляют собой команды выравнивания текста:

Select Range E13

With Selection (в следующем формате)

. HorizontalAlignment = xlGeneral

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

With Selection (формат следующим образом)

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

900 02 .WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

End Sub

JD Сартейн / IDG

Как отлаживать макрос

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

Чтобы внести изменения, просто введите их, затем нажмите Alt+Q , чтобы вернуться в электронную таблицу.

Читайте также: