Как Работать с Формулами в Excel: Копирование, Вставка и Автозаполнение
Электронные таблицы предназначены не только для финансовых специалистов и бухгалтеров; они также пригодятся фрилансерам или владельцам малого бизнеса, таким как вы. Электронные таблицы могут помочь вам выделить основные данные, касающиеся вашего бизнеса, понять какие продукты лучше продаются и организовать вашу жизнь.
В целом, предназначение Excel — сделать вашу жизнь легче. Этот урок, поможет вам сформировать базовые навыки по работе с ними. Формулы — это сердце Excel.
Тут перечислены ключевые навыки, которые вы получите в результате этого урока:
- Вы узнаете как написать вашу первую формулу в Microsoft Excel, чтобы автоматизировать математические вычисления.
- Как копировать и вставить рабочую формулу в другую ячейку.
- Как использовать автозаполнение, что бы быстро добавить формулу для целого столбца.
В электронной таблице ниже, вы можете видеть, почему Excel является таким мощным инструментом. Верхняя часть скриншота показывает формулы, которые используются в работе, а нижняя часть скриншота, показывает результаты работы этих формул.
Как в excel поставить формулу на весь столбец. Использование вычисляемых столбцов в таблице Excel Online
- =СРЗНАЧ — средние значения.
- =СЧЁТ подсчитывает число ячеек, которые содержат числовые данные.
- =ДЕНЬ возвращает значение для сегодняшнего числа.
- =СЖПРОБЕЛЫ удаляет лишние пробелы в строке, кроме пробелов между словами.
Быстро сложим / разделим/ умножим числовые значения из диапазона ячеек на заданное пользователем число. Этот подход позволяет уменьшить или увеличить разрядность чисел в выбранном диапазоне, быстро выделить НДС и пр.
Как сложить листы в Excel?
Вариант 1. Ручной ввод.
Начнем с одной стороны с медленного варианта, но с другой стороны с более понятного и наглядного способа.
Для примера возьмем простую задачу и сложим данные ячейки A1 с трех листов: Лист1, Лист2 и Лист3. Для этого поочередно в формулу записываем ссылки на ячейку с разных листов и знаки сложения «+»:
Это совсем простой вариант. Еще один вариант записи можно аналогично реализовать с помощью функции СУММ:
В реализуемых нами вариантах в общем и целом все хорошо, за исключением большой ручной работы при прощелкивании всех нужных для суммирования ячеек.
Как это часто бывает если нужно посчитать сумму для двух или трех листов, то вышеописанный способ отлично подойдет. Но если усложнить задачу и представить подобный пример не для 3, а, например, для 30 листов, то процесс уже несколько затянется.
Поэтому перейдем к чуть более сложному на первый взгляд варианту, но гораздо более быстрому с точки зрения трудовых и временных затрат.
Вариант 2. Полуавтоматический ввод.
Повторно воспользуемся функцией СУММ, но в этот раз запишем ссылку на листы через двоеточие:
В этом случае общее количество суммируемых листов для нас уже не так важно, главное знать только начальный и конечный.
Поэтому запись для сложения 10 или 20 листов будет примерно такой же — ИмяПервогоЛиста:ИмяПоследнегоЛиста!Диапазон.
При добавлении еще одной размерности в виде листов мы получаем так называемую трехмерную ссылку. К примеру, диапазон Лист1:Лист3!A1:A10 содержит все ячейки от Лист1!A1:A10 до Лист3!A1:A10.
Таким образом в нашем примере суммируются все ячейки A1 от Лист1 до Лист3. Но при применении такого способа суммирования есть некоторые тонкости, которые желательно нужно помнить, чтобы случайно не допустить ошибку, давайте подробнее про них поговорим.
Как посчитать сумму с разных листов в Excel?.
- Добавление/копирование листа. Если добавить новый лист или скопировать уже существующий и вставить его между первым и последним (используемых в формуле), то он автоматически будет участвовать в расчете.
Т.е. сама формула не поменяется, а значение вполне может измениться, если на новом листе в тех же ячейках содержатся данные; - Удаление листа. Аналогичный случай примеру выше. При удалении листа формула не изменится, а расчет поменяется в зависимости от удаленных данных (если удалить начальный или конечный листы, то формула изменится, промежуточный — формула останется неизменной);
- Перемещение листа. При перемещении листа в другое место книги формула не изменится, но поменяется набор листов между ними (либо добавятся новые, либо удалятся текущие).
Еще один способ представляет собой не совсем протягивание, а копирование. Но только более простое и позволяющее выделить конкретный диапазон, а не доверять такое выделение компьютеру. Процесс распределения требует выполнить следующие действия:
Функция Excel ЕСЛИМН
Функция Эксель ЕСЛИ в целом хорошо справляется со своими задачами. Но вариант, когда нужно записывать длинную цепочку условий не очень приятный, т.к., во-первых, написать с первого раза не всегда получается (то условие укажешь неверно, то скобку не закроешь); во-вторых, разобраться при необходимости в такой формуле может быть непросто, особенно, когда условий много, а сами расчеты сложные.
В MS Excel 2016 появилась функция ЕСЛИМН, ради которой и написана вся эта статья. Это та же ЕСЛИ, только заточенная специально для проверки множества условий. Теперь не нужно сто раз писать ЕСЛИ и считать открытые скобки. Достаточно перечислить условия и в конце закрыть одну скобку.
Работает следующим образом. Возьмем пример выше и воспользуемся новой формулой Excel ЕСЛИМН.
Как видно, запись формулы выглядит гораздо проще и понятнее.
Теперь вы знаете, как пользоваться функцией ЕСЛИ в Excel, а также ее более современным вариантом для множества условий ЕСЛИМН.
Как в excel продлить формулу на весь столбец — Все про Эксель
Для примера возьмем простую задачу и сложим данные ячейки A1 с трех листов: Лист1, Лист2 и Лист3. Для этого поочередно в формулу записываем ссылки на ячейку с разных листов и знаки сложения «+»:
3 Протягивание без изменения ячеек в формуле
Еще один способ позволяет распространять формулы в Excel без изменения некоторых аргументов. Это может понадобиться в тех случаях, когда одно или несколько значений будут содержаться в одной и той же ячейке. Поможет в закреплении формулы специальная функция фиксации ссылок.
Ставить знак $ перед названием только строки или столбца при распределении функции не имеет смысла. Потому что, когда формула протягивается, в ней автоматически меняются только нужные части аргументов. Для столбцов это будут номера строк, для строк — названия колонок.
Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий.
- В первую ячейку с одной из сторон (например, сверху) надо записать нужную формулу и нажать Enter.
- После появления рассчитанного по формуле значения навести курсор в нижний правый угол ячейки. Подождать, пока толстый белый крестик не превратиться в тонкий черный.
- Нажать на крестик и, удерживая его, протянуть формулу в нужном направлении. В указанном примере — вниз.
Чтобы скопировать формулу в ячейке D2 вниз по столбцу, дважды щелкните квадрата зеленый квадратик в правом нижнем углу ячейку D2. Чтобы получить результаты во всех остальных ячеек без копирования и вставки формулы или вводить.
Формула для прибавления процента в Эксель
Ранее мы уже выяснили, как сделать расчеты вручную. Теперь давайте рассмотрим, как произвести вычисления с данными, уже занесенными в таблицу.
Бывает так, что требуется посчитать таким образом все данные столбца. Для этого вовсе не нужно писать формулу для каждой новой строки.
Как в Экселе протянуть формулу по строке или столбцу: 5 способов | Спорт | Селдон Новости
- в любой пустой ячейке вводим 1000 (например, в D2 ) и нажимаем ENTER ;
- копируем в Буфер обмена значение ячейки D2 ;
- выделяем диапазон с ценами ( В2:В10 );
- идем в меню Главная/ Буфер обмена/ Вставить/ Специальная вставка ;
- выбираем Разделить в разделе Операция ;
- нажимаем ОК .
Мы выяснили, что прибавить процент к числу в программе Microsoft Excel не так уж сложно. Тем не менее, многие пользователи не знают, как это сделать, и допускают ошибки. Например, самой распространенной ошибкой является написание формулы по алгоритму «=(число)+(величина_процента)%», вместо «=(число)+(число)*(величина_процента)%». Данное руководство должно помочь не допускать подобных ошибок.
Умножение на процентное значение — Excel
Возможно после некоторого времени вы сотрудничали процентов в Excel. Или, возможно, что расчет процентов не ваших навыков избранные математические. В этой статье вы увидите, как он может быть намного проще Если позвольте Excel сделать всю работу.
Если вам нужно найти доли итогового или найти процент разности между двумя числами, ознакомьтесь с дополнительными в статью Вычисление процентных значений.
Умножение целого столбца чисел на процентное значение
Рассмотрим пример таблицы, как показано на рисунке, в котором у нас есть несколько чисел, чтобы умножить на 15%. Даже если столбец содержит 100 и 1000 ячеек с данными, Excel по-прежнему может обрабатывать его за несколько шагов.
Введите числа, которые нужно умножить на 15% в столбец.
В пустой ячейке введите процентное значение 15% (или 0,15), а затем скопируйте этот номер, нажав клавиши Ctrl + c.
Выделите диапазон ячеек a1: a5 (перетаскивая вниз по столбцу).
Щелкните правой кнопкой мыши на выделенную область ячеек и выберите команду Специальная вставка (выполните не щелкните стрелку рядом с Пунктом Специальная вставка).
Выберите пункт значения > умножение, нажмите кнопку ОК.
Совет: Можно также умножение столбца для вычитания процентного значения. Чтобы вычесть 15%, добавьте знак минус перед процент и вычитание процента от 1, используя формулу = 1 -n%, в какой n — процент. Чтобы вычесть 15%, используйте в качестве формулы = 1-15% .
Изменение суммы на заданное количество процентов
Допустим, вам нужно уменьшить — или определит — эти еженедельные затраты на 25%. Чтобы вычислить сумму, используйте формулу для вычитания или добавления процента.
В этом примере мы столбца B должен содержать в настоящее время, затраченное на сумму набора и столбец C содержит процент по которым, сократите. Вот представляет собой формулу можно ввести в ячейке D2 для выполнения этой задачи:
В этой формуле 1 совпадает с 100%. Значения в скобках вычислить во-первых, поэтому друг значение C2 вычитается от 1 до ждем 75%. Результат умножается B2, чтобы получить результат 56.25 для 1 недели.
Чтобы скопировать формулу в ячейке D2 вниз по столбцу, дважды щелкните квадрата зеленый квадратик в правом нижнем углу ячейку D2. Чтобы получить результаты во всех остальных ячеек без копирования и вставки формулы или вводить.
Чтобы увеличить на 25%, просто заменить + вход формула в ячейке D2 войдите минус (-):
Дополнительные сведения
Результатом будет такое же распределение функции по столбцу, как и при использовании способа №2. Но в отличие от него здесь можно выделить только часть диапазона. Или, наоборот, продлить такое протягивание дальше даже при наличии пустых строк. Правда, во втором случае лишнее значение придется удалить вручную.
Перевод формул в их текст (без сохранения результирующих значений)
Иногда требуется для демонстрационных целей преобразовать ячейки, оставив текст формул. В таких случаях полезной будет процедура замены.
Выделяем диапазон формул, жмем Ctrl+H («эйч», англ.), чтобы запустить диалоговое окно «Найти и заменить«, и в соответствующих окошках вбиваем значения.
Апостроф в результате замены не будет виден и они будут выглядеть идентично их написанию, но восприниматься формулы будут как текст.
В видимых ячейках, пропуская скрытые фильтром
Вот здесь при использовании стандартных решений уже могут возникнуть проблемы. Процедура вставки «как значения» даст сбой и повредит данные, если в копируемом диапазоне есть отфильтрованные строки.
Он отработает на отлично, как и та же процедура надстройки. В синтаксисе кода присутствует проверка ячеек на предмет того, скрыты ли они.
Удаляем формулы только в отфильтрованных строках, пропуская скрытые
Удалить формулы на листе / во всех листах
Когда нужно быстро избавиться от всех формул на листе или во всей книге, также могут помочь соответствующие простенькие процедуры, написанные на VBA:
Ими можно воспользоваться без внедрения в свои файлы, подключив надстройку !SEMTools.
Удаление формул надстройкой !SEMTools
Быструю замену формул на значения в выделенном диапазоне без необходимости копирования и особой вставки я реализовал в своей надстройке для Excel !SEMTools.
Для тех, кто не готов разбираться в VBA, после установки !SEMTools запуск процедуры доступен в 2 клика из соответствующего меню в группе «УДАЛИТЬ«. Функционал относится к бесплатным возможностям !SEMTools (работают без ограничений в любой версии).
Удалить формулы и сохранить значения в Excel с !SEMTools
Хотите так же быстро удалять формулы в Excel?
!SEMTools ускорит эту и сотни других задач!
Прибавление процента ко всему столбцу
- выделяем диапазон
- мышью, если это столбец, строка или лист
- сочетаниями Ctrl+Shift+стрелки или Shift+стрелки, если ячейки или диапазоны ячеек
- копируем сочетанием Ctrl+C
- перемещаемся стрелками к диапазону, куда нужно вставить данные и/или Ctrl+Tab для перехода в другую книгу
- вызываем контекстное меню клавишей на клавиатуре, иногда ее нет, но обычно на клавиатурах она есть, рядом с правым ALT
- Стрелками перемещаемся к команде (Вниз-вниз-вправо)
- Клавиша Enter
Ставить знак $ перед названием только строки или столбца при распределении функции не имеет смысла. Потому что, когда формула протягивается, в ней автоматически меняются только нужные части аргументов. Для столбцов это будут номера строк, для строк — названия колонок.
