При работе с большими объемами информации, особенно при ее обработке с помощью формул или макросов, важно, чтобы в данных не было ошибок, которые могут повлиять на результат. Сложности с получением корректных исходников возникают, когда они поступают из неструктурированных источников (плохо спроектированные базы данных, ручное заполнение таблиц и форм людьми, не заинтересованными в результате). Особенно сложно получить правильный макет для конкретного скина (шаблона). Например, дату в виде строки можно записать несколькими способами:
- 01.01.2001;
- 01.01.2001;
- 1 января 2001 г и т д
В статье описан функционал, упрощающий процесс сбора и проверки данных в Excel.
Где находится?
Чтобы настроить параметры проверки входных значений, на вкладке «Данные» в области «Работа с данными» нажмите иконку «Проверка данных» или выберите аналогичный пункт из выпадающего меню:
На экране появится окно с настройками по умолчанию, где в качестве типа данных можно использовать любое значение:
Настройка условия проверки
Изначально нужно выбрать тип проверяемых данных, что будет первым условием. Всего есть 8 вариантов:
- Все;
- Настоящий номер;
- Готовый;
- Дата;
- Час;
- Длина текста;
- Другой.
В зависимости от выбранного пункта есть возможность выбрать дополнительные условия. Для чисел, дат, времени и длины текста можно указать ограничения: меньше, больше, диапазон и т.д. Источник должен быть связан со списком, а элемент «другое» предлагает ввести формулу, возвращающую логическое значение.
Самый необычный тип — выпадающий список.
Позволяет выбрать указанные значения в качестве источника и ограничить допустимые для него значения. Также возможно создание динамических выпадающих списков.
Всплывающая подсказка ячейки Excel
Функция проверки данных в Excel позволяет настраивать всплывающие подсказки для ячеек электронной таблицы. Для этого перейдите на вторую вкладку окна проверки введенных значений: «Сообщение для ввода».
На изображении показан пример возможного сообщения для ячейки, содержащей целые числа от 1 до 31 (настройки вкладки параметров). Заголовок и сообщения указываются на ваше усмотрение, правил их оформления нет. Не забудьте поставить галочку «Показывать всплывающую подсказку, если ячейка текущая», иначе подсказка будет отключена.
Пример всплывающей подсказки в Excel:
Вывод сообщения об ошибке
Последняя вкладка окна проверки данных позволяет настроить поведение и вывод сообщений при обнаружении ошибочного значения.
Есть три варианта сообщения, которые отличаются по поведению:
Стоп является сообщением об ошибке и допускает только 2 действия: отменить ввод и повторить ввод. В случае аннулирования новое значение будет заменено на предыдущее. Повторный ввод позволяет исправить новое значение.
Предупреждение более снисходительно, чем остановка, поскольку оно позволяет оставить значение, не соответствующее критериям проверки, после того, как пользователь подтвердит ввод.
Сообщение отображает ошибку как простую информацию и дает вам возможность отменить последнее действие.
Заголовок и сообщение заполняйте по своему усмотрению.
Пример вывода одной и той же ошибки, но под разными типами:
Подсказки и проверки ввода данных в MS Excel
MS Excel имеет ряд очень полезных инструментов для создания действительно сложных форм сбора данных с удивительной легкостью. Однако вот в чем загвоздка: в большинстве случаев препятствием при взаимодействии с ними являются не возможности редактора электронных таблиц, а человеческое косоглазие. Пользователи удаляют формулы из ячеек, вводят неинтересные данные — в общем, портят плоды нашего труда.
К счастью, они делают это не злонамеренно, и все, что нужно нам, разработчикам, — это давать им советы и заранее подготовленные ответы. Как это сделать, я расскажу в этой статье.
Вот моя демонстрационная таблица Excel. Как его заполнить?
Подсказки в MS Excel
Первый способ вставить трек
Сначала рассмотрим подсказки. Взгляните на мою тарелку. Вроде бы все просто, но даже при такой простоте можно построить добротный сад. Например, является ли поле «номер документа» числом 1, 1 или 22.03-1?
Самый простой способ вставить заметку в лист MS Excel
Самый простой способ — щелкнуть правой кнопкой мыши по нужной ячейке и выбрать «Вставить заметку». В появившееся поле достаточно набрать требуемый текст, и если пользователь наведет курсор мыши на эту ячейку, то текст тут же появится во всплывающей подсказке.
Обратите внимание: ячейка с комментарием отмечена специальным значком — маленьким красным треугольником в правом верхнем углу. Сам текст заметки может быть отформатирован так же, как и любой другой: вы можете сделать его жирным или курсивом, чтобы подчеркнуть наиболее важные моменты вашего сообщения.
Примечания можно удалить только из контекстного меню, вызываемого правой кнопкой мыши
Обратите внимание, что нажатие кнопки Удалить в ячейке не приведет к удалению примечания. Вы можете избавиться от него (или изменить его текст), снова щелкнув ячейку правой кнопкой мыши и выбрав «Удалить примечание» или «Редактировать примечание».
Второй способ вставки трека
Другой способ ввода подсказки нам интересен только с точки зрения того, о чем дальше будет продолжаться разговор (фильтрация ввода), однако знать его полезно.
Примечания в Excel с проверкой данных
Поместите курсор мыши в выделенную ячейку, в панели «Данные» в группе «Работа с данными», откройте инструмент «Проверка данных» и заполните поля «Заголовок» и «Текст сообщения» в «Сообщении для ввода» вкладка». Нажмите ОК».
Теперь при нажатии на ячейку с подсказкой (обратите внимание, отмеченная таким образом ячейка не отмечена красным треугольником и выглядит так же, как и любая другая), ее текст также будет отображаться в желтом прямоугольнике. Невозможно удалить и изменить заметку, введенную через «Проверку данных» правой кнопкой мыши.
Проверка по числу введенных символов
Советы — это хорошо, но как насчет более «продвинутых» пользователей, которые не читают советы? Остается только одно: бить по рукам.. то есть использовать входной фильтр, я хотел сказать.
Снова откройте инструмент проверки данных, перейдите на вкладку «Параметры» и посмотрите на наши параметры, чтобы ограничить выбор пользователя.
Проверка по количеству введенных символов в MS Excel
Первое, что может быть полезно, это проверка длины вводимого текста. Выберите пункт «Длина текста» и укажите любой диапазон ограничений. Например, я хочу, чтобы текст в поле «Сообщение» был максимально лаконичным, но при этом ячейку нельзя было оставлять пустой. Были введены значения от 3 до 25 символов, этого достаточно, чтобы ввести что-то вроде «Напоминание об оплате» (21 символ), а при попытке ввода более длинного текста MS Excel выдаст окно с предупреждением и не позволит завершить вход.
Текст слишком длинный! Excel на страже и не пропустит столь очевидную ошибку
Проверка по числу
Еще один хороший метод фильтрации, который, несомненно, пригодится: фильтр по номеру. Установите фильтр, аналогичный показанному на изображении, и навсегда избавьтесь от проблем с буквами, пробелами и лишними символами, появляющимися в поле цены.
Проверить по номеру в Excel
Фильтрация для ввода чисел работает так же хорошо
Выбор из имеющегося списка значений
Самая мощная и интересная проверка ввода в MS Excel — это выбор из выпадающего списка. Он подходит не для всех типов ячеек, но там, где нужна точность и диапазон значений не слишком велик, выбор списка — то, что вам нужно.
А теперь давайте проверим выпадающий список в MS Excel. Введите данные вручную, разделив их точкой с запятой.
Выберите вкладку «Параметры» в разделе «Проверка данных» и укажите «Список» «Типа данных». Вы можете ввести «предустановленные» значения двумя способами:
- Выберите диапазон данных с помощью кнопки «Диапазон данных».
- Введите значения из выпадающего списка вручную, разделив их точкой с запятой.
…или выберите из ассортимента
Второй способ более интересен, так как данные «спрятаны» от пользователя, но заполнять их более десятка подряд проблематично. Первый способ позволяет легко настроить гораздо больший список для выбора данных, но сначала этот список нужно разместить где-нибудь в документе (каждое значение на отдельной строке), например, на отдельном «служебном» листе в сам документ Рабочая тетрадь MS Excel.
Выбор из списка на листе MS Excel. Никакие другие данные не могут быть введены.
Вот и все, теперь вы можете заполнить ячейку только теми значениями, которые мы указали в списке. При попытке ввода данных вручную MS Excel выдаст нам знакомую ошибку.
Кстати, вы также можете изменить свой текст; для этого перейдите на вкладку «Сообщение для вывода» в «Проверка данных», как мы уже делали со «вторым способом» для вставки предложений, измените текст на нужный.
Всплывающая подсказка в ячейке
Сегодня я хочу поделиться маленькой хитростью, которая поможет вам сделать удобные и красивые предложения по сотам:
Однажды вопрос на одном из форумов попросил создать такую подсказку: как сделать удобный перевод текста ячейки, чтобы он отображался при наведении курсора на ячейку? Никакого выбора, просто плавание. Можно использовать примечания (вкладка «Обзор» — «Создать примечание»). А вот в случае с нотами есть несколько не очень удобных моментов: размер ноты придется подгонять для каждого трека отдельно; заметки обычно отображаются справа от ячейки, а не сразу под ней; при большом количестве заметок файл может значительно увеличиться в размере, а некоторые особенности будут мешать его правильному отображению (например, если вы исправите области, заметка может сместиться и «обрезаться»). Первый, Что с тобой случилось? Что, если сделать это как в гиперссылках (что такое гиперссылка?)? Точнее, а если использовать эти советы? Но как тогда сделать гиперссылку? Где? Все просто: я решил, что можно создать гиперссылку на ячейку, в которой находится гиперссылка. Так что даже когда мы нажимаем на ячейку с гиперссылкой, нас никуда не перебросит. Я покажу, как это можно сделать вручную и как это можно сделать с помощью кода VBA.
Сначала немного об исходных данных. У нас есть лист с ячейками, в которых должны создаваться подсказки, и есть «справочный» лист, в котором указано, какие значения должны иметь подсказки для каких значений.
СОЗДАНИЕ ТРЕК ВРУЧНУЮ
Выберите ячейку, для которой мы хотим создать подсказку, щелкните правой кнопкой мыши «Гиперссылка». Откроется окно Добавить гиперссылку.
Выберите место в документе
Введите адрес ячейки: указываем адрес ячейки, в которой хотим создать трек
Либо выделяем место в документе — указываем лист, на котором находится наша ячейка, на которой создаем дорожку.
Далее нажмите кнопку Подсказка и в появившемся окне введите текст всплывающей подсказки — Ок. Также в главном окне нажмите ОК. Трек создан.
Ниже небольшое видео, показывающее, как это делается шаг за шагом:
Таким образом, все хорошо, за исключением двух вещей:
- Да, советов много, но создать их — очень сложная задача. Необходимость «ручного» листа в ручном методе также ставится под сомнение
- При создании гиперссылки форматирование ячейки изменяется автоматически. Для наших целей в большинстве случаев это не требуется. Этого можно избежать, создавая всплывающие подсказки вторым способом: с помощью кодов VBA.
СОЗДАЙТЕ ТРЕК АВТОМАТИЧЕСКИ С КОДОМ VBA
Рассказывать тут особо нечего — лучше сначала скачать прикрепленный к статье файл. Есть «справочный» лист, который содержит пару значений для подсказок, и лист, на котором вы создаете подсказки.
Как это работает. Выбираются ячейки для создания подсказок (в примере это D15:D16). И кнопка запускает код, который будет создавать всплывающие подсказки в выбранных ячейках. Сам принцип: код смотрит на каждую выделенную ячейку и, если она не пуста, запоминает ее значение. Он ищет это значение в первом столбце «справочного» листа и, если находит, создает гиперссылку и вставляет значение из второго столбца «справочного» листа (на строку с найденным значением) как намек на гиперссылку.
Но помимо этого код запоминает большую часть форматирования текста в ячейке до создания гиперссылки и возвращает его после создания гиперссылки. Однако здесь есть и ложка дегтя: код не сможет должным образом обработать и вернуть смешанное форматирование (например, другой цвет шрифта в ячейке, другой стиль и т д.). Но предложения все равно будут генерироваться.
Что вам нужно знать: код ищет значение каждой ячейки целиком, а не каждое слово отдельно. Это означает, что если в ячейке указано «аннуитетный платеж», а не просто «аннуитетный платеж», как в примере, для этой ячейки не будет создана подсказка.
Если вы добавляете или меняете значения в «справочном» листе, для обновления подсказок вам нужно снова запустить код. Никаких изменений в самом коде не требуется.
Сгенерированные подсказки останутся в файле, даже если код будет удален. Если вы создадите подсказки в файле, а затем отправите файл кому-то еще, они смогут использовать подсказки без дополнительных действий.
Cell Tooltip (59,0 КБ, 1973 загрузки)
Если кому лень качать сам пример, так же даю код:
Выпадающий список в Excel с помощью инструментов или макросов
Под выпадающим списком понимается содержимое многозначной ячейки. Когда пользователь нажимает стрелку вправо, появляется определенный список. Вы можете выбрать конкретное.
Очень полезный инструмент Excel для проверки введенных данных. Для повышения удобства работы с данными возможности выпадающих списков позволяют: подстановку данных, отображение данных из другого листа или файла, наличие функции поиска и зависимостей.
Создание раскрывающегося списка
Путь: меню «Данные» — инструмент проверки данных — вкладка «Параметры». Тип данных «Список».
Вводить значения, из которых будет формироваться выпадающий список, можно по-разному:
- Вручную через «точку с запятой» в поле «Источник».
- Введите значения заранее. А в качестве источника укажите диапазон ячеек со списком.
- Дайте диапазону значений имя и введите это имя в поле источника.
Любой вариант даст одинаковый результат.
Выпадающий список в Excel с подстановкой данных
Вам нужно сделать выпадающий список со значениями из динамического диапазона. Если в существующий диапазон вносятся изменения (добавляются или удаляются данные), они автоматически отражаются в выпадающем списке.
- Выберите диапазон для раскрывающегося списка. В главном меню находим инструмент «Форматировать как таблицу».
- Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Важно иметь заголовок. В нашем примере это ячейка A1 со словом «Деревья». То есть вы должны выбрать стиль таблицы со строкой заголовка. Получаем следующий тип диапазона:
- Ставим курсор в ячейку, где будет располагаться выпадающий список. Откройте параметры инструмента проверки данных (путь описан выше). В поле «Источник» введите следующую функцию:
Давай попробуем. Вот наша таблица со списком на листе:
Добавим в таблицу новое значение «Ёлочка».
Теперь давайте удалим значение березы».
Реализовать задуманное нам помог «умный стол», который легко «продлить» и изменить.
Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавились в диапазон.
- Давайте создадим именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Введите уникальное имя для диапазона — ОК.
- Создайте раскрывающийся список в любой ячейке. Вы уже знаете, как это сделать. Источник — имя диапазона: =trees.
- Снимите галочки на вкладках «Входное сообщение», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
- Мы вызываем редактор Visual Basic. Для этого щелкните правой кнопкой мыши имя листа и перейдите на вкладку «Исходный текст». Или одновременно нажмите клавиши Alt + F11. Скопируйте код (просто вставьте свои параметры).
- Сохраните, установив тип файла «включен макрос».
- Перейдем к списку. Вкладка «Разработчик» — «Код» — «Макросы». Комбинация клавиш для ярлыка — Alt + F8. Выберите желаемое имя. Нажмите «Выполнить».
При вводе нового имени в пустую ячейку выпадающего списка появится сообщение: «Добавить введенное имя баобаба в выпадающий список?».
Нажмите «Да», и будет добавлена еще одна строка со значением «баобаб».
Выпадающий список в Excel с данными с другого листа/файла
Когда значения выпадающего списка находятся на другом листе или в книге, стандартный способ не работает. Решить проблему можно с помощью функции ДВССЫЛ — она сформирует правильную ссылку на внешний источник информации.
- Делаем ячейку активной там, где хотим разместить выпадающий список.
- Откройте параметры проверки данных. В поле «Источник» введите формулу: =ДВССЫЛ(«[List1.xlsx]Лист1!$A$1:$A$9”).
Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если рабочая книга с нужными значениями находится в другой папке, необходимо указать полный путь.
Как сделать зависимые выпадающие списки
Возьмем три именованных диапазона:
Это обязательное условие. Выше описано, как преобразовать обычный список в именованный диапазон (используя «Диспетчер имен»). Помните, что имя не может содержать пробелы и знаки препинания.
- Создадим первый выпадающий список, в который будут включены названия диапазонов.
- Когда вы поставите курсор в поле «Источник», перейдите на лист и поочередно выберите нужные ячейки.
- Теперь давайте создадим второй выпадающий список. В нем должны быть отражены те слова, которые соответствуют названию, выбранному в первом списке. Если «Деревья», то «граб», «дуб» и т д. Введите в поле «Источник» функцию вида =ДВССЫЛ(Е3). E3 — ячейка с названием первого диапазона.
Выбор нескольких значений из выпадающего списка Excel
Это происходит, когда вам нужно выбрать сразу несколько элементов из выпадающего списка. Рассмотрим способы реализации задачи.
- Мы создаем стандартный список, используя инструмент проверки данных. Добавляем подготовленный макрос в исходный код листа. Как это сделать описано выше. С его помощью выбранные значения будут добавлены справа от выпадающего списка.
- Для того, чтобы выбранные значения отображались рядом, вставляем другой код контроллера.
- Для того, чтобы выбранные значения отображались в одной ячейке, разделенные любыми знаками препинания, применяем такой модуль.
Private Child Worksheet_Change (Цель ByVal как диапазон)
При ошибке Возобновить Далее
Если Not Intersect(Target, Range(«C2:C5»)) ничего не значит и Target.Cells.Count = 1 Then
Приложение.EnableEvents = Ложь
новое значение = цель
Приложение.Отменить
старыйвал = цель
Если Len(oldval) 0 И oldval newVal Тогда
Цель = Цель & «,» & newVal
Остальные
Место назначения = новое значение
Это закончится, если
Если Len(newVal) = 0, то Target.ClearContents
Приложение.EnableEvents = Истина
Это закончится, если
Под конец
Не забудьте изменить диапазоны на «наши». Списки создаются классическим способом. Макросы сделают остальную работу.
Выпадающий список с поиском
- Во вкладке «Разработчик» находим инструмент «Вставка» — «ActiveX». Здесь нам нужна кнопка со списком (ориентируемся на всплывающую подсказку).
- Нажмите на значок: «Режим дизайна» активирован. Рисуем курсором (он превращается в «крестик») небольшой прямоугольник — место будущего списка.
- Нажмите «Свойства» — откроется список настроек.
- Вводим диапазон в строку ListFillRange (от руки). Ячейка, в которой будет отображаться выбранное значение, в строке LinkedCell. Для изменения шрифта и размера — Шрифт.
При вводе первых нескольких букв клавиатуры соответствующие элементы выделяются. И это далеко не все приятные моменты этого средства. Здесь можно настроить визуальное представление информации, указать в качестве источника сразу две колонки.
Источник: 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. Постараюсь рассказать обо всем максимально подробно, последовательно и понятно.
Выпадающий список с контекстным поиском
Итак, речь пойдет о выпадающем списке (так называемом поле со списком), в котором есть встроенная возможность динамического поиска подстроки, которую пользователь вводит с клавиатуры. Посмотрите на пример, где у нас 300 крупнейших городов России. Анимированная иллюстрация показывает, как мы динамически сокращаем список выбора, вводя подстроку «cr» или «lower», экономя много времени. Также список меняется после ввода каждого нового персонажа! Выглядит очень привлекательно и профессионально, не так ли? Давайте разберемся, как это работает.
Файл примера
Скачать
Пошаговая инструкция
Предварительные замечания
В файле примера выпадающий список с поиском реализован сразу в двух вариантах: для обычного диапазона (RangeSheet) и для смарт-таблицы (TableSheet). Обсудим эти варианты одновременно, указав на их отличия.
Шаг 1. Готовим таблицу для списка
Подготовьте таблицу с четырьмя столбцами: Город (или что вам нужно), Штат, Почтовый индекс, Фильтр. Заполните столбец «Город» значениями. Остальные три столбца будут содержать формулы, которые мы обсудим далее. Я рекомендую всем использовать умную таблицу, так как это намного проще.
Шаг 2. Формулы для столбца Статус
Используя в качестве примера ячейку F2, рассмотрим ту же формулу для всего столбца Статус (столбец F). Из F2 формулу можно перетащить в конец, а в случае со Smart Spreadsheet Excel сделает это за вас. Это также относится ко всем формулам, которые мы будем обсуждать в этой статье.
$B$2 — это ячейка, с которой будет связан раскрывающийся список (добавлено на шаге 6). Что значит подключен? Все, что вы введете в раскрывающийся список, будет немедленно отражено в ячейке B2 .
Формула ПОИСК вернет ошибку, если содержимое B2 не будет найдено в $E2. ISERROR поймает ошибку и вернет TRUE, если ошибка действительно была, и FALSE, если строка была найдена. Функция НЕ делает ложь из истины и наоборот (инвертирует результат). Таким образом, мы получим TRUE в этом столбце, если подстрока находится в текущем городе, и наоборот. Обратите внимание, что пустая подстрока содержится в любой строке, поэтому все ячейки в столбце Статус имеют значение ИСТИНА, если мы еще ничего не ввели в B2 .
= НЕ(ЕОШИБКА (ПОИСК ($B$2 ; $E2)))
=NOT(ISERROR(SEARCH($B$2; $E2)))
= НЕ (ЕОШИБКА (ПОИСК ($B$2 ; [@ Город])))
=NOT(ISERROR(SEARCH($B$2; [@Город])))
[@City]: на языке структурных формул смарт-таблиц это ссылка на ячейку в столбце «Город» в той же строке, что и сама формула. Поскольку ссылка идет внутри таблицы, имя таблицы в формуле можно не указывать. Все остальное то же самое.
Шаг 3. Формула для столбца Индекс
Если B2 содержит подстроку поиска, не все ячейки в столбце «Статус» будут ИСТИННЫ. Состояние TRUE будет только в том случае, если названия городов включают соответствующую подстроку. А в столбце Index вычисляем порядковый номер для всех строк, содержащих нужную подстроку. Например, на рисунке ниже B2 содержит «none», что делает столбец State истинным для строк с городами Нижний Новгород, Калининград, Магнитогорск и т д., а в столбце Index мы начинаем считать события обнаружения в F: Нижний Новгород — первое обнаружение, Калининград — второе и так далее.
Функция ЕСЛИ отсекает все значения в F, которые не являются ИСТИНА. Функция СЧЁТЕСЛИ подсчитывает количество ИСТИННЫХ значений в F .
| Нормальный диапазон | Умный стол |
= ЕСЛИ($F2 ; СЧЁТЕСЛИ ($F$2:$F2 ; ИСТИНА); “”)
=IF($F2; COUNTIF($F$2:$F2; TRUE); “”)
= ЕСЛИ ([@ Статус]; СЧЁТЕСЛИ ($F$2 :[@ Статус]; ИСТИНА); “”)
=IF([@Статус]; COUNTIF($F$2:[@Статус]; TRUE); “”)
Обратите внимание, что диапазон условия в СЧЁТЕСЛИ вводится в виде ползунка (вторая координата не фиксирована) и при перетаскивании увеличивается пропорционально таблице. Благодаря этому трюку мы получаем механизм вычисления значения TRUE. Например, 6-я строка будет считаться ИСТИННОЙ в диапазоне $F$2:$F6 (есть значение Нижний Новгород), а 41-я строка будет считаться ИСТИННОЙ уже в диапазоне $F$2:$F41 (и там уже 2 значения): из Нижнего Новгорода и из Калининграда). Вот суть механизма. Это полезный трюк, который стоит запомнить.
Шаг 4. Формула для столбца Фильтр
Теперь наша задача на основе столбца Index сформировать в столбце H отфильтрованный список городов, который должен отображаться в выпадающем списке.
| Нормальный диапазон | Умный стол |
= ЕСЛИОШИБКА(ИНДЕКС (стлГород ; ПОИСКПОЗ (ЧСТРОК ($G$2:$G2); стлИндекс ; 0)); “”)
=IFERROR(INDEX(стлГород; MATCH(ROWS($G$2:$G2); стлИндекс; 0)); “”)
Обратите внимание на динамический именованный диапазон stlCity и stlIndex, которые мы вынуждены создавать для случая диапазона, чтобы придать решению нужный уровень универсальности. Техника, с помощью которой создаются эти именованные диапазоны, обсуждается здесь.
= ЕСЛИОШИБКА (ИНДЕКС ([Город]; ПОИСКПОЗ (ЧСТРОК ($G$2 :[@ Индекс]); [Индекс]; 0)); “”)
=IFERROR(INDEX([Город]; MATCH(ROWS($G$2:[@Индекс]); [Индекс]; 0)); “”)
Не ошибитесь: [Index] — это ссылка на весь столбец, а [@Index] — это ссылка на ячейку в этом столбце в текущей строке. Нам не нужно создавать дополнительные именованные диапазоны, так как мы используем сервис, встроенный в смарт-таблицы, при обращении к столбцам.
Формула STRING ($G$2:$G2) используется для генерации последовательных чисел от 1 (для второй строки) до N (в строке N+1), равных количеству найденных подстрок. Просто генерируется диапазон соответствующего размера, и формула СТРОКИ возвращает его высоту в строках.
Формула ПОИСКПОЗ находит фактический номер строки, содержащей соответствующий индекс. Например, в колонке Фильтр мы видим Магнитогорск на третьей позиции, но фактически он взят из Е45, так как в G45 стоит цифра 3, которую мы нашли через ПОИСКПОЗ. То есть ПОИСКПОЗ сообщил нам, что Магнитогорск находится на 45-й строке, и мы извлекли его оттуда по формуле ИНДЕКС .
Если при извлечении возникает ошибка (текущая строка ниже строки N + 1), формула возвращает пустую строку. ЕСЛИОШИБКА несет за это ответственность .