Функция offset (смещ) в excel. как использовать? - эксель хак: онлайн-академия

Диапазон с пропусками (текст и числа)

Если столбец содержит и текстовые и числовые значения, то для определения номера строки последней заполненной ячейки можно предложить универсальное решение: =МАКС(ЕСЛИОШИБКА(ПОИСКПОЗ(«*»;$A:$A;-1);0); ЕСЛИОШИБКА(ПОИСКПОЗ(1E+306;$A:$A;1);0))

Функция ЕСЛИОШИБКА() нужна для подавления ошибки возникающей, если столбец A содержит только текстовые или только числовые значения.

Другим универсальным решением является формула массива: =МАКС(СТРОКА(A1:A20)*(A1:A20«»))

После ввода формулы массива нужно нажать CTRL + SHIFT + ENTER. Предполагается, что значения вводятся в диапазон A1:A20. Лучше задать фиксированный диапазон для поиска, т.к. использование в формулах массива ссылок на целые строки или столбцы является достаточно ресурсоемкой задачей.

Значение из последней заполненной ячейки, в этом случае, выведем с помощью функции ДВССЫЛ() : =ДВССЫЛ(«A»&МАКС(СТРОКА(A1:A20)*(A1:A20«»)))

Как обычно, после ввода формулы массива нужно нажать CTRL + SHIFT + ENTER вместо ENTER.

СОВЕТ: Как видно, наличие пропусков в диапазоне существенно усложняет подсчет. Поэтому имеет смысл при заполнении и проектировании таблиц придерживаться правил приведенных в статье Советы по построению таблиц.

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

EDIT: лучшее решение, чем использование INDIRECT()

стоит отметить, что решение, которое я дал, должно быть предпочтительнее любого решения с использованием @и imix это ниже для вариации этой идеи (с использованием ссылок на стиль RC). В этом случае вы можете использовать =!RC на THIS_CELL именованная формула диапазона, или просто использовать RC напрямую.

просто для полноты картины хочу дать еще один ответ:

во-первых, перейти к Excel-Options ->Формулы и включения ссылки R1C1. Тогда используйте

RC всегда ссылается на текущую строку, текущий столбец, т. е.»эта ячейка».

решение Рика Тичи это в основном настройка, чтобы сделать то же самое возможно в A1 эталонный стиль (см. также GSerg это!—18—> к ответу и записке Джоуи комментарии на ответ Патрика Макдональда).

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

например, при наличии двух столбцов и , поставив в

надеюсь, что это помогает.

столбец A является истинным или ложным, столбец B содержит денежное значение, столбец C содержит следующее формула: =B1

теперь, чтобы вычислить, что столбец B будет выделен желтым цветом в условном формате, только если столбец A истинен, а столбец B больше нуля.

затем вы можете скрыть столбец C

Автозаполнение в Excel из списка данных

Ясно, что кроме дней недели и месяцев могут понадобиться другие списки. Допустим, часто приходится вводить перечень городов, где находятся сервисные центры компании: Минск, Гомель, Брест, Гродно, Витебск, Могилев, Москва, Санкт-Петербург, Воронеж, Ростов-на-Дону, Смоленск, Белгород. Вначале нужно создать и сохранить (в нужном порядке) полный список названий. Заходим в Файл – Параметры – Дополнительно – Общие – Изменить списки.

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

Как видно, их не много. Но легко добавить свой собственный. Можно воспользоваться окном справа, где либо через запятую, либо столбцом перечислить нужную последовательность. Однако быстрее будет импортировать, особенно, если данных много. Для этого предварительно где-нибудь на листе Excel создаем перечень названий, затем делаем на него ссылку и нажимаем Импорт.

Помимо текстовых списков чаще приходится создавать последовательности чисел и дат. Один из вариантов был рассмотрен в начале статьи, но это примитивно. Есть более интересные приемы. Вначале нужно выделить одно или несколько первых значений серии, а также диапазон (вправо или вниз), куда будет продлена последовательность значений. Далее вызываем диалоговое окно прогрессии: Главная – Заполнить – Прогрессия.

Рассмотрим настройки.

В левой части окна с помощью переключателя задается направление построения последовательности: вниз (по строкам) или вправо (по столбцам).

Посередине выбирается нужный тип:

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

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

автозаполнение – эта команда равносильная протягиванию с помощью левой кнопки мыши. То есть эксель сам определяет: то ли ему продолжить последовательность чисел, то ли продлить список. Если предварительно заполнить две ячейки значениями 2 и 4, то в других выделенных ячейках появится 6, 8 и т.д. Если предварительно заполнить больше ячеек, то Excel рассчитает приближение методом линейной регрессии, т.е. прогноз по прямой линии тренда (интереснейшая функция – подробнее см. ниже).

Нижняя часть окна Прогрессия служит для того, чтобы создать последовательность любой длины на основании конечного значения и шага. Например, нужно заполнить столбец последовательностью четных чисел от 2 до 1000. Мышкой протягивать не удобно. Поэтому предварительно нужно выделить только ячейку с одним первым значением. Далее в окне Прогрессия указываем Расположение, Шаг и Предельное значение.

Результатом будет заполненный столбец от 2 до 1000. Аналогичным образом можно сделать последовательность рабочих дней на год вперед (предельным значением нужно указать последнюю дату, например 31.12.2016). Возможность заполнять столбец (или строку) с указанием последнего значения очень полезная штука, т.к. избавляет от кучи лишних действий во время протягивания. На этом настройки автозаполнения заканчиваются. Идем далее.

Подсчитать количество ячеек, содержащих числовые или нечисловые значения в Excel

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

Количество ячеек, содержащих числовые значения

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

=COUNT(range)

range: Диапазон ячеек, которые вы хотите подсчитать.

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

=COUNT(A2:C9)

Количество ячеек, содержащих нечисловые значения

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

=SUMPRODUCT(—NOT(ISNUMBER(range)))

range: Диапазон ячеек, которые вы хотите подсчитать.

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

=SUMPRODUCT(—NOT(ISNUMBER(A2:C9)))

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

ЕЧИСЛО (A2: C9): Эта функция ЕЧИСЛО выполняет поиск чисел в диапазоне A2: C9 и возвращает ИСТИНА или ЛОЖЬ. Итак, вы получите такой массив: {ИСТИНА, ИСТИНА, ЛОЖЬ; ЛОЖЬ, ЛОЖЬ, ИСТИНА; ИСТИНА, ЛОЖЬ, ЛОЖЬ; ЛОЖЬ, ИСТИНА, ЛОЖЬ; ЛОЖЬ, ЛОЖЬ, ЛОЖЬ; ИСТИНА, ЛОЖЬ, ИСТИНА; ИСТИНА, ИСТИНА, ЛОЖЬ; ЛОЖЬ, ЛОЖЬ, ИСТИНА}.
НЕ (ЕЧИСЛО (A2: C9)): Эта функция НЕ преобразует результат массива в обратную сторону. И результат будет таким: {ЛОЖЬ, ЛОЖЬ, ИСТИНА; ИСТИНА, ИСТИНА, ЛОЖЬ; ЛОЖЬ, ИСТИНА, ИСТИНА; ИСТИНА, ЛОЖЬ, ИСТИНА; ИСТИНА, ИСТИНА, ИСТИНА; ЛОЖЬ, ИСТИНА, ЛОЖЬ; ЛОЖЬ, ЛОЖЬ, ИСТИНА; ИСТИНА, ИСТИНА, ЛОЖЬ}.
—НЕТ (НОМЕР (A2: C9)): Этот двойной отрицательный оператор — преобразует вышеуказанный TURE в 1 и FALSE в 0 в массиве, и вы получите следующий результат: {0,0,1; 1,1,0; 0,1,1; 1,0,1, 1,1,1; 0,1,0; 0,0,1; 1,1,0; XNUMX}.
SUMPRODUCT(—NOT(ISNUMBER(A2:C9)))= SUMPRODUCT({0,0,1;1,1,0;0,1,1;1,0,1;1,1,1;0,1,0;0,0,1;1,1,0}): Наконец, функция СУММПРОИЗВ складывает все числа в массиве и возвращает окончательный результат: 14.

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

Используемая относительная функция:

СЧИТАТЬ:
Функция COUNT используется для подсчета количества ячеек, содержащих числа, или для подсчета чисел в списке аргументов.

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

НЕ:
Функция НЕ возвращает обратное логическое значение.

НОМЕР:
Функция ЕЧИСЛО возвращает ИСТИНА, если ячейка содержит число, и ЛОЖЬ, если нет.

Другие статьи:

Подсчитать количество ячеек, содержащих определенный текст в Excel
Предположим, у вас есть список текстовых строк, и вы можете захотеть найти количество ячеек, которые содержат определенный текст как часть своего содержимого. В этом случае вы можете использовать подстановочные знаки (*), которые представляют любые тексты или символы в ваших критериях при применении функции СЧЁТЕСЛИ. В этой статье я расскажу, как использовать формулы для решения этой задачи в Excel.

Подсчитайте количество ячеек, не равное множеству значений в Excel
В Excel вы можете легко получить количество ячеек, не равное определенному значению, используя функцию СЧЁТЕСЛИ, но пробовали ли вы когда-нибудь подсчитать количество ячеек, которые не равны множеству значений? Например, я хочу получить общее количество продуктов в столбце A, но исключить конкретные элементы в C4: C6, как показано на скриншоте ниже. В этой статье я представлю несколько формул для решения этой задачи в Excel.

Подсчитайте количество ячеек, содержащих нечетные или четные числа
Как все мы знаем, остаток нечетных чисел равен 1 при делении на 2, а остаток четных чисел равен 0 при делении на 2. В этом уроке я расскажу о том, как получить количество ячеек, содержащих нечетные или четные. числа в Excel.

Добавление вспомогательного столбца в набор данных

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

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

Хотя это простой обходной путь, у него есть некоторые недостатки (которые будут рассмотрены далее).

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

Предположим, у меня есть набор данных, как показано ниже:

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

= ЕСЛИ (СЧЁТЕСЛИМН ($C$2:C2; C2; $B$2:B2; B2) > 1;0;1)

Приведенная выше формула использует функцию СЧЁТЕСЛИМН для подсчета количества раз, когда имя появляется в данном регионе

Также обратите внимание на диапазоны критериев: $C$2:C2 и $B$2:B2. Это означает, что они продолжают расширяться, когда вы идете вниз по столбцу

Например, в ячейке F2 диапазон критериев составляет $C$2:C2 и $B$2:B2, а в ячейке F3 эти диапазоны расширяются до $C$3:C3 и $B$3:B3.

Это гарантирует, что функция СЧЁТЕСЛИМН считает первый экземпляр имени как 1, второй экземпляр имени как 2 и так далее.

Поскольку мы хотим получить только разные имена, используется функция ЕСЛИ, которая возвращает 1, когда имя появляется для региона в первый раз, и возвращает 0, когда оно появляется снова. Это гарантирует, что учитываются только разные имена, а не повторы.

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

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

Ниже приведены шаги, как сделать это:

Выберите любую ячейку в таблице.
Нажмите вкладку «Вставка».

Нажмите на кнопку Сводная таблица.

В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно (и включает вспомогательный столбец), и выбран «На новый лист» в качестве места размещения.

Нажмите ОК.

Вышеуказанные шаги вставят новый лист со сводной таблицей.

Перетащите поле «Регион» в область «Строки» и поле «Помощник» в область «Значения».

Вы получите вот такую сводную таблицу:

Теперь вы можете изменить заголовок столбца с «Сумма по полю Помощник» на «Количество сотрудников».

Примеры функции ГПР в Excel пошаговая инструкция для чайников

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

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

Например, в таблице с полями «Имя» и «Дата рождения» необходимо получить значение даты рождения для сотрудника, запись о котором является третьей сверху. В этом случае удобно использовать следующую функцию: =ГПР(«Дата рождения»;A1:B10;4), где «Дата рождения» – наименование столбца таблицы, в котором будет выполнен поиск, A1:B10 – диапазон ячеек, в котором расположена таблица, 4 – номер строки, в которой содержится возвращаемое значение (поскольку таблица содержит шапку, номер строки равен номеру искомой записи +1.

Пошаговые примеры работы функции ГПР в Excel

Пример 1. В таблице содержатся данные о клиента и их контактных номерах телефонов. Определить номер телефона клиента, id записи которого имеет значение 5.

Вид таблицы данных:

Для расчета используем формулу:

F1 – ячейка, содержащая название поля таблицы;
A1:C11 – диапазон ячеек, в которых содержится исходная таблица;
E2+1 – номер строки с возвращаемым значением (для – шестая строка, поскольку первая строка используется под шапку таблицы).

В ячейке F2 автоматически выводится значение соответствующие номеру id в исходной таблице.

ГПР для выборки по нескольких условиях в Excel

Пример 2. На основе таблицы из первого примера создать компактное представление таблицы, в которой по номеру id можно получить записи, хранящиеся в столбцах «Клиент» и «Телефон».

Создадим заготовку таблицы:

Для удобного использования в ячейке E2 создадим выпадающий список. Для этого выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

Для выбора клиента используем следующую формулу в ячейке F2:

Для выбора номера телефона используем следующую формулу (с учетом возможного отсутствия записи) в ячейке G2:

Функция ЕСЛИ выполняет проверку возвращаемого значения. Если искомая ячейка не содержит данных, будет возвращена строка «Не указан».

Интерактивный отчет для анализа прибыли и убытков в Excel

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

Вид таблиц данных с выпадающим списком в ячейке E2 (как сделать выпадающий список смотрите в примере выше):

В ячейку F2 запишем следующую формулу:

Функция ABS возвращает абсолютное число, равное разнице возвращаемых результатов функций ГПР.

В ячейке G2 запишем формулу:

Функция ЕСЛИ сравнивает возвращаемые функциями ГПР значения и возвращает один из вариантов текстовых строк.

Как можно использовать сводную таблицу.

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

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

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

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

Когда откроется сводная таблица, расположите области строк, столбцов и значений так, как вам нужно. Если у вас нет большого опыта работы со сводными таблицами Excel, могут оказаться полезными следующие подробные рекомендации: Создание сводной таблицы в Excel.
Переместите поле, количество уникальных элементов которого вы хотите вычислить ( поле « Товар» в этом примере), в область « Значения» , щелкните его и выберите «Параметры значения поля…» из раскрывающегося меню.
Откроется диалоговое окно , прокрутите вниз до операции «Число разных элементов» , которая является самым последним пунктом в списке, выберите ее и нажмите OK .

Вы также можете дать собственное имя своему счетчику, если хотите.

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

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

Благодарю вас за чтение и надеюсь увидеть вас снова. Пожалуйста, не переключайтесь!

Как найти и выделить уникальные значения в столбце – В статье описаны наиболее эффективные способы поиска, фильтрации и выделения уникальных значений в Excel. Ранее мы рассмотрели различные способы подсчета уникальных значений в Excel. Но иногда вам может понадобиться только просмотреть уникальные…

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

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

Как посчитать количество повторяющихся значений в Excel? – Зачем считать дубликаты? Мы можем получить ответ на множество интересных вопросов. К примеру, сколько клиентов сделало покупки, сколько менеджеров занималось продажей, сколько раз работали с определённым поставщиком и т.д. Если…

Как убрать повторяющиеся значения в Excel? – В этом руководстве объясняется, как удалять повторяющиеся значения в Excel. Вы изучите несколько различных методов поиска и удаления дубликатов, избавитесь от дублирующих строк, обнаружите точные повторы и частичные совпадения. Хотя…

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

Функции ИНДЕКС и СЧЁТЗ прекрасно используются для поиска значений в случае, когда диапазон исходных данных не содержит пустых ячеек и является неразрывным. Если же исходных диапазон ячеек содержит пустые ячейки, а искомыми значениями являются числа, можно воспользоваться функцией ПРОСМОТР с очень большим числовым значением в аргументах. Данная техника применяется с помощью составления следующей формулы:

Искомое значение в данной формуле (единица с 308 знаками) – это самое большое число доступное в программе Excel. Так как функция ПРОСМОТР не имела шансов найти большего значения чем искомое, она прекратила свое вычисление на последнем найденном числовом значении, которую и вернула в результат.

Что значит число с буквой E в Excel?

Число такое как, например, 9,99E+307 записано в экспоненциальном формате. Число перед буквой E имеет одну цифру 0-9 и только две цифры после запятой. Число после буквы E значит количество разрядов, на которые следует сместить запятую (307 в данном примере), чтобы получить числовое значение, записанное традиционным способом. Плюс значит, что запятую следует смещать в право, а минус – влево. Например, запись 4,32E-02 означает число, записанное в десятичной дроби 0,0432.

Функция ПРОСМОТР имеет преимущество перед другими функциями в том, что она возвращает последнее число даже тогда, когда ячейки в просматриваемом диапазоне могут быть не только пустыми, но и содержать текстовые строки, коды ошибок или даты.

5 ответов

много способов сделать это. Самое надежное-найти.

Dim rLastCell As RangeSet rLastCell = ws.Cells.Find(What:=”*”, After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)MsgBox (“The last used column is: ” & rLastCell.Column)

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

Dim lColumn As LonglColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column

используя используемый ряд (более менее надежный):

Dim lColumn As LonglColumn = ws.UsedRange.Columns.Count

использование используемого диапазона не будет работать, если у вас нет данных в столбце A. см. здесь еще одну проблему с используемым диапазоном:

посмотреть здесь по поводу сброса используемого диапазона.

Я знаю, что это старый, но я проверил это во многих отношениях, и это еще не подвело меня, если кто-то не может сказать мне иначе.

номер строки

Row = ws.Cells.Find(What:=”*”, After:= , SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Буква Столбца

ColumnLetter = Split(ws.Cells.Find(What:=”*”, After:=, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), “$”)(0)

Номер Столбца

ColumnNumber = ws.Cells.Find(What:=”*”, After:=, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

попробуйте использовать код после того, как вы активируете лист:

Dim J as integerJ = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

если вы используете Cells.SpecialCells(xlCellTypeLastCell).Row только проблема будет в том, что xlCellTypeLastCell информация не будет обновляться, если не выполнить действие “сохранить файл”. Но используйте UsedRange всегда будет обновлять информацию в режиме реального времени.

Я думаю, мы можем изменить UsedRange код @Readify это ответ выше, чтобы получить последний используемый столбец, даже если начальные столбцы пусты или нет.

это lColumn = ws.UsedRange.Columns.Count изменен на

этой lColumn = ws.UsedRange.Column + ws.UsedRange.Columns.Count – 1 даст надежные результаты всегда

?Sheet1.UsedRange.Column + Sheet1.UsedRange.Columns.Count – 1

над линией выходы 9 в окне immediate.

Что делать, если у вас нет XLOOKUP?

Поскольку XLOOKUP, скорее всего, будет доступен только для пользователей Office 365, один из способов получить его — перейти на Office 365.

Если у вас уже есть Office 365 для дома, личного или университетского, у вас уже есть доступ к XLOOKUP. Все, что вам нужно сделать, это присоединиться к программе предварительной оценки Office.

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

Если у вас есть другие подписки на Office 365 (например, Enterprise), я уверен, что XLOOKUP и другие замечательные функции (например, динамические массивы, формулы, такие как SORT и FILTER) станут доступны в ближайшее время.

Если вы используете Excel 2010/2013/2016/2019, у вас не будет XLOOKUP, и вам придется продолжать использовать комбинации ВПР, ГПР и ИНДЕКС / ПОИСКПОЗ, чтобы максимально эффективно использовать формулы поиска.

Диапазон с пропусками (числа)

В случае наличия пропусков (пустых строк) в столбце, функция СЧЕТЗ() будет возвращать неправильный (уменьшенный) номер строки: оно и понятно, ведь эта функция подсчитывает только значения и не учитывает пустые ячейки.

Если диапазон заполняется числовыми значениями, то для определения номера строки последней заполненной ячейки можно использовать формулу =ПОИСКПОЗ(1E+306;A:A;1) . Пустые ячейки и текстовые значения игнорируются.

Так как в качестве просматриваемого массива указан целый столбец ( A:A ), то функция ПОИСКПОЗ() вернет номер последней заполненной строки. Функция ПОИСКПОЗ() (с третьим параметром =1) находит позицию наибольшего значения, которое меньше или равно значению первого аргумента (1E+306). Правда, для этого требуется, чтобы массив был отсортирован по возрастанию. Если он не отсортирован, то эта функция возвращает позицию последней заполненной строки столбца, т.е. то, что нам нужно.

Чтобы вернуть значение в последней заполненной ячейке списка, расположенного в диапазоне A2:A20 , можно использовать формулу: =ИНДЕКС(A2:A20;ПОИСКПОЗ(1E+306;A2:A20;1))

Диапазон с пропусками (числа)

В случае
наличия пропусков
(пустых строк) в столбце, функция
СЧЕТЗ()
будет возвращать неправильный (уменьшенный) номер строки: оно и понятно, ведь эта функция подсчитывает только значения и не учитывает

пустые

ячейки.

Если диапазон заполняется
числовыми
значениями, то для определения номера строки последней заполненной ячейки можно использовать формулу
=ПОИСКПОЗ(1E+306;A:A;1)
. Пустые ячейки и текстовые значения игнорируются.

отсортирован

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

Чтобы вернуть значение в последней заполненной ячейке списка, расположенного в диапазоне
A2:A20
, можно использовать формулу:
=ИНДЕКС(A2:A20;ПОИСКПОЗ(1E+306;A2:A20;1))

Обратная совместимость XLOOKUP

Это одна вещь, о которой вам нужно быть осторожным: XLOOKUP — это НЕ обратно совместимый.

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

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

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

Надеюсь, вы нашли этот урок полезным!