Как найти и вернуть предпоследнее значение в определенной строке или столбце в 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.
Весь бизнес-контент в удобном формате. Интервью, кейсы, лайфхаки корп. мира — в нашем телеграм-канале. Присоединяйтесь!