Как записать в эксель

6

Формулы Excel, необходимые в работе: разбираем с примерами

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

Коды ошибок при работе с формулами.

Отличие в версиях MS Excel.

Кому важно знать формулы Excel и где изучить основы

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

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

Элементы, из которых состоит формула в Excel

Основные виды

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

Простые

Позволяют совершить одно простое действие: сложить, вычесть, разделить или умножить. Самой простой является формула=СУММ.

=СУММ (A1; B1) — это сумма значений двух соседних ячеек.

=СУММ (С1; М1; Р1) — сумма конкретных ячеек.

=СУММ (В1: В10) — сумма значений в указанном диапазоне.

Сложные

Это многосоставные формулы для более продвинутых пользователей. В данную категорию входят ЕСЛИ, СУММЕСЛИ, СУММЕСЛИМН. О них подробно расскажем ниже.

Комбинированные

Эксель позволяет комбинировать несколько функций: сложение + умножение, сравнение + умножение. Это удобно, когда, например, нужно вычислить сумму двух чисел, и, если результат будет больше 100, его нужно умножить на 3, а если меньше — на 6.

Выглядит формула так ↓

=ЕСЛИ (СУММ (A1; B1)<100; СУММ (A1; B1)*3;(СУММ (A1; B1)*6))

Встроенные

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

кликните по нужной ячейке таблицы;

нажмите одновременно Shift + F3;

выберите из предложенного перечня нужную формулу;

в окошко «Аргументы функций» внесите свои данные.

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

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

Поиск перечня доступных функций

Перейдите в закладку «Формулы» / «Вставить функцию». Или сразу нажмите на кнопочку «Fx».

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

Выберите любую формулу и прочитайте ее описание. А если хотите изучить ее более детально, нажмите на «Справку» ниже.

Вставка функции в таблицу

Вы можете сами писать функции в Excel вручную после «=», или использовать меню, описанное выше. Например, выбрав СУММ, появится окошко, где нужно ввести аргументы (кликнуть по клеткам, значения которых собираетесь складывать):

После этого в таблице появится формула в стандартном виде. Ее можно редактировать при необходимости.

Использование математических операций

Начинайте с «=» в ячейке и применяйте для вычислений любые стандартные знаки «*», «/», «^» и т.д. Можно написать номер ячейки самостоятельно или кликнуть по ней левой кнопкой мышки. Например: =В2*М2. После нажатия Enter появится произведение двух ячеек.

Растягивание функций и обозначение константы

Введите функцию =В2*C2, получите результат, а затем зажмите правый нижний уголок ячейки и протащите вниз. Формула растянется на весь выбранный диапазон и автоматически посчитает значения для всех строк от B3*C3 до B13*C13.

Чтобы обозначить константу (зафиксировать конкретную ячейку/строку/столбец), нужно поставить «$» перед буквой и цифрой ячейки.

Например: =В2*$С$2. Когда вы растяните функцию, константа или $С$2 так и останется неизменяемой, а вот первый аргумент будет меняться.

$С$2 — не меняются столбец и строка.

B$2 — не меняется строка 2.

$B2 — константой остается только столбец В.

22 формулы в Эксель, которые облегчат жизнь

Собрали самые полезные формулы, которые наверняка пригодятся в работе.

=МАКС (число1; [число2];…)

Показывает наибольшее число в выбранном диапазоне или перечне ячейках.

=МИН (число1; [число2];…)

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

СРЗНАЧ

=СРЗНАЧ (число1; [число2];…)

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

=СУММ (число1; [число2];…)

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

=ЕСЛИ (лог_выражение; значение_если_истина; [значение_если_ложь])

Сложная формула, которая позволяет сравнивать данные.

=ЕСЛИ (В1>10;”больше 10″;»меньше или равно 10″)

В1 — ячейка с данными;

>10 — логическое выражение;

больше 10 — правда;

меньше или равно 10 — ложное значение (если его не указывать, появится слово ЛОЖЬ).

СУММЕСЛИ

=СУММЕСЛИ (диапазон; условие; [диапазон_суммирования]).

Формула суммирует числа только, если они отвечают критерию.

=СУММЕСЛИ (С2: С6;»>20″)

С2: С6 — диапазон ячеек;

>20 —значит, что числа меньше 20 не будут складываться.

СУММЕСЛИМН

=СУММЕСЛИМН (диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2];…)

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

=СУММЕСЛИМН (D2: D6; C2: C6;”сувениры”; B2: B6;”ООО ХУ»)

D2: D6 — диапазон, где суммируются числа;

C2: C6 — диапазон ячеек для категории; сувениры — обязательное условие 1, то есть числа другой категории не учитываются;

B2: B6 — дополнительный диапазон;

ООО XY — условие 2, то есть числа другой компании не учитываются.

Дополнительных диапазонов и условий может быть до 127 штук.

=СЧЁТ (значение1; [значение2];…)Формула считает количество выбранных ячеек с числами в заданном диапазоне. Ячейки с датами тоже учитываются.

=СЧЁТ (значение1; [значение2];…)

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

СЧЕТЕСЛИ и СЧЕТЕСЛИМН

=СЧЕТЕСЛИ (диапазон; критерий)

Функция определяет количество заполненных клеточек, которые подходят под конкретные условия в рамках указанного диапазона.

=СЧЁТЕСЛИМН (диапазон_условия1; условие1 [диапазон_условия2; условие2];…)

Эта формула позволяет использовать одновременно несколько критериев.

ЕСЛИОШИБКА

=ЕСЛИОШИБКА (значение; значение_если_ошибка)

Функция проверяет ошибочность значения или вычисления, а если ошибка отсутствует, возвращает его.

=ДНИ (конечная дата; начальная дата)

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

КОРРЕЛ

=КОРРЕЛ (диапазон1; диапазон2)

Определяет статистическую взаимосвязь между разными данными: курсами валют, расходами и прибылью и т.д. Мах значение — +1, min — −1.

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

Находит данные в таблице и диапазоне.

В1 — значение, которое ищем.

С1: Е26— диапазон, в котором ведется поиск.

2 — номер столбца для поиска.

ЛЕВСИМВ

=ЛЕВСИМВ (текст;[число_знаков])

Позволяет выделить нужное количество символов. Например, она поможет определить, поместится ли строка в лимитированное количество знаков или нет.

=ПСТР (текст; начальная_позиция; число_знаков)

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

ПРОПИСН

=ПРОПИСН (текст)

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

СТРОЧН

Функция, обратная предыдущей. Она делает все литеры строчными.

ПОИСКПОЗ

=ПОИСКПОЗ (искомое_значение; просматриваемый_массив; тип_сопоставления)

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

ДЛСТР

=ДЛСТР (текст)

Данная функция определяет длину заданной строки. Пример использования — определение оптимальной длины описания статьи.

СЦЕПИТЬ

=СЦЕПИТЬ (текст1; текст2; текст3)

Позволяет сделать несколько строчек из одной и записать до 255 элементов (8192 символа).

ПРОПНАЧ

=ПРОПНАЧ (текст)

Позволяет поменять местами прописные и строчные символы.

ПЕЧСИМВ

=ПЕЧСИМВ (текст)

Можно убрать все невидимые знаки из текста.

Использование операторов

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

умножение и деление;

сложение и вычитание.

Арифметические

Операторы сравнения

Оператор объединения текста

Операторы ссылок

Использование ссылок

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

Простые ссылки A1

Они используются чаще всего. Буква обозначает столбец, цифра — строку.

диапазон ячеек в столбце С с 1 по 23 строку — «С1: С23»;

диапазон ячеек в строке 6 с B до Е– «B6: Е6»;

все ячейки в строке 11 — «11:11»;

все ячейки в столбцах от А до М — «А: М».

Ссылки на другой лист

Если необходимы данные с других листов, используется формула: =СУММ (Лист2! A5: C5)

Выглядит это так:

Абсолютные и относительные ссылки

Относительные ссылки

Рассмотрим, как они работают на примере: Напишем формулу для расчета суммы первой колонки. =СУММ (B4: B9)

Нажимаем на Ctrl+C. Чтобы перенести формулу на соседнюю клетку, переходим туда и жмем на Ctrl+V. Или можно просто протянуть ячейку с формулой, как мы описывали выше.

Индекс таблицы изменится автоматически и новые формулы будут выглядеть так:

Абсолютные ссылки

Чтобы при переносе формул ссылки сохранялись неизменными, требуются абсолютные адреса. Их пишут в формате «$B$2».

Например, есть поставить знак доллара в предыдущую формулу, мы получим: =СУММ ($B$4:$B$9)

Как видите, никаких изменений не произошло.

Смешанные ссылки

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

$А1– сохраняются столбцы;

А$1 — сохраняются строки.

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

Трёхмерные ссылки

Это те, где указывается диапазон листов.

Формула выглядит примерно так: =СУММ (Лист1: Лист5! A6)

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

Ссылки формата R1C1

Номер здесь задается как по строкам, так и по столбцам.

R9C9 — абсолютная ссылка на клетку, которая расположена на девятой строке девятого столбца;

R[-2] — ссылка на строчку, расположенную выше на 2 строки;

R[-3]C — ссылка на клетку, которая расположена на 3 ячейки выше;

R[4]C[4] — ссылка на ячейку, которая распложена на 4 клетки правее и 4 строки ниже.

Использование имён

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

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

Как присвоить имя:

Выделите нужную ячейку/столбец.

Правой кнопкой мышки вызовите меню и перейдите в закладку «Присвоить имя».

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

Сохраните, нажав Ок.

Использование функций

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

Ручной ввод

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

Панель инструментов

Это более упрощенный способ. Достаточно перейти в закладку «Формулы», выбрать подходящую библиотеку — Логические, Финансовые, Текстовые и др. (в закладке «Последние» будут наиболее востребованные формулы). Остается только выбрать из перечня нужную функцию и расставить аргументы.

Мастер подстановки

Кликните по любой ячейке в таблице. Нажмите на иконку «Fx», после чего откроется «Вставка функций».

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

Вставка функции в формулу с помощью мастера

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

Вызовите окошко «Вставка функции», как описывалось выше.

В перечне доступных функций выберите «Если».

Теперь составим выражение, чтобы проверить, будет ли сумма трех ячеек больше 10. При этом Правда — «Больше 10», а Ложь — «Меньше 10».

=ЕСЛИ (СУММ (B3: D3)>10;”Больше 10″;»Меньше 10″)

Программа посчитала, что сумма ячеек меньше 10 и выдала нам результат:

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

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

Редактирование функций с помощью мастера

Чтобы отредактировать функцию, можно использовать два способа:

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

Специальный мастер. Нажмите на иконку «Fx» и в появившемся окошке измените нужные вам аргументы. И тут же, кстати, сможете узнать результат после редактирования.

Операции с формулами

С формулами можно совершать много операций — копировать, вставлять, перемещать. Как это делать правильно, расскажем ниже.

Копирование/вставка формулы

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

Отмена операций

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

Повторение действий

Если вы выполнили команду «Отменить», программа сразу активизирует функцию «Вернуть» (возле стрелочки отмены на панели). То есть нажав на нее, вы повторите только что отмененную вами операцию.

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

Выделенные ячейки переносятся с помощью указателя мышки в другое место листа. Делается это так:

Выделите фрагмент ячеек, которые нужно переместить.

Поместите указатель мыши над одну из границ фрагмента.

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

Копирование путем перетаскивания

Если вам нужно скопировать выделенный массив ячеек в другое место рабочего листа с сохранением данных, делайте так:

Выделите диапазон ячеек, которые нужно скопировать.

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

Он станет похожим на крестик +. Это говорит о том, что будет выполняться копирование, а не перетаскивание.

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

Особенности вставки при перетаскивании

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

Автозаполнение формулами

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

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

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

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

Кликните на клетку, где находится формула.

Наведите курсор в нужную вам ячейку и нажмите F4.

В формуле аргумент с номером ячейки станет выглядеть так: $A$1 (абсолютная ссылка).

Когда вы протяните формулу, ссылка на ячейку $A$1 останется фиксированной и не будет меняться.

Как поставить «плюс», «равно» без формулы

Когда нужно указать отрицательное значение, поставить = или написать температуру воздуха, например, +22 °С, делайте так:

Кликаете правой кнопкой по ячейке и выбираете «Формат ячеек».

Теперь можно ставить = или +, а затем нужное число.

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

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

Слишком много вложений в выражении. Лимит 64 штуки.

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

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

Указывая имена книг и листов, пользователи забывают брать их в кавычки.

Числа в неверном формате. Например, символ $ в Эксель — это не знак доллара, а формат абсолютных ссылок.

Неправильно введенные диапазоны ячеек. Не забывайте ставить «:».

Коды ошибок при работе с формулами

Если вы сделаете ошибку в записи формулы, программа укажет на нее специальным кодом. Вот самые распространенные:

Отличие в версиях MS Excel

Всё, что написано в этом гайде, касается более современных версий программы 2007, 2010, 2013 и 2016 года. Устаревший Эксель заметно уступает в функционале и количестве доступных инструментов. Например, функция СЦЕП появилась только в 2016 году.

Во всем остальном старые и новые версии Excel не отличаются — операции и расчеты проводятся по одинаковым алгоритмам.

Заключение

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

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

Как записать в эксель

Для работы с данными, которые представлены в табличном формате, можно использовать программы, установленные на компьютере – Microsoft Excel или Numbers (для Mac OS). Или воспользоваться практически аналогичной по функционалу онлайн-версией Google Sheets. Панели инструментов разных версий Excel отличаются, поэтому мы покажем возможности работы с этим инструментом в Google Sheets, которые на любом компьютере выглядят одинаково.

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

Как устроен Excel

Пространство внутри программы похоже на лист бумаги с клетками. Каждая колонка здесь имеет свое название – по букве алфавита, а каждая строка – свой номер. У каждой ячейки есть свой адрес, который состоит из сочетания буквы столбца и номера строки – например, ячейка А1 или B2 – это чем-то похоже на игру в Морской бой. Сам файл похож на книгу со множеством листов. Нажимая на знак плюса в левом нижнем углу страницы, можно создавать новые листы, и например, помещать каждый набор данных на отдельный лист.

Импорт данных

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

Еще одно распространенное расширение – csv. Это текстовый файл, значения в котором разделены специальными символами – например, запятыми (отсюда и название – comma-separated values) или другими. Его можно открыть в обычном Блокноте. Там можно посмотреть содержимое файла, но чтобы обрабатывать такие данные, пригодится Excel. Чтобы открыть csv, необходимо нажать «Файл» – «Импортировать» – и указать путь к файлу.

После загрузки появится меню с разделом «Тип разделителя». Обычно Google Sheets сами определяют верный тип разделителя, поэтому галочку можно оставить на опции «Определять автоматически». Если же тип разделителя определен неверно, и вместо табличного представления вы получили данные в нечитаемом виде, можно указать тип разделителя самостоятельно. Выбрать из предложенных опций или вставить свой символ в окно «Другой». Затем нажать «Импортировать данные» и «Открыть сейчас».

Предварительная работа с данными

Когда данные загружены, первым делом стоит проверить, в удобном ли для работе виде они представлены. Важно, например, проверить, есть ли у столбцов (а иногда и строк) названия, это упростит работу с данными.

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

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

Формулы

В программе можно использовать простые математические формулы – например, сложение. Нужно активировать ячейку, которую необходимо заполнить (в нашем примере D2), и ввести =. Именно со знака равенства начинается любая формула в Excel. Затем нужно указать адрес первой ячейки, поставить +, и указать адрес второй ячейки, которую мы хотим прибавить к первой, и нажать клавишу ввода (Enter или Return). Программа сама посчитает сумму двух ячеек. В формуле можно указывать столько ячеек, сколько понадобится сложить. Сложим значения из столбцов «маск.рф» и «mask.ru». Формула будет такой: =B2+C2.

Автозаполнение ячеек

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

Функции

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

Для подсчета суммы нескольких значений, существует специальная функция СУММ. Предположим, что мы хотим посчитать сумму сайтов, созданных за весь период времени, доступный нам в данных. Введите =, а затем название функции – СУММ. Как только вы начнете вводить название функции, рядом с ней появится всплывающее окно со списком всех доступных функций, начинающихся с этих символов. Если нажать на нужную функцию из этого списка, она появится в ячейке, а после нее автоматически появится открывающая скобка.

Затем выделите курсором диапазон чисел, которые необходимо сложить, поставьте закрывающую скобку и нажмите клавишу ввода. В нашем примере функция будет выглядеть так: =СУММ(D2:D114). Этот же диапазон значений внутри скобок можно не выделять курсором, а указать вручную таким образом: адрес первой ячейки диапазона (D2), двоеточие, адрес последней ячейки диапазона (D114).

Сортировка

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

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

Функцию сортировки можно использовать и для анализа данных. Например, нам интересно, в какой день было зарегистрировано самое большое количество сайтов по продаже медицинских масок. Для этого надо проделать те же действия, но в качестве параметра сортировки выбрать «Все сайты» и «от Я до А». Тогда максимальное количество сайтов отобразится в самом начале таблицы, и мы увидим, что пик регистрации таких сайтов пришелся на 4 апреля.

Общая информация о данных

То же самое можно узнать и другим способом. Google Sheets автоматически подсчитывают основные характеристики данных и показывают их в одном месте. Можно выделить любой участок данных – например, целый столбец с количеством сайтов – и в правом нижнем углу листа появится меню с основными показателями. Там есть сумма, среднее значение, максимум, минимум и другие значения. Этим удобно пользоваться, чтобы быстро получить предварительное представление о данных.

Фильтрация

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

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

Фильтрацию можно выполнять и по условиям. Давайте узнаем, в какие дни зарегистрировали больше 50 сайтов. Для этого в меню фильтрации надо выбрать «Фильтровать по условию», выбрать из списка условие «Больше» и в окно вписать значение – «50». Осталось только три даты, когда было зарегистрировано больше 50 сайтов – это первые три дня апреля.

Есть и другие примеры условий. Например, мы хотим отобразить данные только за апрель. Нажимаем в меню фильтрации столбца «Дата» «Фильтровать по условию» – «Дата после» – «Точная дата» – и вводим «31.03.2020». Отобразились данные только за апрель, и теперь можно приступать к их анализу. Например, выделить этот столбец и в правом нижнем углу листа посмотреть общую информацию о данных. Мы увидим, например, что каждый день апреля, в среднем, регистрировали 34 таких сайта.

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

Сохранение

Google Sheets при наличии подключения к интернету самостоятельно сохраняют каждое действие и результаты работы. Если же вам понадобится сохранить обработанные данные на компьютер, это можно сделать так: выбрать в меню «Файл», нажать «Скачать» и выбрать из выпадающего меню необходимый формат – там есть как xlsx, так и csv, а также другие форматы.

В excel как написать

​Смотрите также​​ динамичней. Когда на​Нажимаем правой кнопкой мыши​ инструмента для создания​ умножить на 100.​ последовательности:​ например при обработке​​Устанавливаем курсор в поле​ показали, что при​ выражением. Первый из​установите флажок рядом​ название​Пользовательские функции должны начинаться​​price​
​, а не​ появится в формуле.​ обозначают какое-то конкретное​Формулы в Excel​ листе сформирована умная​ – выбираем в​ таблиц, чем Excel​ Ссылка на ячейку​%, ^;​ сложных формул, лучше​«Текст1»​ обычном введении без​ них заключается в​ с именем книги,​
​MonthLabels​ с оператора Function​
​(цена). При вызове​Sub​Если в каком-то​ действие. Подробнее о​помогут производить не​ таблица, становится доступным​ выпадающем меню «Вставить»​ не придумаешь.​ со значением общей​​*, /;​ пользоваться оператором​​. Вписываем туда слово​
​ второго амперсанда и​ применении амперсанда, а​​ как показано ниже.​вместо​ и заканчиваться оператором​ функции в ячейке​. Это значит, что​ диалоговом окне это​ таких символах, читайте​ только простые арифметические​
​ инструмент «Работа с​
​ (или жмем комбинацию​Работа с таблицами в​ стоимости должна быть​+, -.​СЦЕПИТЬ​«Итого»​ кавычек с пробелом,​ второй – в​Создав нужные функции, выберите​Labels​
​ End Function. Помимо​ листа необходимо указать​
​ они начинаются с​ имя не вставиться​ в статье «Символы​ действия (сложение, вычитание,​
​ таблицами» — «Конструктор».​
​ горячих клавиш CTRL+SHIFT+» data:image/gif;base64,R0lGODdhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=» data-src=»https://img.my-excel.ru/excel-znaki-v-formulah_2_1.jpg» width=»157″ height=»127″>​ эти два аргумента.​​ оператора​
​ обычным способом, то​ в формулах Excel».​ умножение и деление),​Здесь мы можем дать​Отмечаем «столбец» и жмем​ не терпит спешки.​ копировании она оставалась​ круглых скобок: Excel​Автор: Максим Тютюшев​
​ можно без кавычек,​ данные сольются. Вы​СЦЕПИТЬ​>​ указать его назначение.​ Function обычно включает​ В формуле =DISCOUNT(D7;E7)​Function​ сначала нажимаем кнопку​
​Второй способ​ но и более​ имя таблице, изменить​ ОК.​ Создать таблицу можно​ неизменной.​ в первую очередь​Формула предписывает программе Excel​ так как программа​
​ же можете установить​.​Сохранить как​ Описательные имена макросов​ один или несколько​ аргумент​, а не​ «Вставить имена», затем,​
​– вставить функцию.​​ сложные расчеты. Например,​
​ размер.​Совет. Для быстрой вставки​ разными способами и​Чтобы получить проценты в​ вычисляет значение выражения​ порядок действий с​ проставит их сама.​ правильный пробел ещё​Самый простой способ решить​.​ и пользовательских функций​ аргументов. Однако вы​​quantity​Sub​ выбираем нужное имя​Закладка «Формулы». Здесь​
​посчитать проценты в Excel,​​Доступны различные стили, возможность​ столбца нужно выделить​
​ для конкретных целей​ Excel, не обязательно​ в скобках.​ числами, значениями в​Потом переходим в поле​ при выполнении второго​ данную задачу –​В диалоговом окне​ особенно полезны, если​ можете создать функцию​имеет значение D7,​, и заканчиваются оператором​ из появившегося списка.​ идет перечень разных​ провести сравнение таблиц​
​ преобразовать таблицу в​ столбец в желаемом​ каждый способ обладает​ умножать частное на​​
​ ячейке или группе​«Текст2»​ пункта данного руководства.​ это применить символ​Сохранить как​ существует множество процедур​ без аргументов. В​ а аргумент​End Function​Если результат подсчета​ формул. Все формулы​ Excel, посчитать даты,​
​ обычный диапазон или​ месте и нажать​ своими преимуществами. Поэтому​ 100. Выделяем ячейку​Различают два вида ссылок​ ячеек. Без формул​. Устанавливаем туда курсор.​При написании текста перед​ амперсанда (​
​откройте раскрывающийся список​ с похожим назначением.​​ Excel доступно несколько​​price​, а не​ по формуле не​ подобраны по функциям​ возраст, время, выделить​ сводный отчет.​ CTRL+SHIFT+» data:image/gif;base64,R0lGODdhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=» data-src=»https://img.my-excel.ru/excel-znaki-v-formulah_7_1.jpg» alt=»» width=»397″ height=»243″>​. Во-вторых, они выполняют​ выходит решетка, это​ Например, если нам​ форматировании​ MS Excel огромны.​ при составлении таблицы​Посмотрите внимательно на рабочий​ Или нажимаем комбинацию​ копировании формулы эти​
​Конструкция формулы включает в​ которое выводит формула,​ знака «=» открываем​ логическое отделение данных,​Надстройка Excel​ пользовательские функции, —​ в которых нет​ в ячейки G8:G13,​
​ различные вычисления, а​ не значит, что​ нужно не просто​, т.д.​ Начнем с элементарных​ в программе Excel.​ лист табличного процессора:​ горячих клавиш: CTRL+SHIFT+5​ ссылки ведут себя​ себя: константы, операторы,​ а значит, следует​
​ кавычки и записываем​ которые содержит формула,​. Сохраните книгу с​ ваше личное дело,​ аргументов.​ вы получите указанные​ не действия. Некоторые​ формула не посчитала.​ сложить числа, а​Для того, чтобы​ навыков ввода данных​
​ Нам придется расширять​Это множество ячеек в​Копируем формулу на весь​ по-разному: относительные изменяются,​ ссылки, функции, имена​ дать ссылку на​ текст. После этого​ от текстового выражения.​ запоминающимся именем, таким​
​ но важно выбрать​После оператора Function указывается​ ниже результаты.​ операторы (например, предназначенные​ Всё посчитано, просто​ сложить числа, если​ таблица произвела необходимый​
​ и автозаполнения:​ границы, добавлять строки​​ столбцах и строках.​ столбец: меняется только​ абсолютные остаются постоянными.​ диапазонов, круглые скобки​ ячейку, её содержащую.​ закрываем кавычки. Ставим​ Давайте посмотрим, как​ как​ определенный способ и​ один или несколько​Рассмотрим, как Excel обрабатывает​ для выбора и​ нужно настроить формат​ они будут больше​ нам расчет, мы​Выделяем ячейку, щелкнув по​ /столбцы в процессе​ По сути –​
​ первое значение в​Все ссылки на ячейки​ содержащие аргументы и​ Это можно сделать,​ знак амперсанда. Затем,​ можно применить указанный​MyFunctions​
​ придерживаться его.​ операторов VBA, которые​ эту функцию. При​ форматирования диапазонов) исключаются​ ячейки, числа. Смотрите​ 100. То здесь​ должны написать формулу,​ ней левой кнопкой​
​ работы.​ таблица. Столбцы обозначены​ формуле (относительная ссылка).​ программа считает относительными,​ другие формулы. На​ просто вписав адрес​

Создание пользовательских функций в Excel

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

Создание простой пользовательской функции

​ примере разберем практическое​ вручную, но лучше​ нужно внести пробел,​​У нас имеется небольшая​Сохранив книгу, выберите​​ открыть книгу, содержащую​ и выполняют вычисления​ВВОД​ Из этой статьи​​ Excel. Формат».​​В Excel есть функции​​ произведен этот расчет.​​ /числовое значение. Жмем​ названия столбцов. Вносим​ – цифрами. Если​​ остается прежним. Проверим​​ задано другое условие.​​ применение формул для​​ установить курсор в​​ открываем кавычки, ставим​​ таблица, в которой​​Сервис​​ модуль, в котором​ с использованием аргументов,​Excel ищет имя​ вы узнаете, как​Иногда, достаточно просто​ финансовые, математические, логические,​ Для начала определим,​ ВВОД. Если необходимо​ данные – заполняем​ вывести этот лист​ правильность вычислений –​ С помощью относительных​ начинающих пользователей.​​ поле и кликнуть​​ пробел и закрываем​ в двух столбцах​

​>​ она была создана.​ переданных функции. Наконец,​DISCOUNT​ создавать и применять​ увеличить ширину столбца​ статистические, дата и​ в какой ячейке​

​ изменить значение, снова​ строки. Сразу применяем​ на печать, получим​ найдем итог. 100%.​ ссылок можно размножить​Чтобы задать формулу для​ по ячейке, содержащей​

​ кавычки. Щелкаем по​ указаны постоянные и​Надстройки Excel​

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

​ переменные затраты предприятия.​.​ не открыта, при​

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

Применение пользовательских функций

​.​ находится простая формула​Надстройки​ возникнет ошибка #ИМЯ?​ с тем же​ в модуле VBA.​

​редактор Visual Basic (VBE)​

​ размер столбца, ячейки,​ функцию.​ на неё левой​ данные.​ высоту для строк.​

​ с ячейками, строками​ ссылок:​ столбцов.​ (=). Так же​В поле​​Для записи текста вместе​​ сложения, которая суммирует​​нажмите кнопку «Обзор»,​​ При ссылке на​ именем, что у​ Имена аргументов, заключенные​, который открывается в​ строки, т.д., читайте​Эта же кнопка​​ мышкой и эта​​При введении повторяющихся значений​Чтобы заполнить графу «Стоимость»,​​ и столбцами.​​$В$2 – при копировании​Вручную заполним первые графы​ можно вводить знак​«Текст3»​ с функцией, а​

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

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

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

​ требуется в ту​Открыть​ указать перед ее​ которая вызывает функцию.​и​​ предоставляет скидку в​​ высоту строки в​​ строкой адреса ячейки​​ формула в Excel.​ на клавиатуре несколько​

​ «=». Таким образом,​ щелкаем по его​

​B$2 – при копировании​ вариант:​​ формулы нажать Enter.​​ кнопке​ точно такие же,​ же ячейку, где​​, а затем установите​​ именем название книги.​В пользовательских функциях поддерживается​price​ размере 10 % клиентам,​ Excel» тут.​ и строкой ввода​Перед вводом самой​​ символов и нажать​​ мы сигнализируем программе​ названию (латинской букве)​ неизменна строка;​Вспомним из математики: чтобы​ В ячейке появится​«OK»​ как были описаны​

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

​), представляют собой заполнители​

​ заказавшим более 100​В формулы можно​​ формул. Эта кнопка​​ формулы в ячейке​ Enter.​

​ Excel: здесь будет​

​ левой кнопкой мыши.​$B2 – столбец не​ найти стоимость нескольких​ результат вычислений.​.​ выше.​ затрат добавить после​ надстройкой в поле​ создали функцию DISCOUNT​ VBA, чем в​ для значений, на​​ единиц товара. Ниже​​ написать не только​ активна при любой​ всегда ставим сначала​Чтобы применить в умной​ формула. Выделяем ячейку​Для выделения строки –​

Правила создания пользовательских функций

​ изменяется.​ единиц товара, нужно​В Excel применяются стандартные​Результат выведен в предварительно​Текст также можно указывать​ формулы поясняющее слово​Доступные надстройки​ в книге Personal.xlsb​ макросах. Они могут​ основе которых вычисляется​ мы объясним, как​ адрес ячейки, но​ открытой вкладке, не​ знак «равно» -​ таблице формулу для​

​ В2 (с первой​ по названию строки​Чтобы сэкономить время при​ цену за 1​ математические операторы:​ выделенную ячейку, но,​ в виде ссылки​«рублей»​.​ и хотите вызвать​ только возвращать значение​ скидка.​ создать функцию для​ и вставить определенные​ надо переключаться на​

Применение ключевых слов VBA в пользовательских функциях

​ это сигнал программе,​ всего столбца, достаточно​ ценой). Вводим знак​ (по цифре).​ введении однотипных формул​ единицу умножить на​Оператор​ как видим, как​ на ячейку, в​.​После выполнения этих действий​ ее из другой​ в формулу на​Оператор If в следующем​ расчета такой скидки.​ знаки, чтобы результат​ вкладку «Формулы». Кроме​ что ей надо​ ввести ее в​ умножения (*). Выделяем​Чтобы выделить несколько столбцов​

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

Документирование макросов и пользовательских функций

​ выражение. Для этого​ будут доступны при​=personal.xlsb!discount()​ выражение, используемое в​ аргумент​ форма заказа, в​ пример в статье​ формат ячейки «Процентный».​ Теперь вводим саму​ этого столбца. Программа​ количеством). Жмем ВВОД.​ левой кнопкой мыши​ Если нужно закрепить​ в ячейку D2:​+ (плюс)​ записаны слитно без​ алгоритм действий остается​ либо производим по​ каждом запуске Excel.​

​, а не просто​ другом макросе или​quantity​ которой перечислены товары,​ «Подстановочные знаки в​ Например, нам нужно​ формулу.​ скопирует в остальные​Когда мы подведем курсор​ по названию, держим​ ссылку, делаем ее​ = цена за​Сложение​ пробелов.​ прежним, только сами​ ней двойной щелчок​ Если вы хотите​

​=discount()​ функции VBA. Так,​и сравнивает количество​ их количество и​ Excel» здесь.​​ посчитать сумму долга.​​В формуле Excel можно​​ ячейки автоматически.​​ к ячейке с​ и протаскиваем.​ абсолютной. Для изменения​ единицу * количество.​=В4+7​Для того, чтобы решить​ координаты ячейки в​

​ левой кнопкой мыши,​ добавить функции в​.​ пользовательские функции не​ проданных товаров со​ цена, скидка (если​Здесь показаны относительные​

Предоставление доступа к пользовательским функциям

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

​ ссылки на ячейки​Вычитание​ выделяем ячейку, содержащую​ нужно.​ жмем на функциональную​ редактор Visual Basic.​ быстрее (и избежать​ окон, формулы в​

​If quantity >= 100​ итоговая стоимость.​ но есть еще​ формулу в другие​Как создать формулу в​ плюс пустая ячейка​ крестик. Он указываем​ ставим курсор в​Простейшие формулы заполнения таблиц​ с соответствующими значениями.​

​ оператор​​Также для вставки текста​​ клавишу​​ В обозревателе проектов​​ ошибок), ее можно​

​ ячейках, а также​​ Then​​Чтобы создать пользовательскую функцию​​ абсолютные и смешанные​​ ячейки столбца, чтобы​

​ Excel​​ для будущего итога​​ на маркер автозаполнения.​​ любую ячейку нужного​​ в Excel:​​Нажимаем ВВОД – программа​​* (звездочка)​СЦЕПИТЬ​ вместе с результатом​​F2​​ под заголовком VBAProject​​ выбрать в диалоговом​​ шрифт, цвет или​DISCOUNT = quantity​ DISCOUNT в этой​​ ссылки, ссылки в​​ не вводить каждую​смотрите в статье​ и нажимаем кнопку​

​ Цепляем его левой​​ столбца – нажимаем​​Перед наименованиями товаров вставим​​ отображает значение умножения.​​Умножение​

​и переходим в​​ подсчета формулы можно​​. Также можно просто​​ вы увидите модуль​​ окне «Вставка функции».​

​ узор для текста​​ * price *​​ книге, сделайте следующее:​​ формулах на другие​​ формулу вручную.​

​ «Сложение, вычитание, умножение,​​ «Сумма» (группа инструментов​​ кнопкой мыши и​​ Ctrl + пробел.​​ еще один столбец.​​ Те же манипуляции​​=А3*2​

​ строку формул. Там​​ использовать функцию​​ выделить ячейку, а​ с таким же​ Пользовательские функции доступны​

​ в ячейке. Если​​ 0.1​​Нажмите клавиши​​ листы таблицы Excel.​​Для этого выделим​

​ деление в Excel».​​ «Редактирование» на закладке​​ ведем до конца​​ Для выделения строки​​ Выделяем любую ячейку​​ необходимо произвести для​​/ (наклонная черта)​ после каждого аргумента,​СЦЕПИТЬ​​ потом поместить курсор​​ названием, как у​

​ в категории «Определенные​​ включить в процедуру​​Else​​ALT+F11​​Ссылка в формулах -​

​ ячейку с формулой,​​ Здесь же написано​​ «Главная» или нажмите​ столбца. Формула скопируется​ – Shift +​​ в первой графе,​​ всех ячеек. Как​Деление​ то есть, после​​. Данный оператор предназначен​​ в строку формул.​

​ файла надстройки (но​ пользователем»:​ функции код для​DISCOUNT = 0​(или​ это адрес ячейки.​ наведем курсор на​ где на клавиатуре​ комбинацию горячих клавиш​ во все ячейки.​ пробел.​ щелкаем правой кнопкой​ в Excel задать​=А7/А8​ каждой точки с​

​ для того, чтобы​Сразу после формулы ставим​ без расширения XLAM).​Чтобы упростить доступ к​ таких действий, возникнет​End If​FN+ALT+F11​Адрес ячейки можно​ правый нижний угол​ расположены знаки сложения,​ ALT+» https://support.office.com/ru-ru/article/%D0%A1%D0%BE%D0%B7%D0%B4%D0%B0%D0%BD%D0%B8%D0%B5-%D0%BF%D0%BE%D0%BB%D1%8C%D0%B7%D0%BE%D0%B2%D0%B0%D1%82%D0%B5%D0%BB%D1%8C%D1%81%D0%BA%D0%B8%D1%85-%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D0%B9-%D0%B2-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f» title=»support.office.com»>support.office.com

Вставка текста в ячейку с формулой в Microsoft Excel

​ таблицы не помещается​ Или жмем сначала​ копируем формулу из​Степень​ выражение:​ ячейке значения, выводимые​&​ Project Explorer, чтобы​ определить их в​Единственное действие, которое может​ не меньше 100,​ открыть редактор Visual​ тогда, при переносе​ крестик в правом​ их нажать.​ справа каждого подзаголовка​ данными. Нажимаем кнопку:​ нужно изменить границы​ комбинацию клавиш: CTRL+ПРОБЕЛ,​ первой ячейки в​

Процедура вставки текста около формулы

​ в нескольких элементах​). Далее в кавычках​ вывести код функций.​ отдельной книге, а​ выполнять процедура функции​ VBA выполняет следующую​ Basic, а затем​ этой формулы, адрес​ нижнем углу выделенной​Первый способ.​ шапки, то мы​ «Главная»-«Границы» (на главной​ ячеек:​ чтобы выделить весь​ другие строки. Относительные​= (знак равенства)​Между кавычками должен находиться​​ листа. Он относится​​ записываем слово​

Способ 1: использование амперсанда

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

​ ссылки – в​Равно​ пробел. В целом​ к категории текстовых​«рублей»​ функцию, установите точку​ как надстройку, которую​ это отображение диалогового​ значения​Insert​ не изменится. А​ ведем крестик вниз​ в том же​ дополнительным инструментам для​ «Шрифт»). И выбираем​​ ячейки левой кнопкой​​ потом комбинация: CTRL+SHIFT+» data:image/gif;base64,R0lGODdhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=» data-src=»https://img.my-excel.ru/excel-v-tekst-vstavit-formulu-v_2_1.png» alt=»Таблица с формулой в Microsoft Excel» width=»879″ height=»728″>

​ помощь.​Меньше​ в строке функций​ функций. Его синтаксис​. При этом кавычки​ вставки после оператора​ можно включать при​ окна. Чтобы получить​​quantity​​(Вставка) >​ можно сделать так,​ по столбцу. Отпускаем​ порядке, в каком​

​ формулы, адрес ячейки​ скопированы.​ написали на листочке​Иногда пользователю приходится работать​ столбцов и строк​ в ячейку, щелкнуть​​ п/п». Вводим в​​ столбца маркер автозаполнения.​

​Windows macOS ​ оператор​ результат на 0,1:​ Basic появится окно​ будет писаться новый,​ «ИТОГО» — автосумму.​ правила математики. Только​ Чтобы посмотреть итоги,​С помощью меню «Шрифт»​ границе столбца /​ во вторую –​ точку левой кнопкой​Больше или равно​

​ENTER​1​ указателем для программы,​ Вы можете создать​Создав нужные функции, выберите​InputBox​Discount = quantity *​ нового модуля.​ по отношению к​ Смотрите, как это​ вместо чисел пишем​ нужно пролистать не​ можно форматировать данные​ строки. Программа автоматически​​ «2». Выделяем первые​​ мыши, держим ее​

​<>​. Теперь наши значения​до​ что это текст.​ любое количество функций,​Файл​. Кроме того, с​

​ price * 0.1​Скопируйте указанный ниже код​ переносимой ячейке. Это​ сделать, в статье​ адрес ячейки с​ одну тысячу строк.​ таблицы Excel, как​ расширит границы.​ две ячейки –​ и «тащим» вниз​

Способ 2: применение функции СЦЕПИТЬ

​Не равно​ разделены пробелами.​255​ Для того, чтобы​​ и они будут​​>​ помощью оператора​Результат хранится в виде​ и вставьте его​ удобно для копирования​ «Закладка листа Excel​ этим числом.​ Удалить строки –​ в программе Word.​

​Если нужно сохранить ширину​

​ «цепляем» левой кнопкой​ по столбцу.​​Символ «*» используется обязательно​​При желании можно спрятать​​аргументов. Каждый из​​ вывести результат в​ всегда доступны в​Сохранить как​MsgBox​ переменной​ в новый модуль.​ формулы в таблице,​

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

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

​ и любые другие​​Enter​​Вставка функции​​кнопку Microsoft Office​​ также можете использовать​ хранит значение в​ Then​ ячейке заново.​

​ программка.​​ такую формулу: (25+18)*(21-7)​​ этой цели воспользуйтесь​ текст по центру,​ на панели инструментов.​ можно заполнить, например,​ относительными ссылками. То​ арифметических вычислений, недопустимо.​ чтобы он не​ символы), либо ссылки​на клавиатуре.​.​, а затем щелкните​ настраиваемые диалоговые окна​ переменной, называется оператором​DISCOUNT = quantity​Как написать формулу​Если нужно, чтобы​

​ Вводимую формулу видим​​ числовыми фильтрами (картинка​​ назначить переносы и​

​Для изменения ширины столбцов​ даты. Если промежутки​​ есть в каждой​​ То есть запись​

​ действия, вслед за​

​ главе книги​.​UserForms​, так как он​ 0.1​

​ другие листы быстро,​

​значение ячейки в Excel​​Получилось.​​ напротив тех значений,​Простейший способ создания таблиц​

​ в Excel есть​​ увеличиваем 1 столбец​
​ первую ячейку «окт.15»,​Ссылки в ячейке соотнесены​

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

Работа в Excel с формулами и таблицами для чайников

​ все ту же​«рублей»​ (Mark Dodge) и​Тип файла​Даже простые макросы и​ имени переменной слева​DISCOUNT = Application.Round(Discount,​

​ сразу».​ А1 перенести в​ и в самой​: формат ячейки -​ (в плане последующего​ – автоматически изменится​ «ноя.15». Выделим первые​Формула с абсолютной ссылкой​ формулу числа и​

Формулы в Excel для чайников

​, но убрать элемент​ таблицу, только добавим​. Но у этого​ Крейгом Стинсоном (Craig​и выберите значение​ пользовательские функции может​ от него. Так​ 2)​Бывает в таблице​ ячейку В2. Формула​ ячейке.​

​ текстовый​ форматирования, работы с​

​Примечание:​ буквами, а числами.​ такая: =А1. Всё.​как посчитать проценты в​: нажать правой кнопкой​Сделаем «умную» (динамическую) таблицу:​Примечание. Чтобы вернуть прежний​ вниз.​

​ ячейку. То есть​ результат.​ по сектору панели​«Общая сумма затрат»​ и текстовое пояснение​ относящиеся к более​ запоминающимся именем, таким​

​ задачу проще, добавьте​называется так же,​ Чтобы код было более​ Как изменить название​Ещё, в Excel​ Excel​

​ — формат ячейки​Переходим на вкладку «Вставка»​ размер, можно нажать​

​Найдем среднюю цену товаров.​ при автозаполнении или​Но чаще вводятся адреса​

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

​ как и процедура​

1. ​ удобно читать, можно​ столбцов на буквы,​ можно дать имя​
2. ​- 2% от​ — текстовый -​ — инструмент «Таблица»​ кнопку «Отмена» или​ Выделяем столбец с​ копировании константа остается​
3. ​ ячеек. То есть​ который следует скрыть.​Выделяем пустую ячейку столбца​

​ пробела.​Довольно часто при работе​MyFunctions​ Для этого нужно​ функции, значение, хранящееся​

• ​ добавлять отступы строк​
• ​ читайте статью «Поменять​
• ​ формуле, особенно, если​

​ 500.​ ок​ (или нажмите комбинацию​ комбинацию горячих клавиш​ ценами + еще​

​ неизменной (или постоянной).​

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

​ пользователь вводит ссылку​ После этого весь​«Общая сумма затрат»​При этом, если мы​ в Excel существует​, в папке​ ввести перед текстом​

​ в переменной, возвращается​ с помощью клавиши​ название столбцов в​ она сложная. Затем,​Вводим формулу​Аэль​ горячих клавиш CTRL+T).​ CTRL+Z. Но она​ одну ячейку. Открываем​Чтобы указать Excel на​

1. ​ на ячейку, со​ столбец выделяется. Щелкаем​. Щелкаем по пиктограмме​ попытаемся поставить пробел​
2. ​ необходимость рядом с​AddIns​ апостроф. Например, ниже​ в формулу листа,​TAB​ таблице Excel».​ в ячейке писать​, получилось​: изменить формат ввода​В открывшемся диалоговом окне​ срабатывает тогда, когда​ меню кнопки «Сумма»​ абсолютную ссылку, пользователю​
3. ​ значением которой будет​ по выделению правой​«Вставить функцию»​ вручную, то это​ результатом вычисления формулы​. Она будет автоматически​ показана функция DISCOUNT​ из которой была​. Отступы необязательны и​Подробнее об относительных​ не всю большую​.​

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

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

​Когда формула не​ текстовый например.. или​

​ данных. Отмечаем, что​ – не поможет.​ для автоматического расчета​ доллара ($). Проще​При изменении значений в​ контекстное меню. Выбираем​ строки формул.​

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

1. ​ имя этой формулы.​ считает, значит мы​ на какой то​ таблица с подзаголовками.​Чтобы вернуть строки в​ среднего значения.​ всего это сделать​
2. ​ ячейках формула автоматически​ в нем пункт​Производится активация​ нажата кнопка​
3. ​ этих данных. Конечно,​Сохранить как​ вам, и другим​quantity​ добавить отступ, редактор​ в статье «Относительные​

​ Смотрите статью «Присвоить​ не правильно её​ ещё)) попробуйте поиграть​ Жмем ОК. Ничего​ исходные границы, открываем​

1. ​Чтобы проверить правильность вставленной​ с помощью клавиши​ пересчитывает результат.​«Скрыть»​Мастера функций​Enter​ можно выделить для​, поэтому вам потребуется​ будет впоследствии проще​меньше 100, VBA​
2. ​ Visual Basic автоматически​ и абсолютные ссылки​ имя в Excel​ написали, но можно​ с форматами ввода​ страшного, если сразу​ меню инструмента: «Главная»-«Формат»​ формулы, дважды щелкните​
3. ​ F4.​Ссылки можно комбинировать в​.​. Перемещаемся в категорию​, результат снова «склеится».​ пояснений отдельный столбец,​ только принять расположение,​ работать с кодом​ выполняет следующий оператор:​

​ вставит его и​ в Excel».​ ячейке, диапазону, формуле».​

• ​ проверить — где​ символов в ячейки​ не угадаете диапазон.​
• ​ и выбираем «Автоподбор​ по ячейке с​
• ​Создадим строку «Итого». Найдем​ рамках одной формулы​

Как составить таблицу в Excel с формулами

​После этого, как видим,​«Текстовые»​Но из сложившейся ситуации​ но не во​ используемое по умолчанию.​ VBA. Так, код​Discount = 0​ для следующей строки.​Хотя в Excel предлагается​

​Как правильно написать имя​ допустили в формуле​

1. ​Dorian pasat​ «Умная таблица» подвижная,​ высоты строки»​ результатом.​ общую стоимость всех​ с простыми числами.​ ненужный нам столбец​. Далее выделяем наименование​ все-таки существует выход.​ всех случаях добавление​Сохранив книгу, выберите​ будет легче понять,​
2. ​Наконец, следующий оператор округляет​ Чтобы сдвинуть строку​ большое число встроенных​ диапазона в формуле​ ошибку. Смотрите в​: формат ячеек дополнительно​ динамическая.​Для столбцов такой метод​Программа Microsoft Excel удобна​
3. ​ товаров. Выделяем числовые​Оператор умножил значение ячейки​ скрыт, но при​«СЦЕПИТЬ»​ Снова активируем ячейку,​ дополнительных элементов является​Файл​ если потребуется внести​ значение, назначенное переменной​ на один знак​ функций, в нем​ Excel​
4. ​ статье «Как проверить​ — почтовый индекс​Примечание. Можно пойти по​ не актуален. Нажимаем​ для составления таблиц​ значения столбца «Стоимость»​ В2 на 0,5.​ этом данные в​

​и жмем на​ которая содержит формульное​ рациональным. Впрочем, в​>​

Как работать в Excel с таблицами для чайников: пошаговая инструкция

​ в него изменения.​Discount​ табуляции влево, нажмите​ может не быть​.​ формулы в Excel».​Алекс куха​ другому пути –​ «Формат» — «Ширина​ и произведения расчетов.​

​ плюс еще одну​ Чтобы ввести в​ ячейке, в которой​ кнопку​ и текстовое выражения.​ Экселе имеются способы​Параметры Excel​Апостроф указывает приложению Excel​, до двух дробных​SHIFT+TAB​ той функции, которая​Есть формулы, в​В Excel можно​: апостроф 1м символом​ сначала выделить диапазон​ по умолчанию». Запоминаем​ Рабочая область –​

Как создать таблицу в Excel для чайников

​ ячейку. Это диапазон​ формулу ссылку на​ расположена функция​«OK»​ Сразу после амперсанда​ поместить формулу и​.​ на то, что​ разрядов:​.​

​ нужна для ваших​ которых лучше указать​

​ написать сложную большую​ спасёт положение. В​ ячеек, а потом​ эту цифру. Выделяем​ это множество ячеек,​ D2:D9​ ячейку, достаточно щелкнуть​СЦЕПИТЬ​.​ открываем кавычки, затем​

​ текст в одну​В Excel 2007 нажмите​ следует игнорировать всю​

​Discount = Application.Round(Discount, 2)​

Как выделить столбец и строку

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

​ общем случае, сказали​ нажать кнопку «Таблица».​ любую ячейку в​

​ которые можно заполнять​Воспользуемся функцией автозаполнения. Кнопка​ по этой ячейке.​отображаются корректно.​Запускается окошко аргументов оператора​

​ устанавливаем пробел, кликнув​ ячейку вместе. Давайте​кнопку Microsoft Office​ строку справа от​В VBA нет функции​ новую функцию DISCOUNT.​ разработчики Excel не​ Трудно правильно написать​ условиями (вложенными функциями).​

Как изменить границы ячеек

​ выше​Теперь вносите необходимые данные​ столбце, границы которого​ данными. Впоследствии –​

1. ​ находится на вкладке​В нашем примере:​Читайте также:​
2. ​СЦЕПИТЬ​ по соответствующей клавише​ разберемся, как это​и щелкните​ него, поэтому вы​ округления, но она​
3. ​ Закройте редактор Visual​ могли предугадать все​ в формуле имя​ Как правильно написать​Filosofika​

​ в готовый каркас.​ необходимо «вернуть». Снова​ форматировать, использовать для​ «Главная» в группе​Поставили курсор в ячейку​Функция СЦЕПИТЬ в Экселе​. Данное окно состоит​ на клавиатуре, и​ можно сделать при​

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

​ Если потребуется дополнительный​ «Формат» — «Ширина​ построения графиков, диаграмм,​ инструментов «Редактирование».​ В3 и ввели​

​Как скрыть столбцы​ из полей под​ закрываем кавычки. После​ помощи различных вариантов.​.​ в отдельных строках​ Чтобы использовать округление​ G7 и введите​ в Excel можно​ таблице много именованных​ в статье «Как​ и невозможно что-то​ столбец, ставим курсор​ столбца» — вводим​ сводных отчетов.​После нажатия на значок​

Как вставить столбец или строку

​ =.​ в Экселе​ наименованием​ этого снова ставим​Скачать последнюю версию​В диалоговом окне​ или в правой​ в этом операторе,​

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

​ в предназначенную для​ заданный программой показатель​

​Работа в Экселе с​ «Сумма» (или комбинации​Щелкнули по ячейке В2​Таким образом, можно сказать,​«Текст»​

​ знак амперсанда (​ Excel​Параметры Excel​ части строк, содержащих​ необходимо указать VBA,​=DISCOUNT(D7;E7)​ и ниже вы​

Пошаговое создание таблицы с формулами

1. ​ Excel есть волшебная​ Excel для начинающих».​Светлана попова​ названия ячейку. Вписываем​ (как правило это​ таблицами для начинающих​ клавиш ALT+«=») слаживаются​ – Excel «обозначил»​
2. ​ что существуют два​. Их количество достигает​&​Если просто попробовать вставить​выберите категорию​ код VBA. Советуем​ что метод (функцию)​Excel вычислит 10%-ю скидку​ найдете все нужные​ кнопка «Использовать в​Можно в таблице​: если надо написать​
3. ​ наименование и нажимаем​ 8,43 — количество​ пользователей может на​ выделенные числа и​ ее (имя ячейки​ способа вписать в​255​). Затем щелкаем по​ текст в одну​Надстройки​ начинать длинный блок​
4. ​ Round следует искать​ для 200 единиц​ для этого инструкции.​ формуле» на закладке​ Excel выделить сразу​ число 0000625​ ВВОД. Диапазон автоматически​

​ символов шрифта Calibri​ первый взгляд показаться​ отображается результат в​

​ появилось в формуле,​ одну ячейку формулу​, но для нашего​ клавише​

​ ячейку с функцией,​.​ кода с комментария,​ в объекте Application​ по цене 47,50​Пользовательские функции (как и​

Как создать таблицу в Excel: пошаговая инструкция

​ «Формулы» в разделе​ все ячейки с​Алексей василевский​ расширится.​ с размером в​ сложной. Она существенно​ пустой ячейке.​

​ вокруг ячейки образовался​

1. ​ и текст: при​ примера понадобится всего​Enter​ то при такой​
2. ​В раскрывающемся списке​ в котором объясняется​ (Excel). Для этого​ ₽ и вернет​ макросы) записываются на​ «Определенные имена».​ формулами или найти​: Формат «почтовый индекс»​Если необходимо увеличить количество​

​ 11 пунктов). ОК.​ отличается от принципов​Сделаем еще один столбец,​ «мелькающий» прямоугольник).​ помощи амперсанда и​

​ три поля. В​.​ попытке Excel выдаст​Управление​ его назначение, а​ добавьте слово​ 950,00 ₽.​ языке программирования​При написании формулы, нажимаем​

​ формулы с ошибками.​ лично мне не​ строк, зацепляем в​Выделяем столбец /строку правее​ построения таблиц в​

Как работать с таблицей в Excel

​ где рассчитаем долю​Ввели знак *, значение​ функции​ первом мы разместим​Как видим, теперь результат​ сообщение об ошибке​выберите​ затем использовать встроенные​Application​

​В первой строке кода​Visual Basic для приложений​ на эту кнопку,​

​ Смотрите статью «Как​ позволил в 2007​ нижнем правом углу​ /ниже того места,​

​ Word. Но начнем​ каждого товара в​ 0,5 с клавиатуры​СЦЕПИТЬ​ текст, во втором​

1. ​ вычисления формулы и​ в формуле и​Надстройки Excel​ комментарии для документирования​перед словом Round.​ VBA функция DISCOUNT(quantity,​ (VBA)​ и выходит список​ выделить в Excel​ экселе поставит 0​
2. ​ за маркер автозаполнения​ где нужно вставить​ мы с малого:​ общей стоимости. Для​ и нажали ВВОД.​. Первый вариант проще​
3. ​ – ссылку на​ текстовое выражение разделены​ не позволит совершить​. Затем нажмите кнопку​ отдельных операторов.​ Используйте этот синтаксис​ price) указывает, что​. Они отличаются от​
4. ​ имен диапазонов. Нажимаем​ ячейки с формулами»​ перед числом, а​ и протягиваем вниз.​ новый диапазон. То​ с создания и​ этого нужно:​Если в одной формуле​ и для многих​ ячейку, в которой​

​ пробелом.​ такую вставку. Но​Перейти​Кроме того, рекомендуется присваивать​ каждый раз, когда​ функции DISCOUNT требуется​ макросов двумя вещами.​

​ один раз левой​ здесь.​ вот «текстовый» формат​С выходом новых версий​ есть столбец появится​ форматирования таблицы. И​Разделить стоимость одного товара​ применяется несколько операторов,​ пользователей удобнее. Но,​ содержится формула, и​Естественно, что все указанные​ существует два способа​.​ макросам и пользовательским​ нужно получить доступ​

Как в ячейке Excel написать число начинающееся с нуля, например 04544444

​ два аргумента:​​ Во-первых, в них​ мышкой на название​

​В Excel в​​ — да. Спасибо​ программы работа в​ слева от выделенной​ в конце статьи​

​ на стоимость всех​​ то программа обработает​ тем не менее,​ в третьем опять​ действия проделывать не​ все-таки вставить текст​В диалоговом окне​ функциям описательные имена.​

​ к функции Excel​​quantity​ используются процедуры​

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

​ вы уже будете​​ товаров и результат​ их в следующей​ в определенных обстоятельствах,​

​ разместим текст.​​ обязательно. Мы просто​ рядом с формульным​

​Надстройки​​ Например, присвойте макросу​ из модуля VBA.​(количество) и​Function​ это имя диапазона​ разные символы, которые​ вопрос!​ стала интересней и​ – выше.​

Как написать макрос в Excel на языке программирования VBA

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

Создание макросов

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

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

Создать макрос в Excel с помощью макрорекордера

Для начала проясним, что собой представляет макрорекордер и при чём тут макрос.

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

Этот способ очень полезен тем, кто не владеет навыками и знаниями работы в языковой среде VBA. Но такая легкость в исполнении и записи макроса имеет свои минусы, как и плюсы:

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

Для включения макрорекордера на запись необходимо произвести следующие действия:

• в версии Excel от 2007 и к более новым вам нужно на вкладке «Разработчик» нажать кнопочку «Запись макроса»
• в версиях Excel от 2003 и к более старым (они еще очень часто используются) вам нужно в меню «Сервис» выбрать пункт «Макрос» и нажать кнопку «Начать запись».

Следующим шагом в работе с макрорекордером станет настройка его параметров для дальнейшей записи макроса, это можно произвести в окне «Запись макроса», где:

• поле «Имя макроса» — можете прописать понятное вам имя на любом языке, но должно начинаться с буквы и не содержать в себе знаком препинания и пробелы;
• поле «Сочетание клавиш» — будет вами использоваться, в дальнейшем, для быстрого старта вашего макроса. В случае, когда вам нужно будет прописать новое сочетание горячих клавиш , то эта возможность будет доступна в меню «Сервис» — «Макрос» — «Макросы» — «Выполнить» или же на вкладке «Разработчик» нажав кнопочку «Макросы» Sub MyMakros()
  Dim polzovatel As String
  Dim data_segodnya As Date
  polzovatel = Application.UserName
  data_segodnya = Now
  MsgBox «Макрос запустил пользователь: » & polzovatel & vbNewLine & data_segodnya
  End Sub

Примечание. Если в главном меню отсутствует закладка «РАЗРАБОТЧИК», тогда ее необходимо активировать в настройках: «ФАЙЛ»-«Параметры»-«Настроить ленту». В правом списке «Основные вкладки:» активируйте галочкой опцию «Разработчик» и нажмите на кнопку ОК.

Настройка разрешения для использования макросов в Excel

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

Вариант 1: Автоматическая запись макросов

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

Подробнее: Включение и отключение макросов в Microsoft Excel

Когда все готово, приступаем к записи.

Перейдите на вкладку «Разработчик». Кликните по кнопке «Запись макроса», которая расположена на ленте в блоке инструментов «Код».

Запуск макроса

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

Кликаем в том же блоке инструментов «Код» по кнопке «Макросы» или жмем сочетание клавиш Alt + F8.

Редактирование макроса

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

Снова щелкаем на кнопку «Макросы». В открывшемся окне выбираем нужный и кликаем по кнопке «Изменить».

Создание кнопки для запуска макросов в панели инструментов

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

1. Кнопка в панели инструментов в MS Excel 2003 и более старше. Вам нужно в меню «Сервис» в пункте «Настройки» перейти на доступную вкладку «Команды» и в окне «Категории» выбрать команду «Настраиваемая кнопка» обозначена жёлтым колобком или смайликом, кому как понятней или удобней. Вытащите эту кнопку на свою панель задач и, нажав правую кнопку мыши по кнопке, вызовите ее контекстное меню, в котором вы сможете отредактировать под свои задачи кнопку, указав для нее новую иконку, имя и назначив нужный макрос.
2. Кнопка в панели вашего быстрого доступа в MS Excel 2007 и более новее. Вам нужно клацнуть правой кнопкой мышки на панели быстрого доступа , которое находится в верхнем левом углу окна MS Excel и в открывшемся контекстном меню выбираете пункт «Настройка панели быстрого доступа». В диалоговом окне настройки вы выбираете категорию «Макросы» и с помощью кнопки «Добавить» вы переносите выбранный со списка макрос в другую половинку окна для дальнейшего закрепления этой команды на вашей панели быстрого доступа.

Создание графической кнопки на листе Excel

Данный способ доступен для любой из версий MS Excel и заключается он в том, что мы вынесем кнопку прямо на наш рабочий лист как графический объект. Для этого вам нужно:

• В MS Excel 2003 и более старше переходите в меню «Вид», выбираете «Панель инструментов» и нажимаете кнопку «Формы».
• В MS Excel 2007 и более новее вам нужно на вкладке «Разработчик» открыть выпадающее меню «Вставить» и выбрать объект «Кнопка».

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

Чтобы записать макрос, следует:

1. Войти во вкладку «разработчик».
2. Выбрать запись макроса.
3. Выбрать имя макроса (в имени нельзя использовать пробелы и дефисы);
4. Можно выбрать сочетание клавиш, при нажатии которых будет начинаться запись макроса;
5. Выбрать место сохранения:

— при сохранении в «Эта книга» макрос будет работать только в текущем документе;

— при сохранении в «Личная книга» макрос будет работать во всех документах на Вашем компьютере.

1. Можно добавить описание макроса, оно поможет Вам вспомнить, какие действия совершает макрос.
2. Нажать «Ок».
3. Если вы не указали сочетание клавиш, запись начнется сразу после нажатия кнопки «Ок».
4. Когда идет запись, Вы должны совершать требуемую последовательность действий.
5. Когда закончите, нажимайте кнопку остановить запись.

Записанные макросы отображаются в книге макросов.

Чтобы их посмотреть следует нажать кнопку «макросы». В появившемся окне появится список макросов. Выберете нужный макрос и нажмите «Выполнить».

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

Отображение вкладки “Разработчик” в ленте меню

Перед тем как записывать макрос, нужно добавить на ленту меню Excel вкладку “Разработчик”. Для этого выполните следующие шаги:

1. Щелкните правой кнопкой мыши по любой из существующих вкладок на ленте и нажмите «Настроить ленту». Он откроет диалоговое окно «Параметры Excel».
2. В диалоговом окне «Параметры Excel» у вас будут параметры «Настроить ленту». Справа на панели «Основные вкладки» установите флажок «Разработчик».
3. Нажмите «ОК».

В результате на ленте меню появится вкладка “Разработчик”

Абсолютная и относительная запись макроса

Вы уже знаете про абсолютные и относительные ссылки в Excel? Если вы используете абсолютную ссылку для записи макроса, код VBA всегда будет ссылаться на те же ячейки, которые вы использовали. Например, если вы выберете ячейку A2 и введете текст “Excel”, то каждый раз – независимо от того, где вы находитесь на листе и независимо от того, какая ячейка выбрана, ваш код будет вводить текст “Excel” в ячейку A2.

Если вы используете параметр относительной ссылки для записи макроса, VBA не будет привязываться к конкретному адресу ячейки. В этом случае программа будет “двигаться” относительно активной ячейки. Например, предположим, что вы уже выбрали ячейку A1, и вы начинаете запись макроса в режиме относительной ссылки. Теперь вы выбираете ячейку A2, вводите текст Excel и нажмите клавишу Enter. Теперь, если вы запустите этот макрос, он не вернется в ячейку A2, вместо этого он будет перемещаться относительно активной ячейки. Например, если выбрана ячейка B3, она переместится на B4, запишет текст “Excel” и затем перейдет к ячейке K5.

Теперь давайте запишем макрос в режиме относительных ссылок:

1. Выберите ячейку A1.
2. Перейдите на вкладку “Разработчик”.
3. В группе “Код” нажмите кнопку “Относительные ссылки”. Он будет подсвечиваться, указывая, что он включен.
   
4. Нажмите кнопку “Запись макроса”.
   
5. В диалоговом окне “Запись макроса” введите имя для своего макроса. Например, имя “ОтносительныеСсылки”.
   
6. В опции “Сохранить в” выберите “Эта книга”.
7. Нажмите “ОК”.
8. Выберите ячейку A2.
9. Введите текст “Excel” (или другой как вам нравится).
10. Нажмите клавишу Enter. Курсор переместиться в ячейку A3.
11. Нажмите кнопку “Остановить запись” на вкладке “Разработчик”.

Макрос в режиме относительных ссылок будет сохранен.

Теперь сделайте следующее.

1. Выберите любую ячейку (кроме A1).
2. Перейдите на вкладку “Разработчик”.
3. В группе “Код” нажмите кнопку “Макросы”.
4. В диалоговом окне “Макрос” кликните на сохраненный макрос “ОтносительныеСсылки”.
5. Нажмите кнопку “Выполнить”.

Как вы заметите, макрос записал текст “Excel” не в ячейки A2. Это произошло, потому что вы записали макрос в режиме относительной ссылки. Таким образом, курсор перемещается относительно активной ячейки. Например, если вы сделаете это, когда выбрана ячейка B3, она войдет в текст Excel – ячейка B4 и в конечном итоге выберет ячейку B5.

Вот код, который записал макрорекодер:

Обратите внимание, что в коде нет ссылок на ячейки B3 или B4. Макрос использует Activecell для ссылки на текущую ячейку и смещение относительно этой ячейки.

Не обращайте внимание на часть кода Range(«A1»). Это один из тех случаев, когда макрорекодер добавляет ненужный код, который не имеет никакой цели и может быть удален. Без него код будет работать отлично.

Расширение файлов Excel, которые содержат макросы

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

До Excel 2007 был достаточен один формат файла – .xls. Но с 2007 года .xlsx был представлен как стандартное расширение файла. Файлы, сохраненные как .xlsx, не могут содержать в себе макрос. Поэтому, если у вас есть файл с расширением .xlsx, и вы записываете / записываете макрос и сохраняете его, он будет предупреждать вас о сохранении его в формате с поддержкой макросов и покажет вам следующее диалоговое окно:

Если вы выберете “Нет”, Excel сохранить файл в формате с поддержкой макросов. Но если вы нажмете “Да”, Excel автоматически удалит весь код из вашей книги и сохранит файл как книгу в формате .xlsx. Поэтому, если в вашей книге есть макрос, вам нужно сохранить его в формате .xlsm, чтобы сохранить этот макрос.

Что нельзя сделать с помощью макрорекодера?

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

• Вы не можете выполнить код без выбора объекта. Например, если вы хотите, чтобы макрос перешел на следующий рабочий лист и выделил все заполненные ячейки в столбце A, не выходя из текущей рабочей таблицы, макрорекодер не сможет этого сделать. В таких случаях вам нужно вручную редактировать код.
• Вы не можете создать пользовательскую функцию с помощью макрорекордера. С помощью VBA вы можете создавать пользовательские функции, которые можно использовать на рабочем листе в качестве обычных функций.
• Вы не можете создавать циклы с помощью макрорекордера. Но можете записать одно действие, а цикл добавить вручную в редакторе кода.
• Вы не можете анализировать условия: вы можете проверить условия в коде с помощью макрорекордера. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и запуска кода, если true (или другой код, если false).

Редактор Visual Basic

В Excel есть встроенный редактор Visual Basic , который хранит код макроса и взаимодействует с книгой Excel. Редактор Visual Basic выделяет ошибки в синтаксисе языка программирования и предоставляет инструменты отладки для отслеживания работы и обнаружения ошибок в коде, помогая таким образом разработчику при написании кода.

Запускаем выполнение макроса

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

1. В той же вкладке (“Разработчик”) и группе “Код” нажимаем кнопку “Макросы” (также можно воспользоваться горячими клавишами Alt+F8).
2. В отобразившемся окошке выбираем наш макрос и жмем по команде “Выполнить”. Примечание: Есть более простой вариант запустить выполнение макроса – воспользоваться сочетанием клавиш, которое мы задали при создании макроса.
3. Результатом проверки будет повторение ранее выполненных (записанных) действий.

Корректируем макрос

Созданный макрос можно изменить. Самая распространенная причина, которая приводит к такой необходимости – сделанные при записи ошибки. Вот как можно отредактировать макрос:

1. Нажимаем кнопку “Макросы” (или комбинацию Ctrl+F8).
2. В появившемся окошке выбираем наш макрос и щелкаем “Изменить”.
3. На экране отобразится окно редактора “Microsoft Visual Basic”, в котором мы можем внести правки. Структура каждого макроса следующая:
   • открывается с команды “Sub”, закрывается – “End Sub”;
   • после “Sub” отображается имя макроса;
   • далее указано описание (если оно есть) и назначенная комбинация клавиш;
   • команда “Range(“…”).Select” возвращает номер ячейки. К примеру, “Range(“B2″).Select” отбирает ячейку B2.
   • В строке “ActiveCell.FormulaR1C1” указывается значение ячейки или действие в формуле.
4. Давайте попробуем скорректировать макрос, а именно, добавить в него ячейку B4 со значением 3. В код макроса нужно добавить следующие строки:
   Range(«B4»).Select
   ActiveCell.FormulaR1C1 = «3»
5. Для результирующей ячейки D2, соответственно, тоже нужно изменить начальное выражение на следующее:
   ActiveCell.FormulaR1C1 = «=RC[-2]*R[1]C[-2]*R[2]C[-2]» . Примечание: Обратите внимание, что адреса ячеек в данной строке (ActiveCell.FormulaR1C1) пишутся в стиле R1C1.
6. Когда все готово, редактор можно закрывать (просто щелкаем на крестик в правом верхнем углу окна).
7. Запускаем выполнение измененного макроса, после чего можем заметить, что в таблице появилась новая заполненная ячейка (B4 со значением “3”), а также, пересчитан результат с учетом измененной формулы.
8. Если мы имеем дело с большим макросом, на выполнение которого может потребоваться немало времени, ручное редактирование изменений поможет быстрее справиться с задачей.
   • Добавив в конце команду Application.ScreenUpdating = False мы можем ускорить работу, так как во время выполнения макроса, изменения на экране отображаться не будут.
   • Если потребуется снова вернуть отображение на экране, пишем команду: Application.ScreenUpdating = True .
9. Чтобы не нагружать программу пересчетом после каждого внесенного изменения, в самом начале пишем команду Application.Calculation = xlCalculationManual , а в конце – Application.Calculation = xlCalculationAutomatic . Теперь вычисление будет выполняться только один раз.