Как рассчитать пробелы в Excel

Обновлено: 21.11.2024

Столбец A продолжается день за днем, и счетчик номеров счетов сбрасывается до 500 каждый день. Мне нужно знать, сколько пробелов в счетах возникает каждый день. Не каких цифр не хватает, а сколько. Например, на 01.01.2018 отсутствуют примерно 3 счета-фактуры и 2 счета-фактуры на 02.01.2018. Спасибо!

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

Сообщить о нарушении

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

Ответы (11) 

* Попробуйте уменьшить номер страницы.

* Пожалуйста, вводите только цифры.

* Попробуйте уменьшить номер страницы.

* Пожалуйста, вводите только цифры.

Введите дату, например 01.01.2018, например, E2.

В следующей ячейке (F2) введите следующую формулу массива, подтвердив ее нажатием Ctrl+Shift+Enter:

Сообщить о нарушении

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

Спасибо за ответ. Я попытался использовать вашу формулу, но боюсь, она работает неправильно.

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

Например, на 01.01.2018 у меня есть 90 строк данных. Счета начинаются с 500 и заканчиваются на 661. Я вручную насчитал 75 пробелов в счетах за день, но ваша формула насчитала 72.

У меня есть несколько случаев, когда один и тот же счет повторяется: счета 507 и 611 повторяются дважды. Кроме того, в некоторых случаях отсутствуют 6 или 7 счетов подряд. Я не уверен, что эти конкретные ситуации нарушают вашу формулу, потому что я не знаком с тем, что именно делает ваша формула.

Сообщить о нарушении

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

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

Дата:	Счет	Дата
01/01 /2018	500	01/01/2018	3
01/01/2018< /td>	501	02.01.2018	2
01.01.2018	502
01/01/2018	504
01/01/2018	507
01/01/2018	508
01 /01/2018	509
01/01/2018	510
02.01.2018	500
02.01.2018	501
02.01.2018	503
02.01.2018	504
02.01.2018	506

В E2: =ЕСЛИОШИБКА(ИНДЕКС(ЕСЛИ($A$2:$A$1000<>"",$A$2:$A$1000,"")),ПОИСКПОЗ(0,ЕСЛИ($A$2:$A$1000) <>"",СЧЁТЕСЛИ($E$1:E1,$A$2:$A$1000)),0)),"")

Для ввода с помощью CTRL+SHIFT+ENTER в качестве формулы массива.

В F2 модифицированная версия формулы массива Ганса:

Если этот ответ отвечает на ваш вопрос, отметьте его как ответ.

Сообщить о нарушении

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

Спасибо за попытку помочь мне сделать это правильно. Я думаю, что я что-то упускаю. Формулы, которые вы привели выше, все еще не работают. Ниже приведены все мои данные за 01.01.2018 с ручной проверкой отсутствующих счетов в столбце C. Я посчитал это дважды, так что либо это правильно. или мне следует вернуться в первый класс.

В E2: я вставил вашу формулу выше, выделил всю формулу и нажал CTRL+SHIFT+ENTER. Затем он показал 01.01.2018.

Нажмите F2: я вставил модифицированную формулу Ганса, выделил всю формулу и нажал CTRL+SHIFT+ENTER. Затем он показал 72. Он показал 72 до и после того, как я нажал CTRL+****+ENTER.

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

Сообщить о нарушении

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

Откройте электронную таблицу в Microsoft Excel и просмотрите список чисел. Вам нужно определить диапазон чисел, которые вы хотите рассмотреть. Этот диапазон может быть основан на наименьшем и наибольшем значениях в списке номеров или на каком-либо известном диапазоне.Например, если последний номер чека, который вы указали в балансе в прошлом году, был 8950, а последний номер чека, который вы указали в этом году, был 11859, ваш диапазон будет от 8951 до 11859.

Нажмите первую ячейку в пустом столбце, удерживайте клавишу "Shift" и щелкните последнюю ячейку данных в этом столбце. Это выбирает все ячейки между этими двумя точками. Вам нужно выбрать как минимум столько ячеек, сколько ожидается количество пропущенных значений. Самый простой способ гарантировать, что вы выберете достаточное количество ячеек, — это выбрать количество ячеек, равное разнице диапазонов. В примере вычитание 8951 из 11859 дает диапазон из 2908 чисел. Выбирая ячейки от "1" до "2908", вы гарантируете достаточно большую область вывода, даже если числа не найдены.

Введите следующий код:

Столбец "B" относится к списку номеров, а номера строк относятся к диапазону данных, поэтому в этом примере в столбце "B" выполняется поиск чисел от 8951 до 11859, а недостающие числа будут выводиться в пустой столбец.

Нажмите «Ctrl-Shift-Enter», чтобы ввести введенную формулу в виде массива. Если вы просто нажмете «Ввод», формула не будет работать.

Итог: изучите 5 различных способов поиска и удаления пробелов в Excel. Пробелы могут привести к ошибкам в формулах и проблемам с подготовкой и анализом данных.

Уровень квалификации: средний

Посмотреть обучающее видео

Загрузить файл Excel

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

Удалить пробелы BEFORE.xlsm

Удалить пробелы ПОСЛЕ.xlsm

Космическая охота

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

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

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

1. Найти и заменить

Первый способ поиска пробелов – использование функции "Найти и заменить".

После выделения ячеек, в которых вы хотите выполнить поиск, перейдите на вкладку "Главная". Затем откройте раскрывающееся меню «Найти и выбрать». Выберите вариант «Заменить». Сочетание клавиш для этого — Ctrl + H .

Появится окно «Найти и заменить». На вкладке «Заменить» поместите одно пустое место в поле «Найти». Убедитесь, что в поле «Заменить на» ничего нет. Нажатие «Заменить все» (сочетание клавиш: Alt + A) удалит все экземпляры пробела в выбранном наборе данных.

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

Этот сценарий подводит нас ко второму методу поиска пространства.

2. Функция ТРИМ

Функция TRIM удаляет все пробелы в текстовой строке, кроме одиночных пробелов между словами.

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

В пустом столбце начните с ввода знака равенства ( = ) и слова TRIM, а затем перейдите к функции TRIM.

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

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

Как видите, интервал между словами остается на месте, но все пробелы до и после текста удаляются.

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

3. Power Query

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

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

Для удаления пробелов в Power Query есть функция обрезки, которая находится в контекстном меню. Выделив столбец, который вы хотите исправить, просто щелкните правой кнопкой мыши и выберите "Преобразовать" и "Обрезать".

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

Если вы новичок в Power Query или хотите освежить свои знания, я рекомендую посетить мой бесплатный веб-семинар The Modern Excel Blueprint.

4. Макросы и VBA

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

Для начала откройте редактор VB. Перейдите на вкладку «Разработчик» и нажмите кнопку Visual Basic. Сочетание клавиш — Alt + F11 .

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

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

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

Таким образом, я могу использовать этот макрос в любой книге.

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

Также важно отметить, что функция VBA Trim НЕ обрезает лишние пробелы между словами. Функция TRIM в Excel удалит дополнительные пробелы между словами. Поэтому, если между именем и фамилией есть два пробела, TRIM удалит дополнительный пробел. Trim VBA этого не сделает. Он удаляет только пробелы в начале или конце строки. Спасибо Марвину (члену сообщества Excel Campus) за то, что обратил на это наше внимание!

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

5. Обрезка других символов

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

Чтобы определить, какой персонаж может испортить работу, вы можете использовать функцию КОД. Скопировав и вставив невидимый символ в пустую ячейку, а затем определив текст этой ячейки в функции КОД («текст» — единственный аргумент, который необходимо указать для функции КОД), вы вернете кодовый номер для этого символа. . Быстрый поиск в Google по этому кодовому номеру покажет, с каким типом символа вы имеете дело.

В этом случае неразрывный пробел имеет код 160.

Один из способов удалить этот символ из текста – воспользоваться функцией "Найти и заменить". Вы можете просто скопировать символ и вставить его в поле «Найти».

Вы также можете использовать функцию REPLACE, чтобы сделать то же самое.

Еще один вариант, поскольку мы знаем, что хотим удалить последний символ в тексте, заключается в использовании функций ВЛЕВО и ДЛСТР для создания настраиваемой функции обрезки.

LEFT возвращает крайние левые символы в ячейке до заданного количества. LEN (сокращение от Length) возвращает количество символов в ячейке.

Поэтому объединение этих двух функций и вычитание 1 из LEN для удаления этого нежелательного символа оставит только текст без дополнительного символа.

Заключение

Надеюсь, это было полезно для вас. Я также объясняю, как пробелы могут вызывать ошибки в формулах, в моем видео о введении в функцию ВПР.

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

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

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

Устранение пробелов в данных в диаграммах Excel

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

Чтобы получить доступ к этой опции:

Выберите диаграмму.
Перейти к дизайну > выбрать данные
В диалоговом окне "Выбор источника данных" выберите параметр "Скрытые и пустые ячейки" (в левом нижнем углу)
Выберите подходящий вариант.

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

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

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

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

Читайте также: