Evolcom.ru

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

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

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

Здравствуйте,уважаемые читатели. За последние пять дней ко мне обратились три постоянных читателя с одним и тем же вопросом: как добавить в начало каждого из чисел массива несколько нулей? Зачем это делать — объясню. Когда числа не участвуют в расчетах, а являются идентификатором (уникальным порядковым номером) для каждой строки. Например, в таблице с товарами. Такое число точнее указывает на конкретный товар, вероятность ошибки гораздо меньше. Покажу на примере:

Посмотрите на приведенную таблицу. Наименования почти одинаковые, ошибиться в них слишком легко. А вот уникальные значения в столбце Код прекрасно идентифицируют каждую единицу товара. Так вот, часто хочется привести коды к единому виду. Например, сделать длиной в 8 символов, недостающие заменить нулями. Примерно так: из 101 сделать 00000101 . При этом, все коды станут одинаковой длины, таблица будет более «стройной». Как это сделать?

Мы будем использовать функцию TEXT(Number;Format). Он работает в таком порядке:

  1. Создаем новый столбец для отформатированных кодов. Как вставлять строки и столбцы, я рассказывал в этой статье.
  2. В первой строке этого столбца записываем: =ТЕКСТ(A2; «00000000») . Вместо А2 запишите ссылку на вашу ячейку с кодом. «00000000» (обязательно в кавычках) — это символы подстановки. Они указывают, что значение должно состоять из восьми символов. Если их не хватает — дополнить нулями слева. Функция берет значение из первого аргумента, форматирует его согласно второму аргументу и преобразует в текст.
  3. Жмем Enter и получаем такую картинку:
  4. Теперь скопируем формулу в остальные ячейки таблицы. Посмотрите, наши новые коды не утратили свою показательность, но стали подобными. И это радует глаз! Размеры всех строк выровнены, пустые места заменены нулями.

На этом можно было бы остановиться, но я хотел бы добавить кое-что еще.

Создание префиксов к коду

Этот пункт будет полезен, если Вы делаете небольшую базу данных на основе Excel. Конечно, было бы правильнее реализовать это в Microsoft Access, только в Экселе, по старинке, это бывает проще. Когда Вы оперируете большим списком кодов, присвоенных различным показателям, рекомендую добавить к коду короткий буквенный префикс. Например, код для столбца Остатки — О000001, Продажи — П000001, Накладные — Н000001 и т.п. Согласитесь, это удобно.

Читайте так же:
Как посчитать количество напечатанных страниц на листе?

Давайте исправим нашу формулу: =ТЕКСТ(A2; «Н000000») . Поставим букву «Н» вначале формата. Вот,что получается:

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

Автоматизация присвоения кода

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

Можно использовать номер строки, в которой располагается элемент и считать его кодом. Для этого используем функцию СТРОКА(Ссылка на ячейку) . Формула будет такой: =ТЕКСТ(СТРОКА(C2); «Н00000») , а результат, как на картинке:

Каковы преимущества этого метода? 100% автоматизация присвоения уникальных номеров гарантирована. Недостатки: элементы нельзя сортировать, и их можно добавлять только в конец списка. Если в существующую последовательность вносятся изменения, коды будут пересчитаны в соответствии с новой позицией элемента.

Какие методы автоматической генерации кода вы используете? Может быть, вы используете VBA или другие методы? Поделитесь своими секретами!

А я буду прощаться. Надеюсь, ответил на Ваши вопросы сполна. А если не ответил — задавайте их в комментариях, обязательно отвечу. До встречи!

Как удалить ведущие нули в Excel (5 простых способов)

Как удалить ведущие нули в Excel (5 простых способов)

У многих людей существуют отношения любви и ненависти с ведущими нулями в Excel.

Иногда это то, чего вы хотите, а иногда нет.

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

В этом учебном пособии по Excel я покажу вам, как удалить ведущие нули из чисел.

Возможные причины, по которым у вас могут быть ведущие нули в Excel

Как я уже упоминал, Excel автоматически удаляет все ведущие нули из чисел. Например, когда вы вводите в ячейку Excel значение 00100, оно автоматически преобразуется в 100.

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

В большинстве случаев это имеет смысл, так как первые нули не имеют смысла.

Но в некоторых случаях он может понадобиться.

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

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

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

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

Как удалить ведущие нули из чисел

Удаление ведущих нулей из чисел может быть выполнено разными способами.

В этом разделе я представлю пять из этих методов.

Преобразуйте текст в числа с помощью опции проверки ошибок

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

Это, пожалуй, самый простой способ избавиться от ведущих нулей.

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

Следующие действия помогут вам удалить ведущие нули из этих чисел:

  1. Выберите числа, из которых вы хотите удалить ведущие нули. Вы заметите желтый значок в верхней правой части выделения.
  2. Щелкните желтый значок проверки ошибок.
  3. Нажмите «Преобразовать в число».
Читайте так же:
Как поставить галочку или галочку в ячейку Excel?

Вот и все! Эти шаги удалят апостроф и превратят текстовые значения в числа.

Excel по умолчанию запрограммирован на удаление ведущих пробелов из любых чисел, поэтому вы увидите, что все ведущие нули будут удалены.

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

Изменение пользовательского числового форматирования ячеек

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

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

Чтобы все числа выглядели пятизначными, если у вас трехзначное число, Excel автоматически добавит два ведущих нуля.

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

Способ удаления ведущих нулей заключается в удалении существующего форматирования ячеек.

Ниже приведены шаги, которые необходимо предпринять:

  1. Выделите ячейки с числами с ведущими нулями
  2. Перейдите на вкладку "Главная"
  3. В группе "Числа" щелкните раскрывающееся меню "Формат числа".
  4. Выберите «Общие».

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

Этот метод работает только в том случае, если причина появления первых нулей связана с нестандартным форматированием чисел. Метод не сработает, если для преобразования чисел в текст используется апостроф (в этом случае используйте предыдущий метод).

Умножить на 1 (с помощью специальной техники вставки)

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

Допустим, у вас есть набор данных, показанный ниже, и вы хотите удалить все ведущие нули.

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

  1. Скопируйте любую пустую ячейку с листа
  2. Выберите ячейки, в которых у вас есть числа, из которых вы хотите удалить ведущие нули
  3. Щелкните выделение правой кнопкой мыши и выберите «Специальная вставка». Откроется диалоговое окно Специальная вставка.
  4. Нажмите на опцию «Добавить» (в группе операций).
  5. Нажмите ОК.
Читайте так же:
Как поделиться только одним конкретным листом с другими в листе Google?

Приведенные выше действия добавляют 0 к выбранному диапазону ячеек и удаляют все ведущие нули и апострофы.

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

Этот метод предназначен только для чисел. Если в ячейке есть текстовая строка, она останется неизменной.

Использование функции ЗНАЧЕНИЕ

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

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

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

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

Ниже приведена формула для удаления ведущих нулей:

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

Использование текста в столбец

Хотя функция «Текст в столбцы» используется для разбиения ячейки на несколько столбцов, ее можно также использовать для удаления ведущих нулей.

Представьте себе, что у вас есть следующий набор данных:

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

  1. Выберите диапазон ячеек с числами
  2. Перейдите на вкладку «Данные».
  3. В группе «Инструменты для работы с данными» нажмите «Текст в столбцы».
  4. В мастере «Преобразовать текст в столбцы» внесите следующие изменения:
    1. Шаг 1 из 3. Выберите «С разделителями» и нажмите «Далее».
    2. Шаг 2 из 3. Снимите выделение со всех разделителей и нажмите Далее.
    3. Шаг 3 из 3. Выберите целевую ячейку (в данном случае B2) и нажмите «Готово».

    Вышеописанные действия должны удалить все ведущие нули и дать вам только числа. Если вы все еще видите ведущие нули, измените форматирование ячеек на Общее (это можно сделать на вкладке Главная).

    Как удалить ведущие нули из текста

    Все описанные выше методы работают хорошо, но они ограничены ячейками с цифровыми значениями.

    Но что если у вас есть буквенно-цифровые или текстовые значения, которые также содержат ведущие нули?

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

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

    Ниже приведена формула:

    Я объясню, как эта формула работает

    Часть формулы REPLACE заменяет ноль пробелом. Таким образом, для значения 001AN76 формула замещения дает 1AN76.

    Затем этот результат обрабатывается формулой LEFT для извлечения самого левого символа, который в данном случае будет равен 1.

    Затем формула SEARCH ищет самый левый символ, заданный формулой LEFT, и возвращает его позицию. В нашем примере для значения 001AN76 он вернет 3 (это позиция 1 в исходной строке).

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

    После этого результат формулы SURE вычитается из результата формулы LEN (которая помогает определить длину всей текстовой строки). Убрав начальные нули, мы можем определить длину текстового кольца.

    Затем это значение используется с функцией RIGHT для извлечения всей строки (исключая ведущие нули).

    T RIM следует использовать, если существует вероятность, что в ячейках будут начальные или конечные пробелы.

    Новая формула с добавлением функции TRIM имеет следующий вид:

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

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