Вычислить четвертый столбец как отношение элементов второго столбца к третьему в эксель

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

Описание

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

Создание образца листа

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

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

Определения терминов

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

Определение

Вся таблица подстановки

Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».

Просматриваемый_массив
-или-
Лукуп_вектор

Диапазон ячеек, которые содержат возможные значения подстановки.

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

3 (третий столбец в инфо_таблица)

Ресулт_аррай
-или-
Ресулт_вектор

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

Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.

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

Функции

LOOKUP ()

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

Ниже приведен пример синтаксиса формулы подСТАНОВКи.

= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)

Следующая формула находит возраст Марии на листе "образец".

= ПРОСМОТР (E2; A2: A5; C2: C5)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как "Мария" находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).

Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.

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

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

Ниже приведен пример синтаксиса формулы ВПР :

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

Следующая формула находит возраст Марии на листе "образец".

= ВПР (E2; A2: C5; 3; ЛОЖЬ)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется "3" в качестве Колумн_индекс (столбец C). Так как "Мария" находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).

Чтобы получить дополнительные сведения о функции ВПР , щелкните следующий номер статьи базы знаний Майкрософт:

INDEX () и MATCH ()

Вы можете использовать функции индекс и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании поиска или функции ВПР.

Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:

= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)

Следующая формула находит возраст Марии на листе "образец".

= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как "Мария" находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).

СМЕЩ () и MATCH ()

Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.

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

= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)

Эта формула находит возраст Марии на листе "образец".

= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как "Мария" находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).

Чтобы получить дополнительные сведения о функции СМЕЩ , щелкните следующий номер статьи базы знаний Майкрософт:

Как быстро суммировать каждую вторую или n-ю строку / столбец в Excel?

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

To sum, average or count every other or nth row or column, Kutools for Excel's Select Interval Rows & Columns feature may help you to select every other or nth row firstly, and then get the calculations at the bottom of the status bar. Read more. Free Dowload.

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!

Суммируйте каждую вторую или n-ю строку / столбец с формулами

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

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

В пустой ячейке введите эту формулу: =SUM(IF(MOD(ROW($B$1:$B$15),2)=0,$B$1:$B$15,0)), затем нажмите Shift + Ctrl + Enter ключи и значения всех остальных ячеек в столбце B были суммированы. Смотрите скриншоты:

Ноты:

1. В приведенной выше формуле массива вы можете изменить число 2 на 3, 4, 5…, это означает суммировать каждую 3-ю строку, каждую 4-ю строку, каждую 5-ю строку .

2. Если вы хотите просуммировать каждый второй столбец, вы можете ввести следующую формулу: =SUM(IF(MOD(COLUMN($A$1:$O$1),2)=0,$A$1:$O$1,0)) , и нажмите Shift + Ctrl + Enter ключи, чтобы получить результат. Смотрите скриншот:

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

Вот еще одна формула, которая поможет вам суммировать каждую вторую или n-ю ячейку на листе.

В пустой ячейке введите эту формулу: =SUMPRODUCT((MOD(ROW($B$1:$B$15),3)=0)*($B$1:$B$15)) , Затем нажмите Enter key, и каждая третья ячейка была добавлена. Смотрите скриншоты:

Ноты:

1. В приведенной выше формуле вы можете изменить число 3 на 4, 5, 6…, это означает суммировать каждую 4-ю строку, каждую 5-ю строку, каждую 6-ю строку .

2. Если вы хотите просуммировать каждый второй столбец, вы можете ввести следующую формулу: =SUMPRODUCT((MOD(COLUMN($A$1:$O$1),3)=0)*($A$1:$O$1)).

Суммируйте каждую вторую или n-ю строку / столбец с помощью функции, определяемой пользователем

Следующая определяемая пользователем функция также может помочь вам добавить каждую вторую или n-ю ячейку в Excel.

1. Удерживайте ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

3. Затем сохраните этот код и введите следующую формулу в пустую ячейку, как вам нужно, см. Снимки экрана:

Для суммирования каждой четвертой строки: = SumIntervalRows (B1: B15,4)

Для суммирования каждого четвертого столбца: = SumIntervalCols (A1: O1,4)

4, Затем нажмите Enter ключ, вы получите расчет. Смотрите скриншоты:

Суммируйте каждую четвертую строку:

Суммируйте каждый четвертый столбец:

Внимание: Вы можете изменить число 4 на любые другие числа, такие как 2, 3, 5… это означает суммирование каждой второй строки, каждой третьей строки, каждой пятой строки или столбца.

Сумма / среднее / подсчет каждой другой или n-й строки / столбца с помощью Kutools for Excel

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

Работы С Нами Kutools for ExcelАвтора Выберите интервальные строки и столбцы, вы можете сначала выбрать любую другую или n-ю ячейку, которая вам нужна, а затем дать выбранным ячейкам определенное имя, наконец, используя функцию Sum для суммирования ячеек.

После установки Kutools for Excel, выполните следующие действия:

1. Выберите столбец, в котором вы хотите суммировать каждую вторую или n-ю ячейку.

2. Нажмите Кутулс > Выберите > Выберите интервальные строки и столбцы, см. снимок экрана:

3. В Выберите интервальные строки и столбцы диалоговом окне укажите необходимые операции, см. снимок экрана:

4. Нажмите OK, и каждая вторая строка была выбрана из первой строки, и теперь вы можете просматривать вычисленные результаты, такие как среднее значение, сумма, количество, которые отображаются в нижней части строки состояния. Смотрите скриншот:

Ноты:

1. С помощью этой функции вы можете установить число 2, 3, 4… в Интервал вариант, и вы выберете ячейки с интервалом в 2, 3, 4 строки, начиная с первой строки диапазона на шаге 3.

2. С помощью описанных выше шагов вы также можете суммировать каждый второй или n-й столбец по мере необходимости.

Если вы хотите узнать больше об этой функции, нажмите Выберите интервальные строки и столбцы.

Демо: сумма / среднее / подсчет каждой второй или n-й строки / столбца с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Подсчет совпадений между двумя столбцами в Excel

Например, у меня есть два списка данных в столбце A и столбце C, теперь я хочу сравнить два столбца и подсчитать, найдено ли значение в столбце A в столбце C в той же строке, что и на скриншоте ниже. В этом случае функция СУММПРОИЗВ может быть лучшей функцией для решения этой задачи в Excel.

Подсчитайте совпадения между двумя столбцами с помощью функции СУММПРОИЗВ

Чтобы сравнить два столбца и подсчитать совпадения в каждой строке, функция СУММПРОИЗВ в excel может оказать вам услугу. Общий синтаксис:

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

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

Пояснение к формуле:

= СУММПРОИЗВ (- (A2: A12 = C2: C12)):

A2: A12 = C2: C12 : Это выражение сравнивает значения в диапазоне A2: A12 со значениями в диапазоне C2: C12 для каждой строки, если два значения в одной строке равны, будет отображаться ИСТИНА, в противном случае отображается ЛОЖЬ, поэтому вы получите такой массив: .
- (A2: A12 = C2: C12) = - (): : - этот двойной отрицательный знак преобразует ИСТИНА и ЛОЖЬ в 1 и 0, возвращает следующий результат: .
SUMPRODUCT(--(A2:A12=C2:C12))= SUMPRODUCT(): : Функция СУММПРОИЗВ суммирует все значения в массиве и получает результат: 4.

Советы:

Если вам нужно подсчитать несовпадающие значения между двумя столбцами, примените эту формулу:

Функция ВПР может помочь вам сравнить два столбца и извлечь соответствующие значения из третьего столбца, пожалуйста, сделайте следующее:

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

Примечание: В приведенных выше формулах: D2 это ячейка критериев, значение которой вы хотите вернуть, A2: A16 столбец, содержащий критерии для сравнения, A2: B16 диапазон данных, который вы хотите использовать.

2, Затем нажмите Enter чтобы получить первое соответствующее значение, а затем выберите ячейку формулы и перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите применить к этой формуле, и все соответствующие значения были возвращены сразу, см. снимок экрана:

Сравните два столбца и возвращаемое значение из третьего столбца с функциями ИНДЕКС и ПОИСКПОЗ

В Excel функции ИНДЕКС и ПОИСКПОЗ также могут помочь вам решить эту задачу, пожалуйста, сделайте следующее:

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

Примечание: В приведенных выше формулах: D2 это значение, относительная информация которого вы хотите вернуть, A2: A16 это список, содержащий значение, которое вы хотите вернуть, B2: B16 столбец, который вы ищете.

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

Если вас интересует функция ВПР в Excel, Kutools for Excel's Супер ПОСМОТРЕТЬ поддерживает несколько мощных формул Vlookup, ты можешь быстро выполнить функцию Vlookup, не запоминая никаких формул. Нажмите, чтобы загрузить Kutools for Excel!

Vlookup несколько столбцов и возврат соответствующих значений с помощью функций ИНДЕКС и ПОИСКПОЗ

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

Чтобы завершить эту работу, примените следующую формулу:

Примечание: В приведенных выше формулах: E2, F2 - это ячейки критериев, на основе которых вы хотите вернуть значение, C2: C16 столбец, содержащий значения, которые вы хотите вернуть, A2: A16, B2: B16 столбцы, которые вы ищете.

Затем нажмите Shift + Ctrl + Enter ключи вместе, чтобы получить первый результат, см. снимок экрана

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

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

Kutools for ExcelАвтора Найдите значение в списке также может помочь вам вернуть соответствующие данные из другого диапазона данных.

Примечание: Чтобы применить это Найдите значение в списке, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Щелкните ячейку, в которую вы хотите поместить совпавший результат.

2. Затем нажмите Кутулс > Формула Помощник > Формула Помощник, см. снимок экрана:

3. В Помощник по формулам диалоговом окне выполните следующие действия:

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

4. Затем нажмите Ok, и были возвращены первые совпавшие данные на основе определенного значения. Вам просто нужно перетащить маркер заполнения, чтобы применить эту формулу к другим нужным ячейкам, см. Снимок экрана:

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

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

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

Создание вычисляемого столбца

Создайте таблицу. Если вы не знакомы с таблицами Excel, см. статью Общие сведения о таблицах Excel.

Вставьте в таблицу новый столбец. Введите данные в столбец справа от таблицы, и Excel автоматически расширит ее. В этом примере мы создали новый столбец, введя "Итог" в ячейке D1.

Вы также можете добавить столбец на вкладке Главная. Просто щелкните стрелку на кнопке Вставить и выберите команду Вставить столбцы таблицы слева.

Введите нужную формулу и нажмите клавишу ВВОД.

В этом случае мы ввели =СУММ(, а затем выбрали столбцы Кв1 и Кв2. В результате Excel создал следующую формулу: =СУММ(Таблица1[@[Кв1]:[Кв2]]). Такие формулы называются формулами со структурированными ссылками, и их можно использовать только в таблицах Excel. Структурированные ссылки позволяют использовать одну и ту же формулу в каждой строке. Обычная формула Excel выглядела бы как =СУММ(B2:C2), и ее было бы необходимо добавить в остальные ячейки путем копирования и вставки или заполнения.

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

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

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

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

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

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

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

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

При вводе в ячейку вычисляемого столбца данных, отличных от формулы.

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

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

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

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

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

Примечание: В этом случае исключение не помечается.

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

Если вы используете Mac, в строке меню Excel выберите Параметры > Формулы и списки > Поиск ошибок.

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

Включение и выключение вычисляемых столбцов

На вкладке Файл нажмите кнопку Параметры.

Выберите категорию Правописание.

В разделе Параметры автозамены нажмите кнопку Параметры автозамены

Откройте вкладку Автоформат при вводе.

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

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

Прекращение автоматического создания вычисляемых столбцов

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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