000818.ru

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

Как ограничить записи, чтобы разрешить только значения, которые начинаются / заканчиваются определенным текстом в Excel?

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

VBA AutoFilter Multiple Criteria from Filter Drop-down Menu Listbox

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

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

1) Выделите ячейку, которую вы хотите отформатировать, и измените параметр форматирования чисел.

Когда вы считываете данные (из ячейки в Excel или VBA), они содержат введенные вами ведущие нули.

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

Выберите нужный квадрат (квадраты), щелкнув по ним мышью.

Перейдите на вкладку Главная вверху. Во втором поле справа есть опция Формат полностью справа. Нажмите на нее.

Нажмите на Формат ячеек. На первой вкладке должно быть написано Номер . На вкладке Число есть опция Текст . Нажмите на него. Теперь нажмите OK в нижней части страницы, и все готово.

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

Читайте так же:
Как напечатать чистый лист с линиями сетки?

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

Это мое самое простое и эффективное решение. Потому что Excel изменит цифры, если вы уже ввели данные. Он работает только при вводе после изменения форматирования.

  1. Добавить новый столбец
  2. Выделить столбец и изменить формат на текстовый, щелкнув правой кнопкой мыши и отформатировав ячейки
  3. Взять номера элементов или столбцов и скопировать их
  4. Вставить их в блокнот (чтобы удалить форматирование)
  5. Скопировать данные в блокнот и вставить их в новый столбец Excel

Это не идеальное решение, но с первого раза оно сработало.

В левой ячейке не видно ведущего 0, а я хотел добавить ведущий 0. Исправление заключается в использовании формулы =»0″&A3(номер ячейки)

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

excel screenshot

Если перед числом, начинающимся с нуля, добавляется апостроф, то все число сохраняется, но апостроф не публикуется. Например, если ваш номер 08753, Excel усечет номер до 8753, если вы не введете ‘ 08753. Затем Excel заполнит ячейку 08753. Расслабьтесь!

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

  1. Щелкните ячейку правой кнопкой мыши, чтобы отформатировать ее.
  2. Нажмите Форматирование ячеек.
  3. Выберите вкладку Числа.
  4. Нажмите для корректировки выбора.
  5. Нажмите одну из опций, перечисленных справа.
  6. Выберите тип текстового поля и перезапишите его содержимое числом «00000».
  7. Нажмите OK.

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

Читайте так же:
Как объединить ячейки без центрирования содержимого в Excel?

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

Многие говорили, что это был непростой маневр. Вот предложение.

Скажите, что ваши ошибочные почтовые индексы (без ведущего 0) находятся в колонке А.

  1. Вставить заменяющий столбец, давайте используем B.
  2. В столбце B вставляем формулу =A1*1 и копируем вниз, чтобы поймать все нули. После этого вы должны увидеть ведущие нули, но это еще не все. Их все равно не будут читать.
  3. Скопируйте столбец B, затем вставьте содержимое столбца B в новый рабочий лист.
  4. Сохраните новый лист как файл .CSV (при сохранении нажмите «Сохранить как тип» и выберите CSV с разделителями-запятыми).
  5. Откройте новый CSV-файл, который вы только что создали.
  6. Скопируйте, а затем вставьте содержимое прямо над вашим дерьмом в плохом формате zip-кода. (Это означает, что вы не можете получить отфильтрованное место вставки, помните). Содержимое столбцов должно совпадать, иначе данные не будут совпадать.
  7. Нажмите и выберите все почтовые индексы, которые вы только что вставили.
  8. Выберите Формат ячеек и выберите: Специальный, затем Почтовый индекс
  9. Мы надеемся, что ваши почтовые индексы будут включать 5-значное форматирование и должны быть читаемы в формулах и т.п.

Это долгое решение, которое сработало для меня. Я надеюсь, что это сработает для вас!

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

Если вы сохраните его как файл .xlsx, его можно будет открыть с помощью Excel и сохранить мастер 0.

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

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

Срезы

Срезы – это те же фильтры, но вынесенные в отдельную область и имеющие удобное графическое представление. Срезы являются не частью листа с ячейками, а отдельным объектом, набором кнопок, расположенным на листе Excel. Использование срезов не заменяет автофильтр, но, благодаря удобной визуализации, облегчает фильтрацию: все примененные критерии видны одновременно. Срезы были добавлены в Excel начиная с версии 2010.

Создание срезов

В Excel 2010 срезы можно использовать для таблиц pivot, но в версии 2013 можно создать срез для любой таблицы.

Выполните следующие действия:

    Выделить в таблице одну ячейку и выбрать вкладку Конструктор [Design].

Вставка среза в Excel

  1. В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.
    Вставка срезов

Форматирование срезов

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

Форматирование срезов

  1. Выберите кнопку с нужным стилем форматирования.

Чтобы удалить вырезку, выделите ее и нажмите клавишу Delete.

Решение

Если появится вышеуказанное диалоговое окно, выполните одно из перечисленных действий.

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

Что происходит при нажатии кнопки "Да"

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

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

Переименуйте конфликтующий диапазон имен, нажав кнопку нет. Откроется следующее диалоговое окно.

Читайте так же:
Как найти самую длинную или самую короткую текстовую строку в столбце?

В поле Новое имя введите новое имя для области. Новое имя не будет принято, если

Начинаются с буквы или подчеркивания,

— Это ссылка на ячейку, например, на A1, A123 или KSFD21.

Содержит пробелы или знаки (,/, *, [,].) или

Совпадает с именем другого диапазона в книге, в которую вы копируете.

Нажмите кнопку ОК.

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

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

Отфильтруйте значения по первой букве или последнему символу с помощью функции фильтрации
Отфильтруйте значения по первой букве или последнему символу с кодом VBA

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

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

1. Выделите весь столбец, значения которого вы хотите отфильтровать по первой букве или последнему символу, а затем нажмите Данные >Фильтр . См. Снимок экрана:

2. Теперь небольшой выпадающий значок отображается в первой ячейке выбранного столбца. Щелкните значок раскрывающегося списка>Текстовые фильтры >Начинается с / Заканчивается на . См. Снимок экрана:

Примечания

Фильтровать значения по первой букве или последнему символу с кодом VBA

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

Чтобы открыть окно Microsoft Visual Basic, нажмите Alt + F11 одновременно.

2. В окне Приложение Microsoft Visual Basic нажмите Вставить >Модуль . Затем скопируйте и вставьте приведенный ниже код в окно модуля.

Код VBA: фильтрация по первому или последнему символу

Примечания:

Значения фильтрации по первой букве или последнему символу с помощью Kutools for Excel

Кроме того, Super Filter Kutools for Excel поможет вам фильтровать значения по первой букве или последнему символу в Excel. Пожалуйста, сделайте следующее

Читайте так же:
Как найти наименее или наиболее частое / частое значение в Excel?

1. Щелкните Enterprise >Суперфильтр , чтобы включить панель Суперфильтра. См. Снимок экрана:

I panelet Суперфильтр

A: щелкните , чтобы выбрать диапазон, который нужно отфильтровать. Здесь мы выбираем столбец B;

В первом выпадающем списке укажите название выбранного столбца;

C: Выберите Текст во втором выпадающем списке;

Выберите Starts with или Ends with в третьем раскрывающемся окне. список;

В последнем поле введите первую букву или последний символ, который вы хотите отфильтровать;

Фильтрация может быть инициирована нажатием кнопки Filter. См. скриншот:

Любое значение, начинающееся или заканчивающееся указанной буквой или символом, немедленно отфильтровывается.

Замечания

1. В этой утилите Super Filter (Суперфильтр) AND и OR являются взаимоисключающими.

2. Super Filter объединяет несколько условий фильтрации, а затем одновременно фильтрует значения в соответствии с этими условиями. См. скриншот:

3. Чтобы отменить фильтр и восстановить данные, нажмите кнопку Очистить.

Совет. Получите бесплатную пробную версию этой утилиты, сначала загрузив ее бесплатно, а затем выполнив процедуру, описанную выше.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector