Как использовать функцию Drop в Microsoft Excel

Одной из наиболее недостаточно используемых функций поиска и эталонных функций в Microsoft Excel является функция Drop. Эта мощная, но простая функция позволяет удалить указанное количество строк или столбцов с начала или конца массива без изменения исходного набора данных.

Синтаксис падения

Функция падения Excel имеет три аргумента:

=DROP(a,b,c)

где

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

Когда вы используете эту функцию, имейте в виду следующие критерии и характеристики:

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

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

• Если вы вводите число для аргументов беременный или в Это превышает количество строк или столбцов в массиве, вы увидите Calc! ошибка.

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

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

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

УПРАЖНЕНИЕ 1: Отбрасывайте ряды с вершины массива

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

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

=DROP(T_Stud,10)

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

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

Вместо этого вы можете ввести значение в ячейке и ссылаться на это в формуле:

=DROP(T_Stud,H1)

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

УПРАЖНЕНИЕ 2: Сбросьте ряды с нижней части массива

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

На этот раз, допустим, вы хотите показать всем учащимся не в нижней части 10. Чтобы сделать это, после сортировки данных в таблице под названием T_STUD2 в нисходящий порядок по столбцу с оценкой и печатать 10 в ячейку H1, тип:

=DROP(T_Stud2,-H1)

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

УПРАЖНЕНИЕ 3: Столбные столбцы слева от массива

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

=DROP(T_Stud3,,1)

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

УПРАЖНЕНИЕ 4: Столбные столбцы справа от массива

На этот раз ваша цель – составить список студентов, их пол и их класс из таблицы T_STUD4не включив их счет. Чтобы достичь этого, тип:

=DROP(T_Stud4,,-1)

Помните, что знак минус говорит Excel сбросить столбцы справа от данных. Это означает, что если вы Добавьте еще один столбец Данных справа от столбца оценки, новый столбец будет отброшен, и результат вернет четыре столбца в целом.

Упражнение 5: Drop Row и столбцы одновременно

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

Допустим, вы хотите составить список студенческих идентификаторов и их пол из таблицы T_STUD5но только те, кто не в нижней части 10. После сортировки столбца оценки по порядку убывания и печати 10 в ячейку H1, тип:

=DROP(T_Stud5,-H1,-2)

где -Х1 Поводит значение в ячейке H1 (10) отрицательно, чтобы удалить нижние 10 рядов, и -2 Удаляет два самых правых столбца.

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

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

УПРАЖНЕНИЕ 6: Сортировка и падение данных

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

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

В этом примере, где таблица называется T_STUD6Предположим, вы хотите создать новый анонимный набор данных студентов, не в нижних 10, отсортированный по счету. Для этого тип:

=DROP(SORT(T_Stud6,4,-1),-H1,1)

где

• 4 сообщает Excel для сортировки данных по четвертому столбцу (оценка),
• -1 сообщает Excel, чтобы сортировать эту колонку в порядке убывания (наибольшее до наименьшего),
• -Х1 сообщает Excel, чтобы сбросить 10 нижних рядов, поскольку ячейка H1 содержит число 10, а формула содержит знак минус, и
• 1 сообщает Excel сбросить первый столбец.

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

УПРАЖНЕНИЕ 7: Объединить и сбросить данные

В этом примере у каждого класса есть своя таблица: T_blue для синего класса, T_green для зеленого класса, и T_yellow для желтого класса.

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

Тип:

=DROP(SORT(VSTACK(T_Blue,T_Green,T_Yellow),3,-1),G1)

где

• Функция VSTACK добавляет три таблицы, одна на другой,

• 3 сообщает Excel сортировать результат по третьему столбцу (оценка),
• -1 сообщает Excel сортировать этот столбец в порядке убывания, и
• G1 сообщает Excel, чтобы отбросить первые 10 строк в добавленных данных, поскольку Cell G1 содержит положительное число 10.

УПРАЖНЕНИЕ 8: Отбрасывайте строки или столбцы из избранных данных

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

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

В этом случае вы можете использовать Функция выбораПолем Тип:

=DROP(CHOOSECOLS(SORT(T_Stud8,4,-1),3),-I1)

где

• Сортировка части формулы сортирует исходные данные по четвертому столбцу (4) в убыточном порядке (-1),

• Выборка часть формулы извлекает третий столбец (3) из набора данных и

• Часть падения формулы исключает нижние десять рядов, поскольку ячейка I1 содержит число 10, а эталонке ячейки предшествует символ минус.

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