Заполнить пустые ячейки в excel предыдущим значением
Как в Excel заполнить пустые ячейки нулями или значениями из ячеек выше (ниже)
Смотрите такжеMichael_S- выделяете весь: попробовала,вот что пишетЯчейка целиком выбрав параметри в появившемсяКак известно, для полноценной выделения виснет наглухо
нажимаем таблице часто оставляютReplace эти формулы значениями. снимке экрана выше,ОКGo To 2010 и 2013.Из этой статьи Вы: Risovod, AlexM, у массивLittleв параметрах, нажмитеЗначения (Values) окне выбираем работы с данными приходится перегружатьCTRLENTER незаполненные ячейки. Наличие(Заменить). В противном случае, активна ячейка.(Переход).Итак, моим ответом будет узнаете способ, как ТС- выгрука из- нажимаете "Ctrl+G"
: не найдено ниЗаменить все. Так будет совсемВыделить
Как выделить пустые ячейки на листе Excel
– заполнять! Давайте выделить разом все1С- в открывшемся одной ячейки,удовлетворяющей даннымТеперь впишите в хорошо.пустые ячейки итогов и т.д.) нашел)В итоге получим результат. таблице затрудняет применениеFind what
СОВЕТ: фильтров, сортировку и(Найти) пустым и или при вводеВведите знак равенства (=). ячейки и всё(Выделить). сможем это сделать. листе Excel и в "пустых" ячейках- ставите точкуLittle0: как заменить в: т.е. таблица без: Бред.В статье Советы
- построение Сводных таблиц. введите нужное значение новых данных.Укажите ячейку, находящуюся выше готово к следующемуЗамечание:Как выделить пустые ячейки заполнить их значениями пробел, и, зачастую,
- напротив "Пустые ячейки": а если сразузаменить, копируйте эту ячейку, таблице все пустыеНе снимая выделения вводим разрывов (пустых строкИщите причину. по построению таблиц Заполним пустые ячейки в полеЧто если Вам нужно
Формула для заполнения пустых ячеек значениями из ячеек выше (ниже)
или ниже, нажав шагу.Если Вы вдруг на листе Excel из ячеек выше не просто пробел, и нажимаете "ОК"
на ноль,то заполняються выделяйте предназначенную для ячейки на ноль,таблица в первую ячейку и ячеек -korbon рассказано более подробно значениями из вышерасположенныхReplace with заполнить все пустые стрелку вверх илиПосле того как Вы забыли сочетание клавиш,
Home из ячеек вышеЗаполнять или не заполнять?. Поэтому самый верный
- теперь набираетеRANF5 - Выделить - выделить-заменить почему то ячейке (т.е. создаем мы имеем как это бред или таблице. частично заполненным столбцомReplace All другими числовыми илиФормула ( можно заполнить значениями(Главная) и в
Заполняем пустые ячейки нулями или другим заданным значением
(ниже)– этот вопрос вариант нужное число/текст (на: D3:G5 - выполнено пустые ячейки - не помогает,нули нужно ссылку на предыдущую
Способ 1
часто возникает вAlexM которое нужно заменить
Способ 2
- замен 12 ОК
- проставить в желтые ячейку, другими словами): пропущенными пустыми ячейками меня искать причину: Добрый вечер(или ночь).
- Какой бы способ Вы показаны два способа) показывает, что в
- снизу или вставитьEditing или другим заданным отношении пустых ячеек: Понятно. Первый, предложенный пустые ячейки), вLittle
- и вставляйте скопированный ячейки,выкладываю пример выгрузки,здесьИ, наконец, чтобы ввести
- например после надо время(а какесть диапазон данных(большой)Заполним столбец соответствующими названиями ни выбрали, обработка
решения этой задачи. ячейке определённое значение.(Редактирование) из выпадающего значением в таблицах Excel. вариант не проходит. данном случае "0": вот что получаеться,может ранее ноль. конечно можно поставить эту формулу во
копирования результатов сводных для новичка,
с числами и
отделов.
всей таблицы Excel
Заполнение пустых ячеек в MS EXCEL
Выделите все пустые ячейкиC4Если Вы собираетесь заполнить менюПрежде чем заполнять пустые С одной стороны, Буду иметь ввиду- и нажимаете у меня какиеMichael_S в первую свободную
все выделенные (пустые) таблиц или выгрузокмного времени) где много пустых ячеек,
выделяем диапазон ячеек в займёт не больше
- Нажмитепоявится значение из пропуски значениями изFind & Select ячейки в Excel, таблица выглядит аккуратнее
- особенности 1С "Ctrl+Enter" то параметры не: А почему сразу
- ячейку ноль и ячейки нажимаем в Excel из и как искать. в пустые ячейки первом столбце, который
- минуты!F2 ячейки
- ближайшей не пустой(Найти и выделить) их нужно выделить. и более читабельной,А второй вариант
выставлены?
0 растянуть по всейCtrl + Enter внешних программ. ТакимМожет есть какая-то надо вставить нули
Заполнить пустые ячейки нулями
надо заполнить (вТеперь Вы знаете приёмы,
, чтобы ввести значениеC3 ячейки сверху или выберите команду Если у Вас
когда Вы не проходит. ячейки заполнились необходимыми
Littleне поставить? Не
таблице,но как быть,есливместо обычного Enter. образом, возникает необходимость
альтернатива.пробовал через найти/ нашем примере, это как заполнить пустые в активную ячейку.. снизу, то потребуется
Go To Special большая таблица с загромождаете её повторяющимисяВыделить диапазон D3:G5 символами.
: не могу прикрепить проверял, но должно
таблица огроменная и И все! Просто
заполнить пустые ячейки
Serge 007 выделить группы/пустые ячейки,
A2:A14 ячейки различными значениямиВведите нужное число илиНажмите ввести в одну(Выделить группу ячеек).
дюжинами пустых ячеек, значениями. С другой
В строке формулAlexM
файл,вообщем делаю все сработать. часть ячеек заполнена и красиво. таблицы значениями из
: Макросом можно. Но
Excel почему-то зависает). в Excel 2013. текст.
Ctrl+Enter из пустых ячеек
На экране появится разбросанных по всей стороны, пустые ячейки вставить 0 (вместо
: Задача решена, но
Заполнение пустых ячеек
как надо,нажимаю заменить,пишет,выполненоSerge_007 числами?В качестве завершающего мазка верхних ячеек, то нет никакой гарантии,Можно как-то пов меню Главная/ Редактирование/ Уверен, для ВасНажмите, чтобы скопировать формулу очень простую формулу. то же диалоговое таблице, то Вы в Excel могут пробела) Нажать Ctrl+Enter есть еще одно 6 замен,и заменяет: Мы не ищемSerge_007 я советовал бы бишь. что виснуть не
другому это сделать. | Найти и выделить |
не составит трудаCtrl+Enter
во все выделенные Затем просто скопируйте окно. потратите вечность, если привести к проблемам
Еще раз проверил простое решение. все пустые ячейки лёгких путей: Выделите ячейку D4, заменить все созданные из будет. P.S. я новичок выбираем Выделение группы
сделать это как. ячейки. её во всеКоманда будете делать это во время сортировки,
на файле ТСВыделить 0 в ,кроме желтых,что не:D в строке формул формулы на значения, сделать Не по теме:
в Excel если ячеек; при помощи простойЗа несколько секунд ВыОтлично! Теперь в каждой пустые ячейки. КакGo To Special вручную. Вот быстрый фильтрации данных илиPS. Почему при ячейке С4 - так?Да, конечно так скопируйте находящийся в ибо при сортировкеЭто можно реализовать, буквально,Вы в диспетчер задач можно, подробней.выбираем пункт "Пустые ячейки"
как заменить пустые ячейки на ноль
формулы, так и заполнили все пустые выделенной ячейке содержится это сделать –(Выделить группу ячеек) способ, как выделить при создании сводной открытии файла ТС Копировать.Little проще ней пробел, нажмите или добавлении/удалении строк в несколько движений. заглядывали? Какие процессы?Serge 007 и нажимаем ОК при помощи инструмента
ячейки нужным значением. ссылка на ячейку, читайте далее. позволяет выбрать ячейки пустые ячейки: таблицы. В таком нет ярлыков листов,Выделить диапазон D3:G5: Ребят,Всем спасибо,конечно,я пустотуПерестраховываюсь на тотCtrl+H корректность формул можетВыделяем диапазон ячеек в Что жрёт оперативку?: Выделите диапазон (а (все пустые ячейкиFind & Replace
Выделите диапазон, содержащий пустые расположенную над ней.Выделите все пустые ячейки. определённого типа, например,Выделите столбцы или строки, случае Вам придётся даже после постановки - Вставить Специальная копировала,а не пробел,ВСЕ случай, если пробелы, вставьте его в быть нарушена. Выделите
первом столбце, который Сколько занимает Excel? не весь лист) в диапазоне(Найти и заменить). ячейки.
Замечание:Нажмите ячейки, содержащие формулы, в которых требуется
заполнить все пустые галочки "Ярлыки листов"
вставка операция сложить. ПОЛУЧИЛОСЬ. Еще раз спасибо надо убрать со поле все ячейки в надо заполнить (вkorbon - F5 -
A2:A14Урок подготовлен для Вас
НажмитеНе забывайте, чтоF2 примечания, константы, пустые
заполнить пустоты. ячейки. Существуют разные в Параметрах? Ок.Risovod
всего листа, аНайти: первом столбце, скопируйте
Ctrl+H все ячейки, которыеили просто поместите ячейки и такНажмите способы для решенияSerge_007Или еще проще.
: нули поставить только, поле и тут же
или покажу Вам несколько выгрузка из 1С В строке формул
, таблицы (там гдеоставьте пустым, так
помощьюF5 уже успел воспользоватьсяkorbon
=A2;
Автор: Антон АндроновFind & Replace Вы хотите сохранить к вводу формулы
BlanksF5 быстрых способов заполнитьМышью подвиньте за вставить 0 (вместоЕщё один из значения) же поставьте галкуСпециальной вставки (Paste Special)и затем кнопку Вашим советом: Пробовал (у меня
вместоВместо того, чтобы вводить(Найти и заменить). порядок в таблице, в активную ячейку.
(Пустые ячейки) и, чтобы отобразить диалоговое
пустые ячейки различными левый край полосу пробела) Нажать Ctrl+Enter вариантов:
Little на
в контекстом меню,Выделить (Special)Спасибо ещё раз. excel 2010) послеENTER повторяющиеся данные, в
Перейдите на вкладку то лучше заменить Как видно на
нажмите окно значениями в Excel
Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек - по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками - например после копирования результатов сводных таблиц или выгрузок в Excel из внешних программ. Таким образом, возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, то бишь.
из | сделать |
В общем случае, может возникнуть необходимость делать такое заполнение не только вниз, но и вверх, вправо и т.д. Давайте рассмотрим несколько способов реализовать такое.
Способ 1. Без макросов
Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12).
Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks) :
Не снимая выделения, вводим в первую ячейку знак "равно" и щелкаем по предыдущей ячейке или жмём стрелку вверх (т.е. создаем ссылку на предыдущую ячейку, другими словами):
И, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter . И все! Просто и красиво.
В качестве завершающего мазка я советовал бы заменить все созданные формулы на значения, ибо при сортировке или добавлении/удалении строк корректность формул может быть нарушена. Выделите все ячейки в первом столбце, скопируйте и тут же вставьте обратно с помощью Специальной вставки (Paste Special) в контекстом меню, выбрав параметр Значения (Values) . Так будет совсем хорошо.
Способ 2. Заполнение пустых ячеек макросом
Если подобную операцию вам приходится делать часто, то имеем смысл сделать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert - Module и копируем или вводим туда вот такой короткий код:
Как легко можно сообразить, этот макрос проходит в цикле по всем выделенным ячейкам и, если они не пустые, заполняет их значениями из предыдущей ячейки.
Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы этот макрос был доступен при работе в любом вашем файле Excel.
Способ 3. Power Query
Power Query - это очень мощная бесплатная надстройка для Excel от Microsoft, которая может делать с данными почти всё, что угодно - в том числе, легко может решить и нашу задачу по заполнению пустых ячеек в таблице. У этого способа два основных преимущества:
- Если данных много, то ручной способ с формулами или макросы могут заметно тормозить. Power Query сделает всё гораздо шустрее.
- При изменении исходных данных достаточно будет просто обновить запрос Power Query. В случае использования первых двух способов - всё делать заново.
Для загрузки нашего диапазона с данными в Power Query ему нужно либо дать имя (через вкладку Формулы - Диспетчер имен), либо превратить в "умную" таблицу командой Главная - Форматировать как таблицу (Home - Format as Table ) или сочетанием клавиш Ctrl + T :
После этого на вкладке Данные (Data) нажмем на кнопку Из таблицы / диапазона (From Table/Range) . Если у вас Excel 2010-2013 и Power Query установлена как отдельная надстройка, то вкладка будет называться, соответственно, Power Query.
В открывшемся редакторе запросов выделим столбец (или несколько столбцов, удерживая Ctrl ) и на вкладке Преобразование выберем команду Заполнить - Заполнить вниз (Transform - Fill - Fill Down) :
Вот и всё :) Осталось готовую таблицу выгрузить обратно на лист Excel командой Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close&Load - Close&Load to. )
В дальнейшем, при изменении исходной таблицы, можно просто обновлять запрос правой кнопкой мыши или на вкладке Данные - Обновить всё (Data - Refresh All) .
На практике часто возникает необходимость быстро найти значение последней (крайней) непустой ячейки в строке или столбце таблицы. Предположим, для примера, что у нас есть вот такая таблица с данными продаж по нескольким филиалам:
Задача: найти значение продаж в последнем месяце по каждому филиалу, т.е. для Москвы это будет 78, для Питера - 41 и т.д.
Если бы в нашей таблице не было пустых ячеек, то путь к решению был бы очевиден - можно было бы посчитать количество заполненных ячеек в каждой строке и брать потом ячейку с этим номером. Но филиалы работают неравномерно: Москва простаивала в марте и августе, филиал в Тюмени открылся только с апреля и т.д., поэтому такой способ не подойдет.
Универсальным решением будет использование функции ПРОСМОТР (LOOKUP) :
У этой функции хитрая логика:
- Она по очереди (слева-направо) перебирает непустые ячейки в диапазоне (B2:M2) и сравнивает каждую из них с искомым значением (9999999).
- Если значение очередной проверяемой ячейки совпало с искомым, то функция останавливает просмотр и выводит содержимое ячейки.
- Если точного совпадения нет и очередное значение меньше искомого, то функция переходит к следующей ячейке в строке.
Легко сообразить, что если в качестве искомого значения задать достаточно большое число, то функция пройдет по всей строке и, в итоге, выдаст содержимое последней проверенной ячейки. Для компактности, можно указать искомое число в экспоненциальном формате, например 1E+11 (1*10 11 или сто миллиардов).
Если в таблице не числа, а текст, то идея остается той же, но "очень большое число" нужно заменить на "очень большой текст":
Применительно к тексту, понятие "большой" означает код символа. В любом шрифте символы идут в следующем порядке возрастания кодов:
- латиница прописные (A-Z)
- латиница строчные (a-z)
- кириллица прописные (А-Я)
- кириллица строчные (а-я)
Поэтому строчная "я" оказывается буквой с наибольшим кодом и слово из нескольких подряд "яяяяя" будет, условно, "очень большим словом" - заведомо "большим", чем любое текстовое значение из нашей таблицы.
Вот так. Не совсем очевидное, но красивое и компактное решение. Для поиска последней непустой ячейки в столбцах работает тоже "на ура".
Заполнять пустоты или нет? Этот вопрос часто касается пустых ячеек в таблицах Excel. С одной стороны, ваша таблица выглядит аккуратнее и читабельнее, если вы не загромождаете ее повторяющимися значениями. С другой стороны, пустые ячейки могут вызвать проблемы при сортировке, фильтрации данных или создании сводной таблицы. В этом случае вам желательно заполнить все поля.
Таким образом, мой ответ - «Заполнить». А теперь посмотрим, как это сделать.
Есть разные способы решения этой проблемы. Я покажу вам несколько быстрых и один ОЧЕНЬ быстрый способ заполнить пустые ячейки значениями.
Как выделить пустые ячейки на листах Excel.
Перед тем, как заполнить пустоты в таблице Excel, сначала нужно их выделить. Если у вас большая таблица с десятками незаполненных областей, разбросанными по ней, то потребуется много времени, чтобы сделать это вручную. Вот быстрый приём для выбора пустых ячеек.
- Выберите столбцы или строки, в которых вы хотите заполнить пустоты.
- Нажмите Ctrl + G или же F5 для отображения диалогового окна “Перейти”.
- Щелкните по кнопке «Выделить».
- Выберите «Пустые ячейки».
Совет. Если вы забыли сочетания клавиш, перейдите в группу «Найти и выделить» на вкладке Главная и выберите команду «Выделить группу ячеек» в раскрывающемся меню. Появится то же самое диалоговое окно.
- Далее выберите, что будем выделять. Например, формулы, комментарии, константы, пробелы и т. д.
- Установите переключатель «Пустые ячейки» и нажмите «ОК».
Теперь выделены только пустые ячейки из выбранного диапазона, и вы готовы к следующему шагу.
Формула Excel для заполнения пустых ячеек значениями, стоящими выше / ниже
Выбрав пустые ячейки в таблице, вы можете заполнить их значениями, стоящими сверху или снизу, или же просто вставить какое-то определенное содержимое.
Если вы собираетесь заполнить пробелы значением из ближайшей заполненной ячейки выше или ниже, вам нужно ввести очень простую формулу в одну из пустых ячеек. Затем просто скопируйте ее во все остальные. Вот как это сделать.
- Выделите все незаполненные ячейки, как описано выше.
- Нажмите F2 или просто поместите курсор в строку формул, чтобы начать писать формулу в активной ячейке.
Как видно на скриншоте ниже, активная ячейка – A3, то есть по умолчанию это самая левая верхняя из всех незаполненных.
- Введите знак равенства (=).
- Наведите курсор на ячейку, находящуюся выше или ниже, с помощью клавиши со стрелкой вверх или вниз или просто кликните по ней мышкой.
Формула (=A2) показывает, что A3 получит значение из A2, и будет заполнена предыдущим значением.
- Нажмите Ctrl + Enter , чтобы автоматически вставить формулу сразу во все выделенные позиции.
Ну вот! Теперь каждая выделенная ячейка ссылается на ячейку, находящуюся над ней.
Совет. Вы должны помнить, что все ячейки, которые раньше были пустыми, теперь содержат формулы. И если вы хотите, чтобы ваша таблица была в порядке, лучше эти формулы заменить на значения. В противном случае вы получите беспорядок при сортировке или же добавлении строк или столбцов в таблицу.
Поэтому рекомендую не останавливаться и сразу после ввода формул заменить их на значения. Выполните следующие простые шаги:
- У вас выделены все ячейки с формулами, которые вы только что ввели и хотите преобразовать.
- Нажмите Ctrl + C или же Ctrl + Ins , чтобы копировать формулы и их результаты в буфер обмена.
- Нажмите Shift + F10 а потом V , чтобы вставить обратно в выделенные позиции только значения.
Shift + F10 + V — это самый быстрый способ использовать диалог Excel «Специальная вставка».
Заполните пустые ячейки нулями или другим определенным значением
Что, если вам нужно заполнить все пробелы в таблице нулями, любым другим числом или просто одинаковыми данными? Вот два способа решить эту проблему.
Способ 1.
- Выделите пустые ячейки, как мы уже делали.
- Нажмите F2 для активации режима редактирования в строке формул. Или просто кликните туда мышкой.
- Введите желаемое число или текст.
- Нажмите Ctrl + Enter .
Несколько секунд - и все пустые ячейки одинаково заполнены введенным вами словом, символом либо нулями при необходимости.
Способ 2.
- Выделите диапазон с пустыми ячейками.
- Нажмите Ctrl + H для отображения диалогового окна «Найти и заменить». Или используйте меню.
- В этом окне перейдите на вкладку «Заменить».
- Оставьте поле «Найти» пустым и введите необходимое значение в текстовое поле «Заменить на».
- Щелкните " Заменить все".
Пустые ячейки будут заполнены значением, которое вы указали.
Заполнение пустых ячеек при помощи макроса VBA.
Если подобную операцию вам приходится делать часто, то имеет смысл создать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert – Module. Далее копируем или вводим туда вот такой короткий код:
Sub Fill_Blanks()
For Each cell In Selection
If IsEmpty(cell) Then cell.Value = cell.Offset(-1, 0).Value
Next cell
End Sub
Как легко можно сообразить, этот макрос проходит последовательно по всем выделенным ячейкам и, если они не пустые, то заполняет их значениями из предыдущей ячейки сверху.
Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы он был доступен при работе в любом вашем файле Excel.
Какой бы способ вы ни выбрали, заполнение таблицы Excel займет у вас буквально минуту.
Как быстро заполнить пустые ячейки без использования формул.
Вот наши данные о продажах в разрезе менеджеров и регионов. Некоторые из продавцов работали в нескольких регионах, сведения об их продажах записаны друг под другом. Также объединены ячейки месяцев. Таблица выглядит достаточно читаемо. Однако, если нужно будет отфильтровать или просуммировать данные по менеджерам, или же найти сумму продаж по региону за определенный месяц, то сделать это будет весьма затруднительно. Этому будут мешать пустые и объединенные ячейки.
Поэтому постараемся привести таблицу к стандартному виду, заполнив все пустоты и разъединив ранее объединенные области.
Перейдите на ленте на вкладку AblebitsTools.
- Установите курсор в любую ячейку таблицы, в которой вам нужно заполнить пустые ячейки.
- Щелкните значок «Заполнить пустые ячейки (FillBlankCells)».
На экране появится окно надстройки, в котором перечислены все столбцы и указаны параметры заполнения.
- Снимите отметку со столбцов, в которых нет пустых ячеек.
- Выберите действие из раскрывающегося списка в правом нижнем углу окна.
Если вы хотите заполнить пустые поля значением из ячейки, находящейся выше, выберите параметр «Заполнить ячейки вниз (Fill cells downwards)». Если вы хотите скопировать содержимое из ячейки ниже, выберите в этом же выпадающем списке «Заполнить ячейки вверх (Fill cells upwards)». В нашем случае выбираем заполнение вниз.
В отличие от рассмотренных выше способов, здесь пустые ячейки заполнены не одним и тем же значением, а разными, которые гораздо больше подходят для ваших данных. Правильное заполнение этой даже такой небольшой таблицы потребовало бы от вас достаточно существенных затрат времени. А надстройка позволяет это сделать буквально в пару кликов.
Помимо заполнения пустых ячеек, этот инструмент также разделил объединенные ячейки. В таком виде таблица вполне пригодна для фильтрации данных, различных подсчетов, формирования сводной таблицы на ее основе.
Проверьте это! Загрузите полнофункциональную пробную версию надстройки Fill Blank Cells и посмотрите, как она может сэкономить вам много времени и сил.
Теперь вы знаете приемы замены пустых ячеек в таблице разными значениями. Я уверен, что вам не составит труда сделать это при помощи любого из рассмотренных способов.
Проверка данных в Excel: как сделать, использовать и убрать - Мы рассмотрим, как выполнять проверку данных в Excel: создавать правила проверки для чисел, дат или текстовых значений, создавать списки проверки данных, копировать проверку данных в другие ячейки, находить недопустимые записи, исправлять и удалять проверку данных.…
Быстрое удаление пустых столбцов в Excel - В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых столбцов в Excel не может…
Как полностью или частично зафиксировать ячейку в формуле - При написании формулы Excel знак $ в ссылке на ячейку сбивает с толку многих пользователей. Но объяснение очень простое: это всего лишь способ ее зафиксировать. Знак доллара в данном случае служит только одной цели - он указывает,…
Чем отличается абсолютная, относительная и смешанная адресация - Важность ссылки на ячейки Excel трудно переоценить. Ссылка включает в себя адрес, из которого вы хотите получить информацию. При этом используются два основных вида адресации – абсолютная и относительная. Они могут применяться в разных комбинациях…
Относительные и абсолютные ссылки – как создать и изменить - В руководстве объясняется, что такое адрес ячейки, как правильно записывать абсолютные и относительные ссылки в Excel, как ссылаться на ячейку на другом листе и многое другое. Ссылка на ячейки Excel, как бы просто она ни…
6 способов быстро транспонировать таблицу - В этой статье показано, как столбец можно превратить в строку в Excel с помощью функции ТРАНСП, специальной вставки, кода VBA или же специального инструмента. Иначе говоря, мы научимся транспонировать таблицу. В этой статье вы найдете…
4 способа быстро убрать перенос строки в ячейках Excel - В этом совете вы найдете 4 совета для удаления символа переноса строки из ячеек Excel. Вы также узнаете, как заменять разрывы строк другими символами. Все решения работают с Excel 2019, 2016, 2013 и более ранними версиями. Перенос…
Как безопасно удалить пустые ячейки в Excel и как не нужно никогда это делать - В этом руководстве вы узнаете, как правильно и безопасно удалять пустые ячейки в таблицах Excel, чтобы они выглядели четкими и профессиональными. Пустые ячейки – это неплохо, если вы намеренно оставляете их в нужных местах по…
Как посчитать количество пустых и непустых ячеек в Excel - Если ваша задача - заставить Excel подсчитывать пустые ячейки на листе, прочтите эту статью, чтобы найти 3 способа для этого. Узнайте, как искать и выбирать среди них нужные с помощью стандартных инструментов поиска, или же просто…
6 способов — как безопасно удалить лишние пустые строки в Excel - Это руководство научит вас нескольким простым приемам безопасного удаления нескольких пустых строк в Excel без потери информации. Пустые строки в таблице — это проблема, с которой мы все время от времени сталкиваемся, особенно при объединении…
Пустые ячейки – это неплохо, если вы намеренно оставляете их в нужных местах по эстетическим соображениям. Но в неправильных местах, они, безусловно, нежелательны. К счастью, есть относительно простой способ удалить пустоты в таблицах Excel, и сейчас вы узнаете все подробности этого метода.
Удалить пустые ячейки в Excel очень просто. Однако этот подход применим не во всех ситуациях. О возможных неприятных неожиданностях мы расскажем в этой статье. Чтобы обезопасить себя, обязательно сделайте резервную копию своего рабочего листа и прочтите эти предостережения, прежде чем действовать.
Удаляем пустые ячейки стандартными инструментами Excel.
Ну а теперь, когда вы предупреждены о возможных неприятных последствиях:
- Выберите диапазон, в котором вы хотите убрать пустоты. Чтобы быстро выбрать всю таблицу с данными, щелкните верхнюю левую позицию и затем нажмите Ctrl + Shift + End . Это сразу расширит выделение до последней использованной клетки.
- Нажмите F5 и щелкните Выделить… . Или перейдите на вкладку «Главная» и нажмите Найти…>Выделить группу ячеек.
- В диалоговом окне выберите «Пустые ячейки» и нажмите ОК. Это выделит все незаполненные позиции в вашем диапазоне.
- Щелкните правой кнопкой мыши на выделенном и выберите Удалить… из контекстного меню:
- В зависимости от макета ваших данных выберите сдвиг влево или вверх и нажмите кнопку «ОК». В этом примере мы используем второй вариант.
Вот и все. Вы успешно удалили пустоты в своей таблице:
Примечание. Если что-то пошло не так, не паникуйте и сразу нажмите Ctrl + Z , чтобы вернуть свои данные в исходное состояние.
Думаю, вы понимаете, что такой метод подходит для перечней данных, в которых нет формул. Ведь ссылки в формулах в результате таких действий скорее всего поломаются, и вы получите искаженные данные либо вовсе ошибки.
Когда не следует удалять пустые ячейки.
Техника, описанная выше, отлично подходит для одного столбца или строки. Он также может успешно удалять пустые клетки в диапазоне независимых строк или столбцов, как в приведенном выше примере. Однако это может нанести ущерб данным, которые связаны друг с другом при помощи формул. Чтобы этого не произошло, будьте очень осторожны при удалении пробелов в рабочих листах и помните о следующих предостережениях:
- Удаляйте пустые строки и столбцы вместо отдельных ячеек.
Если ваши данные организованы в виде таблицы, в которой элементы содержат связанную информацию, удаление отдельных пустых ячеек с большой вероятностью приведет к искажению данных. В этом случае следует удалять только незадействованные строки и столбцы целиком. Рекомендуем ознакомится с рекомендациями, как можно и как нельзя удалять пустые строки в Excel.
Невозможно удалить отдельные ячейки в таблице Excel (но не в диапазоне данных). Вам разрешено удалять только целые строки таблицы. Или вы можете сначала преобразовать таблицу в диапазон, а затем заняться удалением.
- Вашими действиями могут быть повреждены формулы и именованные диапазоны.
Формулы Excel могут адаптироваться ко многим изменениям, внесенным в данные, которые они используют. Многим, но не всем. В некоторых случаях формулы, ссылающиеся на удалённые вами ячейки, перестают работать. Поэтому после удаления незаполненных ячеек взгляните внимательно на соответствующие формулы и именованные диапазоны, чтобы убедиться, что они работают нормально.
Как извлечь список данных без пустот.
Если вы опасаетесь, что удаление пустых ячеек в столбце может исказить ваши данные, оставьте исходный столбец как есть и извлеките заполненные ячейки в другое место.
Этот метод удобен, когда вы создаете собственный список или раскрывающийся список проверки данных и хотите убедиться, что в нем нет пустот.
Итак, наш список находится в диапазоне A2:A12. Причем, в A4 записано пустое значение "". Рассмотрим 5 методов, при помощи которых мы попытаемся избавиться от пустых ячеек и сформировать в нужном нам месте новый список без пробелов.
Способ 1.
Введите приведенную ниже формулу массива в C2, нажмите Ctrl + Shift + Enter , чтобы заполнить ее правильно, а затем скопируйте формулу в еще несколько ячеек вниз по столбцу. Количество ячеек, в которые вы копируете формулу, должно быть не меньше, чем количество элементов в вашем исходном списке.
Формулы для извлечения заполненных позиций:
=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$12; НАИМЕНЬШИЙ(ЕСЛИ(НЕ(ЕПУСТО($A$2:$A$12)); СТРОКА($A$1:$A$11);""); СТРОКА(A1)));"")
Обратите внимание, что диапазон в функции СТРОКА($A$1:$A$11) должен всегда начинаться с $A$1. И включать в себя он должен столько же позиций, сколько их в нашей области поиска. Иначе говоря, если в $A$2:$A$12 — 11 позиций, значит столько же их нужно указать и в $A$1:$A$11.
На скриншоте ниже показан результат:
Сразу отметим, что пустые значения "" здесь не исключаются из списка.
Сложную, на первый взгляд, логику формулы легко понять при ближайшем ее рассмотрении.
Итак, наша задача: вернуть первое значение, которое встретится в диапазоне A2:A12. В случае ошибки возвращаем пустоту ("").
Для вдумчивых пользователей Excel, которым интересно разбираться в деталях каждой новой формулы, вот пошаговое описание:
У вас есть функция ИНДЕКС, возвращающая значение из $A$2:$A$12 на основе порядкового номера строки (не реального номера, а относительного номера в этом диапазоне). При этом нам нужно учесть еще 2 вещи:
- Убедиться, что A2 не пустая
- Вернуть второе непустое значение в C3, третье непустое значение в C4 и так далее.
Обе эти задачи выполняются функцией НАИМЕНЬШИЙ:
НАИМЕНЬШИЙ(ЕСЛИ(НЕ(ЕПУСТО($A$2:$A$12)); СТРОКА($A$1:$A$11);""); СТРОКА(A1)))
В нашем случае аргумент массива генерируется динамически следующим образом:
- НЕ(ЕПУСТО($A$2:$A$12)) определяет, какие позиции в целевом диапазоне заполнены, и для них возвращается ИСТИНА, в противном случае - ЛОЖЬ. Результирующий массив ИСТИНА и ЛОЖЬ проходит логическую проверку функции ЕСЛИ.
- ЕСЛИ оценивает каждый элемент массива ИСТИНА / ЛОЖЬ и возвращает соответствующий номер позиции для ИСТИНА, и ничего — для ЛОЖЬ:
В результате мы получаем массив и наша сложная функция преобразуется:
НАИМЕНЬШИЙ(; СТРОКА(A1))
Как видите, аргумент массива содержит только номера непустых ячеек (заметьте, это относительные положения элементов в массиве, т.е. A2 – имеет порядковый номер 1, A3 — это 2 и так далее).
Во втором аргументе мы имеем СТРОКА(A1), что указывает функции НАИМЕНЬШИЙ вернуть самое маленькое число. Из-за использования относительной ссылки номер увеличивается с шагом 1 при копировании формулы вниз. Итак, в C3 формула вернет номер второй по величине минимальный номер заполненной позиции в диапазоне (то есть, 2) и так далее.
Однако на самом деле нам не нужны номера позиций, а нужны их значения. Итак, мы вкладываем функцию НАИМЕНЬШИЙ в аргумент функции ИНДЕКС, заставляя возвращать значение из соответствующей позиции в диапазоне.
В качестве последнего штриха мы заключаем всю конструкцию в функцию ЕСЛИОШИБКА, чтобы заменить ошибки пустотами. Ошибки будут неизбежны, потому что вы не можете заранее знать, сколько непустых ячеек находится в целевом диапазоне, и копируете формулу вниз по столбцу в заведомо большее количество ячеек, чем это необходимо.
Способ 2.
Если нужно избавиться и от пустых ячеек, и от пустых значений, то можно использовать приведённую ниже формулу массива.
=ЕСЛИ(СТРОКА() -СТРОКА($C$2:$C$12)+1>ЧСТРОК($A$2:$A$12) -СЧИТАТЬПУСТОТЫ($A$2:$A$12);"";ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ($A$2:$A$12<>"";СТРОКА($A$2:$A$12);СТРОКА()+ЧСТРОК($A$2:$A$12)+1));СТРОКА() -СТРОКА($C$2:$C$12)+1);СТОЛБЕЦ($A$2:$A$12);4)))
Как и в предыдущем случае, вы вводите выражение в первую позицию вашей области с результатами, а затем копируете вниз по столбцу, насколько это необходимо.
Естественно, не забываем ввод заканчивать комбинацией Ctrl + Shift + Enter .
Недостатком здесь является то, что нужно точно указывать размеры исходного и результирующего диапазонов. Причем, с абсолютными ссылками.
Способ 3.
Это тоже формула массива. Но, в отличие от предыдущих двух, ее нужно вводить сразу в диапазон. Если основываться на наших условных данных, то выделяем при помощи мыши диапазон C2:C12, а затем в строку формул записываем следующее выражение:
Естественно, заканчиваем всё нажатием Ctrl + Shift + Enter .
Еще одной особенностью этого метода является то, что мы сравниваем каждую позицию с эталоном — с совершенно пустой ячейкой. В нашей формуле это D1.
Сравниваем значение D1 с каждым элементом исходного диапазона $A$2:$A$12.
СТРОКА(ДВССЫЛ("1:"&ЧСТРОК($A$2:$A$12))))) - счетчик массива от первого до его последнего элемента.
При помощи функции ИНДЕКС выводим только непустые элементы.
При сравнении ячейки с нулями и нулевой длиной приравниваются к пустым.
НАИМЕНЬШИЙ позволяет выводить значения по порядку — с 1 до конца массива.
Способ 4.
Несомненным достоинством трёх описанных выше формул является то, то не нужно вносить никаких дополнительных изменений в данные, делать какие-то промежуточные расчеты.
Однако, формулы массива с большими объемами данных работают довольно медленно. А на особо больших массивах в несколько тысяч строк Эксель и вовсе может «зависнуть». Поэтому в таких случаях проще и быстрее будет создать на листе дополнительный столбец для промежуточных вычислений. Зато быстродействие вырастет многократно.
Создаем дополнительный столбец А. В нем будем нумеровать непустые ячейки.
Для этого проверяем выполнение 2 условий:
- ячейка в столбце В непустая,
- она не равна 0.
Если условия выполнены, считаем количество чисел, находящихся в этом столбце выше текущей позиции, и увеличиваем результат на 1 (своего рода счётчик). То есть, записываем, какая по счету непустая позиция найдена. Если же клетка в столбце В пустая, то ставим "".
Копируем эту формулу вниз по столбцу. Получаем последовательную нумерацию непустых ячеек.
Переходим ко второму шагу. Функция ВПР (VLOOKUP) последовательно ищет значения начиная с 1 в столбце А и выводит соответствующие им значения из В.
Для получения последовательности чисел используем СТРОКА().
Копируем формулу вниз, получаем список без пустот. Работает такая конструкция быстро, независимо от количества данных. Да и сами формулы просты и вполне доступны для понимания, в отличие от формул массива. Так что можете взять на вооружение и этот способ.
Способ 5.
Предвидя то, что многим может не понравиться торчащий на самом виду вспомогательный столбец А с цифрами, предлагаю еще один метод избавления от пустых ячеек. Здесь мы используем так называемый «левый ВПР». Поэтому вспомогательный столбец можно размещать в любом месте, где он не будет мешать. Чтобы не портить внешний вид вашей таблицы, можно использовать любой другой столбец, находящийся правее (например — Z). Мы же используем B для наглядности результатов.
В позиции B2 записываем вспомогательную формулу
D1 будет служить нам эталоном как абсолютно пустая. Последовательно сравниваем столбец А с ней. При этом игнорируются нули, а также содержимое нулевой длины "".
В С2 записываем:
При помощи ПОИСКПОЗ ищем в столбце B порядковые номера от 1 и далее, и функцией ИНДЕКС выводим соответствующее значение из столбца A. В результате в столбце С мы избавились от пустых ячеек в списке. При этом исходные данные мы не меняли, чем гарантировали себя от возможных ошибок.
Как удалить пустые ячейки после последней позиции с данными.
Пустые ячейки, содержащие форматирование или непечатаемые символы, могут вызвать множество проблем в Excel. Например, вы можете получить гораздо больший размер файла, чем необходимо, или напечатать несколько чистых страниц. Чтобы избежать этих проблем, мы удалим (или очистим) пустые строки и столбцы, содержащие форматирование, пробелы или неизвестные невидимые символы.
Как найти последнюю использованную ячейку на листе
Чтобы перейти к последней ячейке на листе, содержащей данные или форматирование, щелкните в любом месте вашей таблицы и нажмите Ctrl + End .
Если в результате этого действия выбрана последняя ячейка с вашими данными, это означает, что оставшиеся строки и столбцы действительно не используются и никаких дополнительных манипуляций не требуется.
Но если вы оказались в визуально пустой клетке, знайте, что Excel не считает её таковой. Это может быть простой пробел, созданный случайным нажатием клавиши, пользовательский формат чисел, установленный для неё, или непечатаемый символ, импортированный из внешней базы данных. Какой бы ни была причина, она не пуста.
Как удалить ячейки после последней позиции с данными.
Чтобы очистить все содержимое и форматирование после последней заполненной ячейки, выполните следующие действия:
- Щелкните заголовок первого незаполненного столбца справа от вашей таблицы и нажмите Ctrl + Shift + End . Это позволит выбрать диапазон справа между вашими данными и последней использованной позицией на листе.
- На вкладке «Главная» нажмите «Очистить». Или щелкните на выделении правой кнопкой мыши и выберите Удалить.
- Щелкните заголовок первой чистой строчки под данными и нажмите Ctrl + Shift + End .
- Выполните те же действия, которые выполнялись в п.2.
- Нажмите Ctrl + S , чтобы сохранить книгу.
Проверьте используемый диапазон, чтобы убедиться, что он теперь содержит только ячейки с данными и без пробелов. Когда вы снова откроете файл, последняя использованная ячейка должна быть последней заполненной.
Примечание. Учитывая, что Microsoft Excel 2007–2019 содержит более 1 000 000 строк и более 16 000 столбцов, вы можете уменьшить размер рабочей области, чтобы пользователи не могли случайно вводить данные в неправильные места.
Если у вас есть небольшая рабочая область на листе и много ненужных чистых строк и столбцов, вы можете скрыть неиспользуемые строки следующим образом:
- Выберите строчку под последней позицией с данными (чтобы выделить её целиком, щелкните заголовок).
- Нажмите Ctrl + Shift + ↓ (стрелка вниз), чтобы расширить выделение до нижней части листа.
- Нажмите Ctrl + 9 , чтобы скрыть выбранные строки.
Думаю, это лучше, чем удалять ненужные строки, поскольку их в любой момент можно вернуть обратно.
Подобным же образом вы скрываете неиспользуемые столбцы:
- Выберите чистый столбец после последнего столбца с данными.
- Нажмите Ctrl + Shift + → (стрелка вправо), чтобы выделить все остальные неиспользуемые столбцы до конца листа.
- Нажмите Ctrl + 0 , чтобы скрыть выбранные колонки. Готово!
Если позже вы решите отобразить все ячейки, выберите весь лист (можно при помощи Ctrl + A ), затем нажмите Ctrl + Shift + 9 , чтобы показать все строки, и Ctrl + Shift + 0 , чтобы показать все столбцы.
Вот как вы можете избавиться от пустых ячеек в Excel. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Проверка данных в Excel: как сделать, использовать и убрать - Мы рассмотрим, как выполнять проверку данных в Excel: создавать правила проверки для чисел, дат или текстовых значений, создавать списки проверки данных, копировать проверку данных в другие ячейки, находить недопустимые записи, исправлять и удалять проверку данных.…
Быстрое удаление пустых столбцов в Excel - В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых столбцов в Excel не может…
Как полностью или частично зафиксировать ячейку в формуле - При написании формулы Excel знак $ в ссылке на ячейку сбивает с толку многих пользователей. Но объяснение очень простое: это всего лишь способ ее зафиксировать. Знак доллара в данном случае служит только одной цели - он указывает,…
Чем отличается абсолютная, относительная и смешанная адресация - Важность ссылки на ячейки Excel трудно переоценить. Ссылка включает в себя адрес, из которого вы хотите получить информацию. При этом используются два основных вида адресации – абсолютная и относительная. Они могут применяться в разных комбинациях…
Относительные и абсолютные ссылки – как создать и изменить - В руководстве объясняется, что такое адрес ячейки, как правильно записывать абсолютные и относительные ссылки в Excel, как ссылаться на ячейку на другом листе и многое другое. Ссылка на ячейки Excel, как бы просто она ни…
6 способов быстро транспонировать таблицу - В этой статье показано, как столбец можно превратить в строку в Excel с помощью функции ТРАНСП, специальной вставки, кода VBA или же специального инструмента. Иначе говоря, мы научимся транспонировать таблицу. В этой статье вы найдете…
4 способа быстро убрать перенос строки в ячейках Excel - В этом совете вы найдете 4 совета для удаления символа переноса строки из ячеек Excel. Вы также узнаете, как заменять разрывы строк другими символами. Все решения работают с Excel 2019, 2016, 2013 и более ранними версиями. Перенос…
Как быстро заполнить пустые ячейки в Excel? - В этой статье вы узнаете, как выбрать сразу все пустые ячейки в электронной таблице Excel и заполнить их значением, находящимся выше или ниже, нулями или же любым другим шаблоном. Заполнять пустоты или нет? Этот вопрос часто…
Как посчитать количество пустых и непустых ячеек в Excel - Если ваша задача - заставить Excel подсчитывать пустые ячейки на листе, прочтите эту статью, чтобы найти 3 способа для этого. Узнайте, как искать и выбирать среди них нужные с помощью стандартных инструментов поиска, или же просто…
Как объединить строки в Excel без потери данных? Примеры и советы. - Объединение строк в Excel - одна из самых распространенных задач, которые нам нужно выполнять время от времени. Проблема в том, что Microsoft Excel не предоставляет для этого надежного инструмента. Кроме того, при использовании стандартного объединения…
Читайте также: