Пусто excel

Пустая ячейка определяется Excel-ем как не пустая. Что за глюк?

Эта статья будет близка тем, кому приходится часто работать с отчетами, выгруженными в Excel из программ вроде 1С, SAP и им подобных. Хотя, справедливости ради, надо отметить, что и в обычных файлах Excel такая проблема может так же встречаться, особенно, если в файле использовались различные формулы.
Вводные данные: есть отчет который выкачивается из сторонней программы. В нем есть ячейки, с виду пустые: в них нет пробелов, нет переносов на строки, никаких символов, нет объектов, условного форматирования, в настройках не стоит скрывать нули(ФайлПараметрыДополнительноПоказывать нули в ячейках, которые содержат нулевые значения). Даже если перейти в режим редактирования ячейки – там пусто.
Если попробовать найти такие “пустые” ячейки(выделить все ячейки листа – F5 – Выделить – Пустые ячейки ) – они не выделяются. Но фильтр при этом их видит как пустые и фильтрует как пустые.
Любые математические действия(умножение на ячейку, сложение, деление и т.п.) внутри формул с такими ячейками выдают ошибку #ЗНАЧ! (#VALUE!) , а функция ЕПУСТО (ISBLANK) считает ячейку не пустой. Формулы вроде СУММ (SUM) и СЧЁТ (COUNT) игнорируют такие ячейки, а СЧЁТЗ (COUNTA) считает их заполненными.

И самое удивительное – если выделить такую ячейку вручную и нажать Delete (или вкладка Главная -группа РедактированиеОчистить содержимое) – то ячейка становится действительно пустой и с ней начинают работать формулы и другие функции Excel как с реально пустой.
Здесь возникает главный вопрос: что с этой ячейкой не так, если там и до Delete ничего не было?
А не так с ней вот что: практически во всех программах есть такое понятие строковых(текстовых) данных, как строка нулевой длины(еще её часто называют нулевая строка. В Visual Basic for Application такая строка обозначается константой vbNullString , в ячейках её можно встретить как сочетание двух кавычек подряд – “” ). Визуально такой текст себя никак не проявляет, однако это все же текст. А любой текст это уже не пусто, но и не число.
Откуда может появится такой текст в ячейках? Здесь несколько вариантов:

  1. Он есть в ячейках изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе(вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет – они просто заполняются строкой нулевой длины.
  2. в ячейке была записана формула, результатом которой стал текст нулевой длины. Самый простой пример такой формулы:
    =ЕСЛИ( A1 =1;10;””)
    =IF(A1=1,10,””)
    в итоге, если в ячейке A1 записано любое значение, отличное от 1 формула вернет строку нулевой длины. И если впоследствии формулу заменять значением(Как удалить в ячейке формулу, оставив значения?), то получим нашу псевдо пустую ячейку.
    Если формулы создаются Вами и в дальнейшем планируются производить с этими ячейками математические действия, то лучше вместо “” ставить 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: ФайлПараметрыДополнительноПоказывать нули в ячейках, которые содержат нулевые значения

А если такой файл делали не Вы – он достался “по наследству” или это выгрузка из другой программы, что делать тогда? Я предлагаю такой вот нехитрый код, который во всех выделенных ячейках заменит строки нулевой длины на нормальные пустые ячейки:

Sub ReplaceNullString() Dim rR As Range, rF As Range, rC As Range Dim avR, lr As Long, lc As Long Set rR = Intersect(ActiveSheet.UsedRange, Selection) On Error Resume Next Set rR = rR.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If rR Is Nothing Then MsgBox “В выделенных ячейках нет значений!”, vbInformation, “www.excel-vba.ru” Exit Sub End If Set rF = rR.Find(vbNullString, , xlFormulas, xlWhole) If Not rF Is Nothing Then avR = rR.Value For lr = 1 To UBound(avR, 1) For lc = 1 To UBound(avR, 2) If avR(lr, lc) = “” Then rR.Item(lr, lc).Value = Empty Next lc Next lr MsgBox “Строки нулевой длины заменены”, vbInformation, “www.excel-vba.ru” Exit Sub End If MsgBox “Строк нулевой длины на листе нет или лист защищен”, vbInformation, “www.excel-vba.ru” End Sub

Как это работает:
если раньше никогда не работали с макросами, то рекомендую ознакомиться со статьями:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?

  1. создаем в книге новый стандартный модуль: Alt+F11InsertModule()
  2. копируем в созданный модуль приведенный выше код
  3. выделяем нужный диапазон(если надо заменить на всем листе – то можно выделить все ячейки листа или целиком нужные столбцы – программа сама определить нужные данные)
  4. нажимаем Alt+F8 и в появившемся окне выбираем ReplaceNullString

Короткая видеоинструкция:

Статья помогла? Поделись ссылкой с друзьями!

Источник: www.excel-vba.ru

Примеры функции ЕПУСТО для проверки пустых ячеек в Excel

Функция ЕПУСТО в Excel используется для наличия текстовых, числовых, логических и прочих типов данных в указанной ячейке и возвращает логическое значение ИСТИНА, если ячейка является пустой. Если в указанной ячейке содержатся какие-либо данные, результатом выполнения функции ЕПУСТО будет логическое значение ЛОЖЬ.

Примеры использования функции ЕПУСТО в Excel

Пример 1. В таблице Excel находятся результаты (баллы) по экзамену, который проводился в учебном заведении. В данной электронной ведомости напротив некоторых студентов оценки не указаны, поскольку их отправили на пересдачу. В столбце рядом вывести текстовую строку «Сдал» напротив тех, кому выставили оценки, и «На пересдачу» – напротив не сдавших с первого раза.

Выделим ячейки C3:C18 и запишем следующую формулу:

Формула ЕСЛИ выполняет проверку возвращаемого результата функции ЕПУСТО для диапазона ячеек B3:B18 и возвращает один из вариантов (“На пересдачу” или “Сдал”). Результат выполнения функции:

Теперь часть данной формулы можно использовать для условного форматирования:

  1. Выделите диапазон ячеек C3:C18 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
  2. В появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулы для определения форматируемых ячеек» и введите следующую формулу:
  3. Нажмите на кнопку «Формат» (как на образцу), потом укажите в окне «Формат ячеек» красный цвет заливки и нажмите ОК на всех открытых окнах:

На против незаполненных (пустых) ячеек или двоек мы получаем соответственное сообщение «На пересдачу» и красную заливку.

Почему нужно использовать функцию ЕПУСТО при проверке пустых ячеек

У выше указанном примере можно изменить формулу используя двойные кавычки (“”) в место функции проверки ячеек на пустоту, и она также будет работать:

=ЕСЛИ(ИЛИ( B3=”” ;B3=2);”На пересдачу”;”Сдал”)

Но не всегда! Все зависит от значений, которые могут содержать ячейки. Обратите внимание на то как по-разному себя ведут двойные кавычки, и функция ЕПУСТО если у нас в ячейках находятся одни и те же специфические значения:

Как видно на рисунке в ячейке находится символ одинарной кавычки. Первая формула (с двойными кавычками вместо функции) ее не видит. Более того в самой ячейке A1 одинарная кавычке не отображается так как данный спецсимвол в Excel предназначенный для отображения значений в текстовом формате. Это удобно, например, когда нам нужно отобразить саму формулу, а не результат ее вычисления как сделано в ячейках D1 и D2. Достаточно лишь перед формулой ввести одинарную кавычку и теперь отображается сама формула, а не возвращаемый ее результат. Но функция ЕПУСТО видит, что в действительности ячейка А1 не является пустой!

Проверка на пустую ячейку в таблице Excel

Пример 2. В таблице Excel записаны некоторые данные. Определить, все ли поля заполнены, или есть как минимум одно поле, которое является пустым.

Исходная таблица данных:

Чтобы определить наличие пустых ячеек используем следующую формулу массива (CTRL+SHIFT+Enter):

Функция СУММ используется для определения суммы величин, возвращаемых функцией –ЕПУСТО для каждой ячейки из диапазона B3:B17 (числовых значений, поскольку используется двойное отрицание). Если запись СУММ(–ЕПУСТО(B3:B17) возвращает любое значение >0, функция ЕСЛИ вернет значение ИСТИНА.

То есть, в диапазоне B3:B17 есть одна или несколько пустых ячеек.

Примечание: в указанной выше формуле были использованы символы «–». Данный вид записи называется двойным отрицанием. В данном случае двойное отрицание необходимо для явного преобразования данных логического типа к числовому. Некоторые функции Excel не выполняют автоматического преобразования данных, поэтому механизм преобразования типов приходится запускать вручную. Наиболее распространенными вариантами преобразования текстовых или логических значений к числовому типу является умножение на 1 или добавление 0 (например, =ИСТИНА+0 вернет число 1, или =«23»*1 вернет число 23. Однако использование записи типа =–ИСТИНА ускоряет работу функций (по некоторым оценкам прирост производительности составляет до 15%, что имеет значение при обработке больших объемов данных).

Как посчитать количество пустых ячеек в Excel

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

Формула для расчета (формула массива):

Функция ЕСЛИ выполняет проверку диапазона на наличие пустых ячеек (выражение СУММ(–ЕПУСТО(B3:B12))). Если СУММ вернула значение >0, будет выведено сообщение, содержащее количество незаполненных данными ячеек (СЧИТАТЬПУСТОТЫ) и строку «поля не заполнены», которые склеены знаком «&» (операция конкатенации).

Особенности использования функции ЕПУСТО в Excel

Функция ЕПУСТО в Excel относится к числу логических функций (выполняющих проверку какого-либо условия, например, ЕСЛИ, ЕССЫЛКА, ЕЧИСЛО и др., и возвращающих результаты в виде данных логического типа: ИСТИНА, ЛОЖЬ). Синтаксическая запись функции:

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

  1. Если в качестве аргумента функции было явно передано какое-либо значение (например, =ЕПУСТО(ИСТИНА), =ЕПУСТО(«текст»), =ЕПУСТО(12)), результат ее выполнения – значение ЛОЖЬ.
  2. Если требуется, чтобы функция возвращала значение ИСТИНА, если ячейка не является пустой, ее можно использовать совместно с функцией НЕ. Например, =НЕ(ЕПУСТО(A1)) вернет ИСТИНА, если A1 не является пустой.
  3. Запись типа =ЕПУСТО(АДРЕС(x;y)) всегда будет возвращать значение ложь, поскольку функция АДРЕС(x;y) возвращает ссылку на ячейку, то есть непустое значение.
  4. Функция возвращает значение ЛОЖЬ даже в тех случаях, когда в переданной в качестве аргумента ячейке содержится ошибка или ссылка на ячейку. Это суждение справедливо и для случаев, когда в результате выполнения какой-либо функции в ячейку была выведена пустая строка. Например, в ячейку A1 была введена формула =ЕСЛИ(2>1;””;ЛОЖЬ), которая вернет пустую строку «». В этом случае функция =ЕПУСТО(A1) вернет значение ЛОЖЬ.
  5. Если требуется проверить сразу несколько ячеек, можно использовать функцию в качестве формулы массива (выделить требуемое количество пустых ячеек, ввести формулу «=ЕПУСТО(» и в качестве аргумента передать диапазон исследуемых ячеек, для выполнения использовать комбинацию клавиш Ctrl+Shift+Enter)

Источник: exceltable.com

Исправление Microsoft Excel, открывающего пустой документ

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

Иногда, когда вы дважды щелкаете файл Excel, программное обеспечение MS Excel открывается как обычно, и вы ожидаете, что ваш документ будет там, но вместо этого он может открыть пустое окно без какой-либо таблицы внутри него. Если Microsoft Excel открывает пустой серый документ или рабочий лист для вас, то вот несколько вещей, которые вы могли бы сделать, чтобы решить эту проблему.

Excel открывает пустое окно

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

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

Запустить DDE

DDE означает динамический обмен данными ; оно используется для информирования определенного программного обеспечения о том, что файл с расширением, которое вы поддерживаете, предлагается открыть. Если DDE отключен в MS Excel, Excel откроется, но не будет загружать электронную таблицу, которую вы ожидаете, чтобы проверить это, выполните следующие действия:

  1. Запустите Microsoft Excel
  2. В Ленте Файла нажмите на Опции
  3. Отправляйтесь в аванс Секцию
  4. Выделите Общая группа предпочтений на странице; это будет к нижнему концу страницы.

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

Попробуйте Скрыть/Показать таблицу

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

Проверьте надстройки

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

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

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

Проверьте Fie Ассоциации

Откройте Windows 10 Настройки> Приложения> Приложения по умолчанию и проверьте сопоставление файлов для документов Excel. Пользователь Windows 8/7 может использовать панель управления. Если вам нужно исправить ассоциации файлов, воспользуйтесь нашим бесплатным Fixer Ассоциации файлов, чтобы сделать это легко.

Отключить аппаратное ускорение

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

Ремонт офисной установки

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

Это некоторые из решений проблемы с Microsoft Excel, если вы нашли другое решение, сообщите нам об этом в разделе комментариев ниже.

Источник: techarks.ru

Найти первое непустое значение в строке

Задача: требуется формула, которая позволяла найти первое непустое значение в строке, т.е., возвращала бы номер первой непустой ячейки в строке. Предположим, что данные представлены в столбцах С:K (рис. 1).

Рис. 1. Формула находит первую непустую ячейку в каждой строке и возвращает ее номер в массиве

Скачать заметку в формате Word или pdf, примеры в формате Excel

Решение: формула в А2: =ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0). Хотя эта формула имеет дело с массивом ячеек, она в конечном счете возвращает одно значение, так что использовать при вводе нажатие Ctrl+Shift+Enter не требуется (о формулах массива см. Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel).

Рассмотрим работу формулы подробнее. Функция ЕПУСТО возвращает ИСТИНА, если ячейка является пустой, и ЛОЖЬ, если ячейка – не пустая. Посмотрите на строку данных в С2:К2. ЕПУСТО(С2:К2) возвратит массив: <ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА>.

Обратите внимание, что далее этот массив вычитается из 1. При попытке использовать значения ИСТИНА и ЛОЖЬ в математической формуле, значение ИСТИНА интерпретируется как 1, а значение ЛОЖЬ – как 0. Задавая 1-ЕПУСТО(С2:К2), вы преобразуете массив логических значений ИСТИНА/ЛОЖЬ в числовую последовательность нулей и единиц: <0;0;1;0;0;0;0;0;0>.

Итак, фрагмент формулы 1-ЕПУСТО(С2:К2) возвращает массив <0;0;1;0;0;0;0;0;0>. Это немного странно, так как от такого фрагмента Excel ожидает, что вернется одно значение. Странно, но не смертельно. Функция ИНДЕКС также обычно возвращает одно значение. Но вот, что написано в Справке Excel: Если указать в качестве аргумента номер_строки или номер_столбца значение 0 (ноль), функция ИНДЕКС возвратит массив значений для целого столбца или целой строки соответственно. Чтобы использовать значения, возвращенные как массив, введите функцию ИНДЕКС как формулу массива в горизонтальный диапазон ячеек для строки и в вертикальный — для столбца.

Если функция ИНДЕКС возвращает массив, ее можно использовать внутри других функций, ожидающих, что аргумент является массивом.

Итак, указав в качестве третьего аргумента функции ИНДЕКС(1-ЕПУСТО(C2:K2);1;) значение ноль, мы получим массив <0;0;1;0;0;0;0;0;0>.

Функция ПОИСКПОЗ выполняет поиск искомого значения в одномерном массиве и возвращает относительную позицию первого найденного совпадения. Формула =ПОИСКПОЗ(1,МАССИВ,0) просит Excel найти номер ячейки в МАССИВЕ, которая содержит первую встретившуюся единицу. Функция ПОИСКПОЗ определяет в каком столбце содержится первая непустая ячейка. Когда вы просите ПОИСКПОЗ найти первую 1 в массиве <0;0;1;0;0;0;0;0;0>, она возвращает 3.

Итак =ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0) превращается в =ПОИСКПОЗ(1;<0;0;1;0;0;0;0;0;0>;0) и возвращает результат 3.

В этот момент, вы знаете, что третий столбец строки С2:К2 содержит первое непустое значение. Отсюда довольно просто, используя функцию ИНДЕКС, узнать само это первое непустое значение: =ИНДЕКС(МАССИВ;1;3) или =ИНДЕКС(C2:K2;1;ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0)).

Рис. 2. Формула находит первую непустую ячейку в каждой строке и возвращает значение этой ячейки

Дополнительные сведения: если все ячейки пустые, то формула возвращает ошибку #Н/Д.

Альтернативные стратегии: когда вы из единицы вычитаете значение ЕПУСТО, вы преобразуете логические значения ИСТИНА/ЛОЖЬ в числовые 1/0. Вы могли бы пропустить этот шаг, но тогда вам придется искать ЛОЖЬ в качестве первого аргумента функция ПОИСКПОЗ: =ИНДЕКС(C2:K2;1;ПОИСКПОЗ(ЛОЖЬ;ИНДЕКС(ЕПУСТО(C2:K2);1;0);0)).

Источник: baguzin.ru

Как в Excel заполнить пустые ячейки нулями или значениями из ячеек выше (ниже)

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

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

Итак, моим ответом будет – заполнять! Давайте посмотрим, как мы сможем это сделать.

Как выделить пустые ячейки на листе Excel

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

  1. Выделите столбцы или строки, в которых требуется заполнить пустоты.
  2. Нажмите Ctrl+G или F5, чтобы отобразить диалоговое окно Go To (Переход).
  3. Нажмите кнопку Special (Выделить).

Замечание: Если Вы вдруг забыли сочетание клавиш, откройте вкладку Home (Главная) и в разделе Editing (Редактирование) из выпадающего меню Find & Select (Найти и выделить) выберите команду Go To Special (Выделить группу ячеек). На экране появится то же диалоговое окно.

  1. Команда Go To Special (Выделить группу ячеек) позволяет выбрать ячейки определённого типа, например, ячейки, содержащие формулы, примечания, константы, пустые ячейки и так далее.
  2. Выберите параметр Blanks (Пустые ячейки) и нажмите ОК.Теперь в выбранном диапазоне выделены только пустые ячейки и всё готово к следующему шагу.

Формула для заполнения пустых ячеек значениями из ячеек выше (ниже)

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

Если Вы собираетесь заполнить пропуски значениями из ближайшей не пустой ячейки сверху или снизу, то потребуется ввести в одну из пустых ячеек очень простую формулу. Затем просто скопируйте её во все пустые ячейки. Как это сделать – читайте далее.

  1. Выделите все пустые ячейки.
  2. Нажмите F2 или просто поместите курсор в строку формул, чтобы приступить к вводу формулы в активную ячейку. Как видно на снимке экрана выше, активна ячейка C4.
  3. Введите знак равенства (=).
  4. Укажите ячейку, находящуюся выше или ниже, нажав стрелку вверх или вниз, или просто кликнув по ней.Формула (=C3) показывает, что в ячейке C4 появится значение из ячейки C3.
  5. Нажмите Ctrl+Enter, чтобы скопировать формулу во все выделенные ячейки.

Отлично! Теперь в каждой выделенной ячейке содержится ссылка на ячейку, расположенную над ней.

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

Заполняем пустые ячейки нулями или другим заданным значением

Что если Вам нужно заполнить все пустые ячейки в Вашей таблице нулями или другими числовыми или текстовыми значениями? Далее показаны два способа решения этой задачи.

Способ 1

  1. Выделите все пустые ячейки
  2. Нажмите F2, чтобы ввести значение в активную ячейку.
  3. Введите нужное число или текст.
  4. Нажмите Ctrl+Enter.

За несколько секунд Вы заполнили все пустые ячейки нужным значением.

Способ 2

  1. Выделите диапазон, содержащий пустые ячейки.
  2. Нажмите Ctrl+H, чтобы появилось диалоговое окно Find & Replace (Найти и заменить).
  3. Перейдите на вкладку Replace (Заменить).
  4. Оставьте поле Find what (Найти) пустым и введите нужное значение в поле Replace with (Заменить на).
  5. Нажмите кнопку Replace All (Заменить все).

Какой бы способ Вы ни выбрали, обработка всей таблицы Excel займёт не больше минуты!

Теперь Вы знаете приёмы, как заполнить пустые ячейки различными значениями в Excel 2013. Уверен, для Вас не составит труда сделать это как при помощи простой формулы, так и при помощи инструмента Find & Replace (Найти и заменить).

Источник: office-guru.ru

Заполнение пустых ячеек значениями из соседних ячеек

Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек – по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками – например после копирования результатов сводных таблиц или выгрузок в 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) .

Источник: www.planetaexcel.ru