☰

Как получить самое правое значение строки в Excel?

tag-icon • tag-icon microsoft-excel
Как получить самое правое значение строки в Excel?

Мне нужно показать значение самой правой непустой ячейки каждой строки массива. Как это можно сделать в Excel?

В этом примере таблицы столбец [Текущий] имеет желаемый результат:

+---------+----------+---------+----------+
|  2016   |   2017   |  2018   | Current  |
+---------+----------+---------+----------+
|     700 |          |   200   |     200  |
|         |          |         |          |
|         |     450  |         |     450  |
|         |          |  2,700  |   2,700  |
|         |          |         |          |
|  42,350 |  71,500  |         |  71,500  |
|  2,660  |          |         |   2,660  |
|         |   1,100  |         |   1,100  |
|         |          |         |          |
|    470  |          |         |     470  |
+---------+----------+---------+----------+

Вариации на тему: самое левое, самое верхнее, самое нижнее значение; или значение большени т. д. Desktop Excel из Office 2016, если версия актуальна.

решение1

введите описание изображения здесь

  • Введите эту формулу E2и заполните поле Вниз.

=LOOKUP(2,1/(A2:C2<>""),A2:C2)

Как это работает:

  • Формула распознает, что искомое значение 2 намеренно больше любых значений, которые появятся в векторе поиска.
  • Выражение A2:C2<>""возвращает массив значений Trueи False.
  • 1затем делится на этот массив и создает новый массив, состоящий либо из единиц, либо из ошибок деления на ноль (#DIV/0!): {1,0,1,...}.
  • Этот массив является вектором поиска.
  • Если формула не может найти искомое значение, то ей
    Lookupсоответствует следующее наименьшее значение.
  • В этом случае значением поиска является 2, но наибольшее значение в массиве поиска — 1, поэтому поиск будет соответствовать последнему значению 1в массиве.
  • LOOKUP возвращает соответствующее значение в векторе результата, которое является значением в той же позиции.

:Отредактировано:

  • Для Google Таблиц следует использовать следующую формулу:

    =(IFERROR(LOOKUP( 2, 1 / ( A2:C2 <> "" ), A2:C2 ),""))

  • Закончи это с помощьюCtrl+Shift+Enter, формула будет выглядеть так,

    =ArrayFormula(IFERROR(LOOKUP( 2, 1 / ( A2:C2 <> "" ), A2:C2 ),""))

решение2

Хотя уже существует несколько решений этой проблемы, вот мое любимое решение, для меня оно наиболее близко к естественному мышлению:

=INDEX(A2:C2,MAX(IF(A2:C2="","",COLUMN(A2:C2))))- это формула массива, поэтому нажмите CTRL+ SHIFT+ ENTERпосле ее ввода.

введите описание изображения здесь

Как это работает:

  • IF(A2:C2="","",COLUMN(A2:C2))- для каждой ячейки в строке возвращает пустую строку, если ячейка пуста, и номер столбца в противном случае
  • MAX( ... )- выбирает наибольший номер возвращаемого столбца
  • =INDEX(A2:C2, ... )- выбирает ячейку из строки на основе наибольшего номера столбца

Внимание: это работает правильно только в том случае, если ваш диапазон начинается с первого столбца, в противном случае необходимо компенсировать смещение, например, для диапазона, начинающегося со столбца C:
=INDEX(C2:X2,MAX(IF(C2:X2="","",COLUMN(C2:X2)))-2)

решение3

Предположим, что ваша таблица расположена в ячейках C2:F12, строка заголовка — это строка 2, а столбец сводки — это F. Поместите следующую формулу в ячейку F3 и скопируйте ее вниз.

=IFERROR(INDEX(3:3,AGGREGATE(14,6,column($C3:$E3)/($C3:$E3<>""),1)),"")

РОС

ПРИМЕЧАНИЕ:

  • AGGREGATE выполняет операции с массивами с вариантами формул 14 и 15. В результате не используйте полные ссылки на столбцы/строки в функции AGGREGATE, так как это может привести к перегрузке системы или ее сбою из-за количества вычислений, которые будут выполнены. Использование полных ссылок на столбцы вне функций типа массива допустимо. Обратите внимание, что для INDEX используется соотношение 3:3.

  • При вставке нового столбца, если выбран столбец F и выполнена вставка, вам нужно будет обновить формулу в F, чтобы C3:F3 стал новым диапазоном. Если у вас выбран столбец E и вы вставляете новый столбец, диапазон обновится автоматически, но теперь ваши данные находятся в неправильном столбце. Если вы оставили столбец F пустым, поместили формулы в столбец G вместо этого и использовали C3:F3 в качестве диапазона внутри AGGREGATE, то в будущем вы сможете выбрать столбец F для вставки, и ваши формулы обновятся, и вы сможете ввести новые данные в F. У вас будет пустой столбец справа для выбора в следующем году, чтобы повторить процесс.

решение4

Другой подход, неэлегантный, более грубый, но легко понятный, — это использование TEXTJOIN(), теперь, когда он у нас есть.

Используя A2:C2 для первой строки, поместите следующее в D2, затем скопируйте и вставьте. Или Fill, или... вы поняли идею:

ДляТекстовая строка соединенияниже используйте функцию TEXTJOIN() для объединения всего диапазона ячеек, которые вы хотите проверить. Используйте "TRUE", чтобы исключить пробелы для сокращения строки, а в качестве разделителя используйте символ, который НИКОГДА не появится в ваших данных. Я использую "Ŧ" ниже (и для символа, заменяющего последний, "Ų"). Использование запятых, как это часто делается с TEXTJOIN() и его родственниками, может вызвать проблемы.

=RIGHT( Textjoin string,
LEN( Textjoin string ) -
FIND( "Ų", SUBSTITUTE( Textjoin string, "Ŧ", "Ų",
LEN( Textjoin string **with** delimiter ) - LEN( Textjoin string **without** delimiter )
)))

и его легче понять. SUBSTITUTE() может выполнять свою работу, начиная сПример #что позволит вам найти последнее использование вашего разделителя вСтрока Textjoin с разделителем. В последней строке вы находите LEN() строки Textjoin с разделителем и без него и находите разницу вычитанием. Это количество разделителей и, следовательно,Пример #тебе нужно.

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

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

Первая строка делает именно это, оставляя вам содержимое последней ячейки в диапазоне.

Длина строки, которую будет использовать Excel, зависит от функции, некоторые находятся в диапазоне 6–7 000, например, некоторые — около 32 000. Учитывая это (именно поэтому вы указываете «ИСТИНА»), можно использовать ОГРОМНЫЙ диапазон вместо A2:C2.

Обратите внимание, что теперь вы работаете с объединенной строкой, а не с ячейками:

  1. Вам никогда не придется искать адреса сотовых телефонов и т.п.
  2. Вы можете использовать его на диапазоне, составленном из соединенных "под" диапазонов, и диапазонах, составленных из ДЕЙСТВИТЕЛЬНО разрозненных ячеек. Разрывные диапазоны - ваши друзья и союзники.

Поскольку способ, которым данные существуют внутри частей, оцениваемых Excel внутри формулы, разбиение частей на именованные диапазоны может вызвать проблемы или нет, так как промежуточные результаты, создаваемые и используемые Excel, оценивающим формулу, МОГУТ быть в другой форме, чем конечный результат, который именованный диапазон представляет вперед, иногда нельзя использовать именованные диапазоны на частях, чтобы изложить логику формулы для будущего удобства. Но ничто из вышеизложенного не представляет этой проблемы, поэтому вы можете создать именованные диапазоны, скажем, для TEXTJOIN и ввести остальное изначально, чтобы любой, кто щелкнет по ячейке, мог увидеть логику. Или разбить части на что-то логическое, например "InstanceNumber" (именованный диапазон), чтобы это читалось еще проще. Создайте его, затем выгрузите все это в именованный диапазон. Или вообще не беспокойтесь об именованных диапазонах.

Как я и говорю, неэлегантно. Длиннее, чем некоторые решения, но не настолько "грубо", как некоторые вещи. Никаких вспомогательных столбцов или других вещей, которые люди часто не могут использовать. Или не будут. Никаких формул {array}.

(И вы можете использовать прерывистые диапазоны, когда это необходимо.) Подход также может взять кучу текста и данных, которые движок отчетов PDF и которые затем извлекаются в Excel, но по-разному разбиваются на ячейки для каждого связанного набора (например, информация о 10 клиентах, каждый набор в блоке из 10 столбцов и 13 строк, но адрес для одного находится в ячейке 4,6, а для другого в ячейке 3,8, но он следует тому же потоку, просто заполняет разные ячейки при импорте) и, сделав его одной строкой, вы можете шаблонно искать части. Часто, во всяком случае. Или взять блок ячеек и посмотреть, появляется ли где-нибудь внутри них немного данных, используя функции, а не макросы или массивы или одну вспомогательную ячейку для каждой в блоке.

Связанный контент