Как найти и вернуть предпоследнее значение в определенной строке или столбце в Excel?

Excel works!

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

Функция СТРОКА() и СТОЛБЕЦ(). Как использовать?

Самое интересное в этих формулах то, что они могут работать как с деталями, так и без них. Другими словами, формула =STROCK() возвращает номер данного конкретного ряда, а =STROCK(A3) возвращает номер ряда ячейки A3, соответственно 3.

Подробнее на эту тему

Номер строки и столбца для списка или таблицы

При использовании функции STRING для нумерации данных в списке, сопоставьте номер строки с номером элемента, в этом случае мы делаем -1

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

То же самое можно сделать и с нумерацией столбцов.

Как посчитать количество столбцов в выделенном диапазоне столбцов?

Если вы, например, выбираете диапазон столбцов, а не ячеек, не требуется формула для подсчета количества столбцов. На экране «Выбор диапазона» автоматически подсчитывается и отображается количество столбцов в диапазоне. Буква C означает, что подсчитываются столбцы -Colomn

Если вы используете функцию БПФ в формуле, это невероятно удобно.

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

Функция СТРОКА() в условном форматировании. Как сделать зебру в таблице — чередование цветов заливки?

Условным форматировании можно раскрасить заливку ячеек и даже целых таблиц. Удобно для чтения, когда каждая строка таблица выделяется чередующимся цветом — т.н. зеброй. Как раз функция СТРОКА() нам и поможет.

Установите условное форматирование в окне формулы:

И получилось удобное оформление

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

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

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

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

Выделяем цветом дубликаты в таблице

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

Открывает вкладку «Главная», в разделе «Стили» выбираем «Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения».

На экране открывается окно, в котором есть две опции: что выбрать — уникальные или повторяющиеся значения, и каким цветом раскрасить ячейки. Конечно же, есть кнопка «ОК».

Чтобы выполнить поиск по всей таблице Excel, сначала выделите нужные столбцы и строки.

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

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

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

Открывает вкладку «Главная» — раздел «Редактирование» — «Найти и выделить» (CTRL+F). alt=»11″ width=»300″ height=»34″ />

В поле «Поиск» введите то, что вы ищете. Затем нажмите кнопку «Искать все», нажмите CTRL+A, чтобы выбрать все результаты поиска и выделить их. Вы также можете удалить их вместо выделения.

Удаляем все одинаковые значения в Excel с помощью расширенного фильтра

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

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

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

Еще один способ быстро удалить дубли в таблице

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

Теперь откройте вкладку «Данные», раздел «Работа с данными», «Удалить дубликаты». alt=»7″ width=»300″ height=»84″ />

Давайте установим флажки. Мне нужен поиск по двум столбцам, поэтому я оставляю все как есть и нажимаю «ОК».

Это конец метода. Вот мой результат его работы.

Все методы были протестированы в Excel версий 2007, 2010, 2013, 2016 и 2019 гг.

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

Немного юмора:

Оскорбление 21 века — «По тебе фотошоп плачет».

Как строить выпадающие списки в Excel

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

В Excel выпадающие списки можно создавать разными способами. Вместе с аналитиком Laba Александром Галабурдой давайте изучим некоторые из них.

Как работает выпадающий список в Excel

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

Чтобы создать такой выпадающий список, перейдите в раздел «Данные» на панели инструментов, в группе «Работа с данными» выберите пункт «Проверка данных».

Далее всплывает окно «Проверка вводимых значений».

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

#1. Параметры

Так задаются основные параметры выпадающего списка в Excel:

• Тип данных. Можно выбрать тип данных, который будет содержать список: диапазон целых или действительных чисел, текстовые выражения, даты и время. Можно задать ограничения по длине текста и различные формулы.
• Игнорировать пустые значения — данный пункт означает, что Excel не будет проверять на правильность ячейки, в которых содержатся пустые значения.
• Список допустимых значений. Этот флажок отображается только в том случае, если выбран тип данных «Список». Если убрать флажок, в ячейке будет происходить проверка на соответствие значений списку, но раскрывающее поле с выпадающими значениями будет отсутствовать.
• Значение. Работает только с теми типами данных, в которых можно задать ограничения по числам или датам.
• Источник. Здесь перечисляются значения для проверки данных или задается формула.
• Распространить изменения на другие ячейки с тем же условием. Excel здесь находит все ячейки в книге, которые ссылаются на идентичное по свойствам условие и изменяет их согласно новых параметров. В случае, если флажок не будет установлен, условие будет изменено только для выделенных ячеек в таблице.
• Очистить все — удаляет установленную проверку данных с выделенных ячеек.

#2. Подсказка по вводу

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

#3. Сообщение об ошибке

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

Примеры выпадающих списков в Excel

#1. Стандартный

Выделяем ячейку (диапазон ячеек), где должен всплывать выпадающий список (в нашем примере это вкладка «Проект», диапазон ячеек A2:A25), переходим в раздел «Проверка данных» (описано выше), выбираем тип данных «Список», в поле «Источник» вставляем диапазон с источника.

Если вам нужно ввести подсказки и настроить вывод сообщения об ошибке, переходим в соответствующие разделы и прописываем необходимые свойства. Затем нажимаем «ОК».

Как видим, при выделении ячейки в диапазоне A2:A25 во вкладке «Проект», у нас появился список значений.

#2. Список с подстановкой данных

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

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

На этот раз мы настроим проверку данных по-другому. Рассмотрим два варианта списков автозаполнения:

#1. Умная таблица. Выделяем диапазон с источником, переходим во вкладку на панели инструментов «Главная», раздел «Стили», раскрываем меню «Форматировать как таблицу» и выбираем понравившийся стиль умной таблицы Excel.

Подробнее о том, что такое «Умные таблицы» и как с ними работать — на наших курсах.

Excel для финансов

Алексей Вощак,
Партнер в Bridges Consulting

Excel для компаний

Алексей Вощак,
Партнер в Bridges Consulting

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

Чтобы выпадающий список в Excel стал динамическим, выделяем любую из ячеек, где он находится, переходим в раздел «Проверка данных». Нам подтянется текущее условие проверки.

В строке с источником прописываем ссылку на столбец таблицы с использованием функции ДВССЫЛ: =ДВССЫЛ("Товары[Товар]"). Далее отмечаем «Распространить изменения на другие ячейки с тем же условием», и нажимаем «ОК».

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

#2. Менеджер имен. Этот метод похож на описанный ранее, за исключением того, что он не преобразует диапазон в смарт-таблицу.

Имя диапазона так же, как и в умной таблице, не должно содержать пробелы и знаки препинания. Выделяем диапазон ячеек с запасом пустых строк. Например, в нашем случае, мы понимаем, что в списке больше 25 значений содержаться не будет. Переходим во вкладку «Формулы», раздел «Определенные имена», меню «Диспетчер имен», нажимаем «Создать».

Назовите будущий список и измените диапазон значений, если необходимо.

Возвращаемся на лист «Проект», выделяем ячейку, в которой должен быть выпадающий список, переходим в меню «Проверка данных» и в поле Источник ссылаемся на созданный диапазон, нажимаем «ОК».

Общие рекомендации

• Присваивайте источникам с данными имена. Это позволит пополнять списки новыми данными без изменения формулы в проверке данных.
• Если в вашем отчете содержится несколько списков, выносите источники на отдельный лист. Это практично и не загромождает главную страницу отчета лишними данными.
• Сортируйте список по удобному для вас параметру — в больших массивах это позволит быстро найти нужную позицию.
• Не дублируйте в источниках значения выпадающего списка, иначе у вас будет несколько одинаковых значений.
• По умолчанию в выпадающем списке отображается всего 8 первых значений. Если нужно отобразить больше или реализовать функцию быстрого поиска, используйте элементы управления VBA или ActiveX.

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