Как в экселе формулу применить к всему столбцу: Как быстро применить формулу ко всему столбцу или строке с / без перетаскивания в Excel?

Как вычесть процент от числа в Эксель

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

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

Содержание

• Вычитаем проценты от числа
• Вычитаем проценты в заполненной таблице
• Вычитаем проценты в таблице с зафиксированным %
• Заключение

Вычитаем проценты от числа

Смотрите также: “Как скрыть лист в Excel”

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

В Эксель данное математическое действие выглядит следующим образом:

= Цифра (ячейка) – Цифра (ячейка) * Процент (%).

Например, вычитание 23% из числа 56 пишется следующим образом: 56-56*23%.

Введя свои значения в любую свободную ячейку таблицы, достаточно кликнуть по клавише “Enter”, и готовый результат появится в выбранной ячейке.

Вычитаем проценты в заполненной таблице

Но что же делать, если данные уже занесены в таблицу, и расчет вручную займет много времени и сил?

1. Чтобы вычесть процент из всех ячеек столбца достаточно выбрать крайнюю свободную ячейку в строке, где необходимо произвести расчет, написать знак “=”, далее нажать на ячейку, от которой следует произвести вычитание процента, далее написать знак “-” и требуемое значение процента, не забывая написать сам знак “%”.

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

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

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

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

   Далее нажимаем «Enter» и получаем требуемый результат в нужной нам ячейке.

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

Вычитаем проценты в таблице с зафиксированным %

Смотрите также: “Как в Экселе закрепить столбец таблицы”

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

В данном случае, формула расчета будет выглядеть так (на примере ячейки G2):

Примечание: Знаки “$” можно прописать вручную, либо наведя в формуле курсор на ячейку с процентами нажать клавишу “F4”. Таким образом, вы зафиксируете ячейку с процентами, и она не будет меняться при растягивании формулы вниз на другие строки.

Далее нажмите “Enter”, и результат будет посчитан.

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

Заключение

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

Смотрите также: “Как поставить тире в Экселе: длинное или короткое”

Массивы в Excel пример использования

Массивы в Excel пример использования

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

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

Имеется таблица.

Как обычно мы действуем? Вводим формулу — количество * цену, плюсуем доставку, enter, протягиваем за маркер автозаполнения вниз, получаем результат. Способ классический, рабочий.

Давайте выполним те же самые действия, только умножать и прибавлять будем не ячейки, а целые диапазоны. Предварительно выделив диапазон от F3 до F11.

Первый нюанс, когда работаем с формулой массива нельзя нажимать Enter! Нажимаем Ctrl+Shift+Enter! Что это даст в итоге.  

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

Комбинации формул массива и обычных формул

Усложняем задачу — нужно подвести итог (СУММ) по всему столбцу «ИТОГО», высчитать максимальную сумму заказа и узнать среднюю сумму заказа без копеек.

Аналогично воспользуемся функцией СУММ, только передадим ей опять массив в виде вычислений. Не забываем про Ctrl+Shift+Enter!

Взглянем в строчку формул.

Посмотрим, что в этому случае делает программа Excel — пройдём по шагам вычислений. На вкладке «Формулы» нажмём кнопку «Вычислить формулу».

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

Аналогично подсчитаем максимальную стоимость покупки. Используем формулу МАКС и передадим ей массив.

Настала очередь средней суммы покупки. Для округления будем использовать ЦЕЛОЕ (чтобы результат был без копеек) и СРЗНАЧ.

Готово. В этом случае даже получилось «экономнее» нежели в обычных формулах, так как для промежуточного итога нет своего столбца!

Как видно их примеров — у массивов есть свои плюсы и свои минусы. Не пробуйте удалять массив (если он содержится в диапазоне ячеек). Изменить только одну ячейку массива нельзя! Плюс — улучшаем безопасность, минус — приходится переделывать весь массив, если что-то пошло не так.

Если остались вопросы — посмотрите новое видео. Ну и по традиции — вам может понравиться статья про функцию ПЛТ. Всем удачи! 

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

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

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

Например, обратите внимание на две приведенные ниже формулы ЕСЛИ. Первая представляет собой обычную формулу ЕСЛИ, которая относится к одной ячейке, а вторая формула использует «массив» и применяется к нескольким ячейкам.

=ЕСЛИ(A1=1,1,0)
=ЕСЛИ(A1:A100=1,1,0)

Ctrl + Shift + Enter Version (Для более старых версий Excel):

{=ЕСЛИ( A1:A100=1,1,0)}

В этой статье показано, как использовать формулы массива в Excel, но нажмите здесь, если вы хотите узнать, как использовать массивы / ФОРМУЛА МАССИВА в Google Таблицах.

Динамические массивы и формулы CSE (Ctrl + Shift + Enter)

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

Пример формулы динамического массива выглядит так: =A1:A100

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

Пример формулы СПП выглядит следующим образом: {=A1:A100 }

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

Вот различные способы использования формул массива:

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

• =A2:A1001
• {=A2:A1001}

900 54

См.

ряд

• =B1 :V1
• {=B1:V1}

Сумма/умножение столбцов

• =A3:A1001+B3:B1001
• {=A3:A1001+B3:B1001}
• =A3:A1001*B3:B1001
• {=A3:A1001*B3:B1001}

Функция ЕСЛИ с массивом

• =ЕСЛИ(B3:B1001>0,6,»Удачно»,»Неудачно»)
• {=ЕСЛИ(B3:B1001>0,6,»Удачно» ing», «Ошибка «)}

Объединить имя и фамилию

• =B3:B1001&», «&A3:A1001
• {=B3:B1001&», «&A3:A100 1}

Суммируйте целые таблицы данные

• =A3:B1001+D3:E1001+G3:h2001
• {=A3:B1001+D3:E1001+G3:h2001}

Извлечение данных из другого листа

• =Список!A3:B1001
• {=Список!A3:B1001}

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

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

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

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

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

Задача: Показать список имен в другом столбце

Логика: В столбце C обратиться к списку имен, которые находятся в столбце A с формулой массива

Формула: Формула ниже, вводится в синюю ячейку (C2), для этого примера

=A2:A1001
{=A2:A1001}

Ссылка на строку с формулой массива

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

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

Задача: Показать список дат в другой строке

Логика: В строке 3 обратиться к списку дат, которые находятся в строке 1 с формулой массива

Формула: Формула ниже, вводится в синюю ячейку (B3), для этого примера

=B1:V1
{=B1:V1}

Автор SpreadsheetClass.com

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

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

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

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

Столбцы A и B представляют собой списки чисел… а столбцы D и E добавляют/умножают эти числа, расширяя формулы сложения и умножения вниз по столбцу с помощью массива.

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

Задача: Применить формулы сложения и умножения ко всему столбцу

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

Формула: Приведенные ниже формулы вводятся изначально в ячейки D3 и E3 (синие ячейки), для этого пример

=A3:A1001+B3:B1001 (вводится в ячейку D3)
{=A3:A1001+B3:B1001}

=A3:A1001*B3:B1001 9 0006 (вводится в ячейку E3)
{=A3:A1001*B3:B1001}

Использование массива для расширения функции ЕСЛИ

Вот еще один пример, где мы применим формулу ко всему столбцу в Excel, но на этот раз мы будем использовать функцию ЕСЛИ.

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

На приведенном ниже примере в столбцах A и B находится список имен учащихся и их оценок. Формула в столбце C использует функцию ЕСЛИ для отображения «Успешно», если оценка учащегося выше 60% (0,6), и отображает «Неудовлетворительно», если оценка не выше 60%. Но самое важное, что следует отметить в этом примере, это то, что функция ЕСЛИ была применена к столбцу с помощью формулы массива.

Задача: Применить формулу ЕСЛИ ко всему столбцу

Логика: Превратить формулу ЕСЛИ в формулу массива и указать весь столбец как диапазон

Формула: Формула ниже, вводится в синюю ячейку (C3), для этого примера

=ЕСЛИ(B3:B1001>0,6,»Успешно»,»Неудачно»)
{=ЕСЛИ(B3:B1001>0,6,»Успешно»,»Неудачно») «)}

Как объединить имя и фамилию с формулой массива

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

При использовании формулы массива для объединения ячеек/столбцов необходимо использовать оператор «&» между каждым из указанных значений/ссылок (показано в примере).

На приведенном ниже примере в столбце A указаны имена, а в столбце B — фамилии.

Используя функцию формулы массива с оператором «&», эти имена объединяются в столбец D.

Задача: Объединить имя и фамилию в формате «Фамилия, Имя»

Логика: Объедините столбцы A и B по горизонтали с запятой и пробелом между ними, используя формулу массива и оператор «&»

Формула: Формула ниже вводится в синюю ячейку (D3), для этот пример

=B3:B1001&», «&A3:A1001
{=B3:B1001&», «&A3:A1001}

Как суммировать целые таблицы данных с помощью массива формула

Ранее I показал вам, как суммировать столбцы данных с помощью формулы массива, но вы также можете использовать эту функцию для суммирования данных по всем таблицам данных.

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

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

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

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

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

Формула: Приведенная ниже формула вводится в синюю ячейку (J3), для этого примера

=A3:B1001+D3:E1001+G3:h2001
{=A3:B1001 +D3 :E1001+G3:h2001}

Этот контент был первоначально создан и написан SpreadsheetClass. com

Извлечение данных с другого листа с помощью формулы массива

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

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

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

Как и в большинстве формул, при использовании массива для ссылки на данные с другой вкладки введите имя вкладки, из которой вы извлекаете данные, восклицательный знак и ссылку на строку/столбец.

Помните, что если в имени вкладки есть пробел, вы должны ввести апострофы до и после имени вкладки (пример: «Имя вкладки»!A3:B). Однако в этом примере имя вкладки, которое мы будем использовать, называется «Список», поэтому нам не нужны апострофы.

Задача: Отобразить данные, находящиеся в столбцах A и B на вкладке с именем «Список», в столбцах A и B на новой/другой вкладке

Логика: В новой вкладке, отображать данные из столбцов A и B из вкладки «Список», обращаясь к данным массивом

Формула: Приведенная ниже формула вводится в синюю ячейку (A3), для этого примера

=Список!A3:B1001
{=Список!A3:B1001} 9000 6

Источник данные из вкладки «Список»:

Отдельная вкладка, отображающая ссылочные данные/массив:

Вспышка: проверьте свои знания

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

Нажмите зеленую кнопку «Печать» ниже, чтобы напечатать всю эту статью.

Вопрос №1

Какая из следующих формул относится к столбцу данных?

1. =A12:Z12
2. =G1:G1000
3. =B1

Вопрос № 2

Какая из следующих формул относится к ряду данных?

1. =J3
2. =U1:U1000
3. =A9:Z9

Вопрос №3

поскольку формула относится к ячейке, вместо диапазона ячеек:

=ЕСЛИ(D1=100,»Идеально»,»Не идеально»)

1. Верно
2. Ложно

Вопрос #4

Какой из следующих формул относится к диапазону на другом листе?

1. =A1:Z1000
2. =Значения!A3:B1000

Вопрос №5

Какие из следующих формул относятся к диапазону с несколькими столбцами? (Выберите все подходящие варианты)

1. =Значения!A:B
2. =A:A
3. =C:B
4. =List!G:G
5. =Y:Z

Вопрос № 6

Какая из следующих формул будет применять формулу умножения ко всему столбцу?

1. =A3:AxB3:B
2. =A3:A*B3:B
3. =A3:A+B3:B
4. =A3:A(B3:B)

Правда или ложь: в некоторых версиях Excel, чтобы использовать формулу массива, вам нужно удерживать Ctrl + Shift + Enter на клавиатуре, чтобы ввести формулу массива.

1. Верно
2. Ложно

Ответы на вопросы выше:

Вопрос 1: 2

Вопрос 2: 3

Вопрос 3: 1

Вопрос 4: 2

Вопрос 5: 1, 3, 5

Вопрос 6: 2

Вопрос 7: 1

Необходимо применить формулу ко всему столбцу или строке в Excel 900 01

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

Теперь предположим, что вы хотите применить формулу A1*6-5 в столбце B. Обычно есть два способа реализовать эту цель: перетаскивание или не перетаскивание маркера автозаполнения.

Часть 1. Перетаскивание маркера автозаполнения для применения формулы ко всему столбцу или строке в Excel

Шаг 1: Ввод формулы в первую ячейку столбца B.

Сначала мы вводим формулу в ячейку B1 .

Шаг 2: Перетащите маркер автозаполнения в нижнюю часть целевой части в столбце B.

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

Внимание : Этот метод перетаскивания маркера автозаполнения требует автоматического вычисления формулы. Если вы обнаружите, что функция автоматического расчета формулы не включена, вы можете включить ее, щелкнув Формулы > Параметры расчета Автоматически .

Часть 2. Применение формулы ко всему столбцу или строке в Excel без перетаскивания маркера автозаполнения

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

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

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

Например, вы должны применить формулу =(A1*3+8)/5 ко всему столбцу B. Сначала вы должны ввести формулу в ячейку B1, затем выбрать весь столбец B, а затем нажать Главная > Заполнить > Вниз .