Всплывающие подсказки в excel

Проверка данных Excel

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

  • 01.01.2001;
  • 01/01/2001;
  • 1 января 2001 года и т.д.

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

Где находится?

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

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

Настройка условия проверки

Изначально требуется выбрать тип проверяемых данных, что будет являться первым условием. Всего предоставлено 8 вариантов:

  • Целое число;
  • Действительное число;
  • Список;
  • Дата;
  • Время;
  • Длина текста;
  • Другой.

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

Самым необычным видом является выпадающий список.

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

Всплывающая подсказка ячейки Excel

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

На изображении показан пример возможного сообщения для ячейки, в которую вносятся целые числа от 1 до 31 (настройки вкладки «Параметры»). Заголовок и сообщения указываются по Вашему усмотрению, каких-либо правил к их оформлению нет. Не забудьте установить галочку на поле «Отображать подсказку, если ячейка является текущей», иначе сообщение будет деактивировано.

Пример всплывающей подсказки в Excel:

Вывод сообщения об ошибке

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

Существует три варианта сообщений, отличающихся по поведению:

Останов является сообщением об ошибке и позволяет произвести только 2 действия: отменить ввод и повторить ввод. В случае отмены новое значение будет изменено на предыдущее. Повтор ввода дает возможность скорректировать новое значение.

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

Сообщение выводить ошибку в виде простой информации и дает возможность отменить последнее действие.

Заголовок и сообщение заполняются по Вашему желанию.

Пример вывода одной и той же ошибки, но под разными видами:

Источник: office-menu.ru

Подсказки и проверки ввода данных в MS Excel

В MS Excel есть ряд очень полезных инструментов для удивительно простого создания по-настоящему сложных форм сбора информации. Однако, вот незадача — чаще всего камнем преткновения во взаимодействии с ними служат не возможности табличного редактора, а человеческая «косорукость». Пользователи стирают формулы из ячеек, вносят некорыстные данные, в общем, портят плоды наших трудов.

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

Вот и моя демонстрационная excel-таблица. Как её заполнять?

Подсказки в MS Excel

Первый способ вставки подсказки

Первым делом разберемся c подсказками. Взгляните на мою табличку. Вроде бы все просто, однако даже при такой простоте можно нагородить солидный огород. К примеру, поле «номер документ» — это №1, 1, или 22.03-1?

Самый проcтой способ вставить примечание в лист MS Excel

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

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

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

Обратите внимание: нажатие кнопки «Delete» в ячейке не удалит примечание. Избавиться от него (или изменить его текст) можно повторно щелкнув в ячейке правой кнопкой мыши и выбрав пункт «Удалить примечание» или «Изменить примечание».

Второй способ вставки подсказки

Ещё один способ ввести подсказку интересен нам только с точки зрения того, о чем пойдет разговор дальше (фильтрации ввода), тем не менее знать о нем полезно.

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

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

Теперь, при щелчке на ячейке с подсказкой (обратите внимание — отмеченная таким способом ячейка не помечается красным треугольником, и выглядит так же как любая другая) также будет выведен его текст в желтом прямоугольнике. Удалить и изменить примечание введенное через «Проверку данных» с помощью правой кнопки мыши, нельзя.

Проверка по числу введенных символов

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

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

Проверка по числу введенных символов в MS Excel

Первое, что может оказаться полезным, это проверка по длине введенного текста. Выбираем пункт «Длина текста» и указываем любой диапазон ограничений. К примеру, я хочу чтобы текст в поле «Сообщение» был как можно более кратким, но в то же время ячейка не могла оставаться пустой. Значения от 3-х до 25 введенных символов, этого вполне хватит, чтоб вписать что-то вроде «Напоминание об оплате» (21 символ), а при попытке ввести более длинный текст, MS Excel выдаст окно-предупреждение и не даст завершить ввод.

Слишком длинный текст! Excel стоит на страже и не пропустит такой очевидной ошибки

Проверка по числу

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

Читайте также:  Как включить вкладку разработчик в excel 2016

Проверка по числу в Excel

Фильтрация на ввод чисел работает ничуть не хуже

Выбор из имеющегося списка значений

Самая мощная и интересная проверка ввода в MS Excel — выборка из выпадающего списка. Она подойдет не для всех типов ячеек, но там где нужна точность, а диапазон значений не слишком велик, выборка по списку это то что надо.

А теперь, сделаем проверку по выпадающему списку в MS Excel. Данные введем вручную, через точку с запятой.

Выбираем в «Проверке данных» вкладку «Параметры«, и указываем «Тип данных» → «Список». Ввести «предустановленные» значения можно двумя способами:

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

…или выберем из диапазона

Второй способ более интересен, так как данные «спрятаны» от пользователя, зато набить их больше десятка подряд — проблематично. Первый способ позволяет без проблем задать намного больший список для выбора данных, однако прежде придется этот список разместить где-то в документе (каждое значение на отдельной строке), например на отдельном «служебном» листе в той же книге MS Excel.

Выбор из списка на листе MS Excel. Других данных ввести не удастся.

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

Кстати, можно изменить и её текст — для этого перейдите на вкладку «Сообщение для вывода» в «Проверке данных», как мы уже делали при «втором способе» вставке подсказок, измените текст на требуемый.

Источник: bussoft.ru

Всплывающая подсказка в ячейке

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

На создание такой подсказки меня навел однажды вопрос на одном из форумов – как можно сделать удобный перевод текста из ячейки, чтобы он показывался при наведении на ячейку мышью? Не выделении – именно наведении. Можно использовать примечания(вкладка Рецензирование -Создать примечание). Но в случае с примечаниями есть несколько не очень удобных моментов: размеры примечания придется подгонять для каждой подсказки отдельно; примечания как правило будут показываться справа от ячейки, а не чуть ниже; при большом количестве примечаний файл может значительно увеличиться в размерах и некоторые возможности будут мешать их корректному отображению(например, если закрепить области, то примечание может и съехать и “срезаться”). Первое, что пришло в голову – а что если сделать как в гиперссылках (Что такое гиперссылка?)? Если точнее – что если именно эти подсказки использовать? Но как же тогда делать гиперссылку? Куда? Все просто – я решил, что можно создать гиперссылку на ячейку, в которой сама гиперссылка. Тогда даже при нажатии на ячейку с гиперссылкой нас никуда не перекинет. Я покажу как это можно сделать вручную и как это можно сделать при помощи кода VBA.
Для начала немного об исходных данных. У нас есть лист с ячейками, в которых надо создать подсказки и есть лист “справочник”, в котором указано для каких значений какие должны быть подсказки.

РУЧНОЕ СОЗДАНИЕ ПОДСКАЗКИ
Выделяем ячейку, подсказку для которой хотим создать -правый клик мыши -Гиперссылка (Hyperlink). Откроется окно добавления гиперссылки.

Выбираем местом в документе
Введите адрес ячейки – указываем адрес той ячейки, в которой хотим создать подсказку
Или выберите место в документе – указываем тот лист, на котором наша ячейка, в которой создаем подсказку.
Далее жмем кнопку Подсказка и в появившемся окне вписываем текст всплывающей подсказки -Ок. Так же в основном окне жмем Ок. Подсказка создана.
Ниже небольшой ролик, в котором показано как это все делается пошагово:

В этом способе все хорошо, кроме двух вещей:

  1. Если подсказок много, но создавать их весьма непростое занятие. Необходимость листа “справочник” при ручном методе так же ставится под сомнение
  2. При создании гиперссылки формат ячейки автоматом изменяется. А для нашей цели это в большинстве случаев не надо. Это можно обойти, создавая подсказки вторым способом – при помощи кодов VBA.

СОЗДАНИЕ ПОДСКАЗКИ АВТОМАТИЧЕСКИ КОДОМ VBA
Здесь особо рассказывать нечего – лучше сначала скачать файл, приложенный к статье. Там есть лист “справочник”, в котором забита пара значений для подсказок и лист, в котором подсказки создаются.
Как это работает. Выделяются ячейки для создания подсказок(в примере это D15:D16 ). И кнопкой запускается код, который в выделенных ячейках создаст всплывающие подсказки. Сам принцип: код просматривает каждую выделенную ячейку и если она не пустая запоминает её значение. Ищет это значение в первом столбце листа “справочник” и если находит – создает гиперссылку и значение из второго столбца листа “справочник”(в строке с найденным значением) вставляет в качестве подсказки к гиперссылке.
Но помимо этого код перед созданием гиперссылки запоминает большую часть форматирования текста в ячейке и после создания гиперссылки возвращает его. Хотя и здесь не без ложки дегтя – код не сможет корректно обработать и вернуть смешанное форматирование (например, разный цвет шрифта в одной ячейке, разный стиль и т.п.). Но подсказки при этом все равно будут созданы.
Что следует знать: код ищет каждое значение ячейки полностью, а не каждое слово отдельно. Это значит, что если в ячейке будет записано “аннуитентный платеж”, а не просто “аннуитентный” как в примере – то подсказка для такой ячейки не будет создана.
Если добавить или изменить значения в листе “справочник”, то для того, чтобы подсказки обновились необходимо запустить код заново. Внесение изменений в сам код при этом не требуется.
Созданные подсказки останутся в файле даже если удалить сам код. Т.е. если создать подсказки в файле, а затем файл переслать другому человеку – он сможет использовать подсказки без дополнительных действий.

Всплывающая подсказка в ячейке (59,0 KiB, 1 973 скачиваний)

Если кому-то лень качать сам пример – привожу так же код:

Источник: www.excel-vba.ru

Выпадающий список в Excel с помощью инструментов или макросов

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

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

Создание раскрывающегося списка

Путь: меню «Данные» – инструмент «Проверка данных» – вкладка «Параметры». Тип данных – «Список».

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

  1. Вручную через «точку-с-запятой» в поле «Источник».
  2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
  3. Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов даст такой результат.

Выпадающий список в Excel с подстановкой данных

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

Читайте также:  Как поделить в excel

  1. Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
  2. Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:
  3. Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

  1. Сформируем именованный диапазон. Путь: «Формулы» – «Диспетчер имен» – «Создать». Вводим уникальное название диапазона – ОК.
  2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
  3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
  4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
  5. Сохраняем, установив тип файла «с поддержкой макросов».
  6. Переходим на лист со списком. Вкладка «Разработчик» – «Код» – «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

  1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
  2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.

  1. Создадим первый выпадающий список, куда войдут названия диапазонов.
  2. Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
  3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.

Выбор нескольких значений из выпадающего списка Excel

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

  1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
  2. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
  3. Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.

Private Sub Worksheet_Change( ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range( “C2:C5” )) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
Target = Target & “,” & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub

Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

Выпадающий список с поиском

  1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
  2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
  3. Жмем «Свойства» – открывается перечень настроек.
  4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

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

Источник: exceltable.com

Как установить пользовательский текст всплывающей подсказки в MS Excel

Как установить пользовательский текст всплывающей подсказки в MS Excel

Вы можете вставлять гиперссылки в MS Excel так же, как в MS PowerPoint или MS Word. Фактически, если вы вставите ссылку в ячейку, Excel автоматически преобразует ее в интерактивную ссылку. Вставка ссылки в ячейку — это быстрый и простой способ вставки гиперссылки.

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

Настраиваемая подсказка для гиперссылки

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

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

Например, если вы хотите добавить ссылку на AddictiveTips, вы напишите AddictiveTips в поле «Текст для отображения» и вставьте его. ссылка на сайт в поле Адрес.

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

Так выглядит подсказка для настраиваемой гиперссылки в MS Excel,

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

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

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

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

Источник: crazygamer.ru

Всплывающие подсказки в excel

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

Выпадающий список с контекстным поиском

Итак, речь пойдёт о выпадающем списке (так называемый combo box), в который встроена возможность динамического поиска по подстроке, которую пользователь вводит с клавиатуры. Посмотрите пример, в котором мы имеем топ 300 крупнейших городов России. На анимированной иллюстрации видно, как мы динамически сужаем список выбора, вводя подстроку “кр” или “ниж”, экономя огромное количество времени. Более того, список меняется после ввода каждого нового символа! Выглядит чрезвычайно привлекательно и профессионально, не так ли? Давайте разбираться, как это устроено.

Файл примера

Скачать

Пошаговая инструкция

Предварительные замечания

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

Шаг 1. Готовим таблицу для списка

Подготовьте таблицу с четырьмя колонками: Город (или то, что вам нужно), Статус , Индекс , Фильтр . Заполните столбец Город значениями. В остальных трёх колонках будут формулы, которые мы обсудим ниже. Я всем рекомендую использовать умную таблицу, так как это значительно проще.

Шаг 2. Формулы для столбца Статус

На примере ячейки F2 рассмотрим формулу, аналогичную для всего столбца Статус (столбец F ). Из F2 формулу можно протягивать вниз до конца, а в случае умной таблицы Excel это сделает за вас. Это также относится ко всем формулам, которые мы будем обсуждать в этой статье.

$B$2 – ячейка, с которой будет связан выпадающий список (добавляется на шаге 6). Что значит связано? Всё, что вы введёте в выпадающий список, тут же отразится в ячейке B2 .

Формула ПОИСК вернёт ошибку, если содержимое B2 не найдено в $E2 . ЕОШИБКА перехватит ошибку и вернёт ИСТИНА, если действительно была ошибка, и – ЛОЖЬ, если строка таки была найдена. Функция НЕ делает из истины ложь и наоборот (инверсирует результат). Таким образом, мы получим в этом столбце ИСТИНА, если подстрока найдена в текущем городе, и наоборот. Обратите внимание, что пустая подстрока содержится в любой строке, поэтому все ячейки столбца Статус имеют значения ИСТИНА, когда мы не ввели ещё ничего в B2 .

= НЕ( ЕОШИБКА ( ПОИСК ( $B$2 ; $E2 ) ) )

=NOT( ISERROR( SEARCH( $B$2; $E2) ) )

= НЕ ( ЕОШИБКА ( ПОИСК ( $B$2 ; [@ Город ]) ) )

=NOT( ISERROR( SEARCH( $B$2; [@Город]) ) )
[@ Город ] – на языке структурных формул умных таблиц это ссылка на ячейку столбца Город в той же строке, в которой находится сама формула. Поскольку ссылка идёт внутри таблицы, то имя самой таблицы в формуле можно не использовать. В остальном всё – тоже самое.

Шаг 3. Формула для столбца Индекс

Если B2 содержит подстроку поиска, то в столбце Статус не все ячейки примут значение ИСТИНА. Статус ИСТИНА будет только там, в чьи названия городов входит соответствующая подстрока. А в столбце Индекс мы рассчитываем номер по порядку для всех строк, которые содержат искомую подстроку. Например, на рисунке ниже B2 содержит “ни”, что заставляет столбец Статус быть истинным у строк с городами Нижний Новгород , Калининград , Магнитогорск и т.д., а в столбце Индекс мы начинаем считать факты срабатываний в F : Нижний Новгород – первое срабатывание, Калининград – второе и так далее.

Функция ЕСЛИ отсекает все значения в F , которые не равны ИСТИНА. Функция СЧЁТЕСЛИ подсчитывает количество значений ИСТИНА в F .

Обычный диапазон Умная таблица

= ЕСЛИ( $F2 ; СЧЁТЕСЛИ ( $F$2:$F2 ; ИСТИНА ); “”)

=IF( $F2; COUNTIF( $F$2:$F2; TRUE ); “”)

= ЕСЛИ ( [@ Статус ]; СЧЁТЕСЛИ ( $F$2 :[@ Статус ]; ИСТИНА ); “”)

=IF( [@Статус]; COUNTIF( $F$2:[@Статус]; TRUE ); “”)

Обратите внимание, что диапазон условия в СЧЁТЕСЛИ введен скользящий – вторая координата не закреплена – и во время протягивания она растёт пропорционально таблице. За счёт этого трюка мы получаем механизм подсчёта значения ИСТИНА. Например, 6-я строка будет подсчитывать ИСТИНУ по диапазону $F$2:$F6 (там одно значение – от Нижнего Новгорода ), а 41-я строка будет подсчитывать ИСТИНУ уже по диапазону $F$2:$F41 (а там уже 2 значения – от Нижнего Новгорода и от Калининграда ). Вот суть механизма. Это полезный приём, который стоит запомнить.

Шаг 4. Формула для столбца Фильтр

Теперь наша задача, опираясь на столбец Индекс , сформировать в столбце H отфильтрованный список городов, который необходимо показывать в выпадающем списке.

Обычный диапазон Умная таблица

= ЕСЛИОШИБКА( ИНДЕКС ( стлГород ; ПОИСКПОЗ ( ЧСТРОК ( $G$2:$G2 ); стлИндекс ; 0) ); “”)

=IFERROR( INDEX( стлГород; MATCH( ROWS($G$2:$G2); стлИндекс; 0) ); “” )

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

= ЕСЛИОШИБКА ( ИНДЕКС ( [ Город ]; ПОИСКПОЗ ( ЧСТРОК ( $G$2 :[@ Индекс ]); [ Индекс ]; 0) ); “”)

=IFERROR( INDEX( [Город]; MATCH( ROWS($G$2:[@Индекс]); [Индекс]; 0) ); “” )

Не путайте: [ Индекс ] – ссылка на весь столбец, а [@ Индекс ] – ссылка на ячейку из этого столбца в текущей строке. Никакие дополнительные именованные диапазоны нам создавать нет никакой необходимости, так как мы пользуемся встроенным в умные таблицы сервисом при ссылке на столбцы.

Формула ЧСТРОК ( $G$2:$G2 ) используется для генерации последовательных номеров от 1 (для второй строки) до N (в строке N+1), равному количеству найденных подстрок. Просто генерируется диапазон соответствующего размера, а формула ЧСТРОК возвращает его высоту в строках.

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

Если же при извлечении возникает ошибка (текущая строка находится ниже строки N+1), то формула возвращает пустую строку. За это отвечает ЕСЛИОШИБКА .

Источник: perfect-excel.ru

Добавить комментарий

Adblock
detector
Обычный диапазон Умная таблица