1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как автоматически обновить результат расширенного фильтра в Excel?

Часы в ячейке Excel

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

Формулы TODAY(), DATE() и TIME() не помогут вам, потому что они оставляют «отпечатки» текущей даты, времени и часов на момент ввода формулы. Формулы не обновляются автоматически.

Выход один — использовать макросы.

Давайте рассмотрим шаги по созданию нашей процедуры:

  1. Создайте новую рабочую книгу в Excel;
  2. На вкладке «Разработчик» нажмите на кнопку VisualBasic;
  3. Выполните команду Вставка -> Модуль.

Часы в ячейке Excel

Появится пустое окно, в котором нужно ввести следующий макрос:

Dim varNextCall Как переменная

‘записываем в ячейку текущее время
Cells(1, 1).Value = Now

‘записываем в varNextCall время, когда вызвать макрос в следующий раз (через 1 секунду)
varNextCall = TimeSerial(Hour(Now), Minute(Now), Second(Now) +1)

‘уведомляем программу в вызове макроса
Application.Ontime varNextCall, «UpdateTime»

Часы в ячейке Excel

Мы создали макрос для записи обновления времени, поместив текущее время в ячейку A1, который можно вызвать с помощью Alt+F8 или нажав на кнопку «Макросы».

Часы в ячейке Excel

Следующим шагом будет добавление книги в компьютер. Сохраните книгу с поддержкой макросов (.xlsm)!

Именно это будет отображаться при выполнении макроса:

Часы в ячейке Excel

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

У этого примера также есть один недостаток — как только мы закроем книгу, макрос перестанет работать. Чтобы избежать этого, мы подключим к макросу автоматический запуск (как мы это сделали для валюты Currency).

  1. Нажмите на кнопку VisualBasic;
  2. В области Project дважды щелкните на строке «This workbook»;
  3. В появившемся окне выберите «Workbook» в поле «Object» и «Procedure» в имени процедуры «Open»;
  4. В появившихся строках введите вызов нашего макроса Call UpdateTime.
Читайте так же:
Как в Excel показывать только положительные числа?

Часы в ячейке Excel

Макрос можно переместить в любую ячейку, так что теперь даже при закрытии/открытии у нас будет точное время!

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

Если у вас остались вопросы, посмотрите видеоурок:

Как автоматически обновить результат расширенного фильтра в Excel?

Всякий раз, когда я использую Excel, я задаюсь вопросом, почему он не поддерживает регулярные выражения (программисты также называют их регексами). Сортировка и фильтрация — сложные задачи, выполняемые с их помощью. Он поддерживает регулярные выражения, но только на платформах PC, как и многие дополнения к Excel. Наши статьи предназначены в первую очередь для пользователей Mac (мы не продвигаем SeoTools).

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

Немного теории

Мне всегда казалось странным, что Excel не поддерживает регулярные выражения (которые в кругах программистов называют регулярными выражениями или регексами). Они используются для выполнения сложной сортировки и фильтрации. Плагин SeoTools для Excel поддерживает регулярные выражения, но, как и большинство надстроек для Excel, он работает только на платформе ПК. Эта статья предназначена в первую очередь для пользователей Mac (мы не рекламируем SeoTools).

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

Подготовка к работе с расширенным фильтром

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

Читайте так же:
Как быстро скрыть значения ячеек в Excel?

Расположен усовершенствованный фильтр:

Версия для ПК — Данные > Сортировка и фильтрация > Дополнительно.

Для Mac: Данные > Сортировка и фильтр > Фильтр > Расширенный фильтр

На Mac пользователи могут нажать на таблицу с ПК, а затем выбрать элемент из контекстного меню. На ПК это невозможно.

Небольшой совет

Базовые операторы

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

  • =равны, т.е. левая и правая части должны совпадать: =ticket [включает все ячейки, содержащие билет]
  • <> не равны, левая и правая части не должны совпадать: <> https://www.aviasales.ru/mag* [исключает этот адрес (блог)]
  • ‘ апостроф превращает формулу в текст, если он помещен в ячейку первым: ‘=buy a ticket
  • > more than: >500
  • >= more than or equal to: B4-C4>=3
  • < меньше, чем: C6

Заголовки

Набор условий

Вот три базовые конструкции для множественных условий:

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

Фильтр отбирает строки, для которых выполняется хотя бы одно из условий, т.е. либо Position меньше 11, либо Quantity больше 1000. ИЛИ также можно использовать для объединения одного столбца. Как вы можете видеть, все критерии расположены в целевом столбце друг под другом.

A ND: Если все критерии должны выполняться одновременно, используется логический оператор AND.

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

Читайте так же:
Как в Excel отображать только строки с определенным текстом?

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

Таблица условий немного обескураживает. Но этот фильтр выбирает только строки, содержащие слово «купить» или «аэрофлот» без «самолет» или «аэроплан» и значение последнего столбца больше 1000.

Чтобы получить текст вместо формул, просто поставьте апостроф после операторов сравнения, таких как ‘=to buy или ‘<>plane. На своем веб-сайте Microsoft рекомендует слишком сложный метод, советуя заключить все выражение в двойные кавычки и поставить перед ним знак равенства: .

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

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

Эти условия позволят выбрать ключевые слова со среднемесячным показателем от 3 700 до 10 000.

Формулы: Это действительно мощный инструмент. Формулы являются альтернативой таблице критериев.

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

При использовании формул необходимо учитывать несколько моментов:

  • Формула должна возвращать булево значение FALSE или TRUE;
  • Заголовки над формулой должны отличаться от заголовков базы данных, можно даже использовать пустую строку;
  • Формула должна ссылаться на ячейки в первой строке под заголовками базы данных;
  • Ссылки на проверяемые ячейки базы данных должны быть относительными: введите C4, если абсолютными, например, $C$4 — значение формулы будет статичным, то есть всегда FALSE или всегда TRUE.

Общий алгоритм

Как работает расширенный фильтр (если данные и условия уже были подготовлены)

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

Данные > Сортировка и фильтр > Расширенный фильтр для ПК. Для Mac: Данные > Сортировка и фильтр > Фильтр > Расширенный фильтр.

Читайте так же:
Два простых способа конвертировать или импортировать содержимое документа Word на лист Excel

Откроется меню. Снимок экрана Excel 2013, но все пункты те же, что и на Mac.

Шаг 2: Выберите место хранения выбранных данных. Логичнее определить «альтернативное место», которое оставит исходный набор данных нетронутым. Но выбор остается за вами.

Шаг 3: Для исходного диапазона Excel по умолчанию выбирает всю «таблицу». Это можно изменить, исключив ненужные столбцы. Однако Excel позволяет выбрать только прямоугольную область. Чтобы выбрать столбцы с пропуском, введите названия целевых столбцов в свободные ячейки и укажите их адрес для исходной области. Дополнительные примеры содержат подробные пояснения.

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

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

Шаг 6: Если требуются только уникальные записи, установите флажок. К сожалению, большинство людей используют исключительно эту функцию.

Шаг 7: Нажмите кнопку ОК и посмотрите результат.

Шаг 8: Если список был отфильтрован, вы можете сбросить фильтр, нажав кнопку Очистить, однако это справедливо только для ПК. Этот пункт находится над пунктом Дополнительно. Существует аналог для Mac, Очистить фильтр, но в настоящее время он недоступен. Еще один аргумент в пользу переноса фильтрации в новое место. Нажмите Command-Z, чтобы отменить действие, или подключите стандартный фильтр, чтобы сбросить его.

голоса
Рейтинг статьи
Читайте так же:
Как быстро найти или вернуть второе по величине или наименьшее значение в Excel?
Ссылка на основную публикацию
Adblock
detector