I’m trying to create a query that will return all the rows that have a null value across all but 1 column. Some rows will have more than one null entry somewhere. There is one column I will want to exclude, because at this moment in time all of the entries are null and it is the only column that is allowed to have null values. I am stuck because I don’t know how to include all of the columns in the WHERE.
SELECT *
FROM Analytics
WHERE * IS NULL
Alternatively, I can do a count for one column, but the table has about 67 columns.
SELECT COUNT(*)
FROM Analytics
WHERE P_Id IS NULL
Martin Smith
435k87 gold badges737 silver badges836 bronze badges
asked Jan 23, 2013 at 20:34
Snake_PlisskenSnake_Plissken
3781 gold badge4 silver badges14 bronze badges
3
In SQL Server you can borrow the idea from this answer
;WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' as ns)
SELECT *
FROM Analytics
WHERE (SELECT Analytics.*
FOR xml path('row'), elements xsinil, type
).value('count(//*[local-name() != "colToIgnore"]/@ns:nil)', 'int') > 0
SQL Fiddle
Likely constructing a query with 67 columns will be more efficient but it saves some typing or need for dynamic SQL to generate it.
answered Jan 23, 2013 at 21:08
Martin SmithMartin Smith
435k87 gold badges737 silver badges836 bronze badges
Depending on which RDBMS you’re using, I think your only option (rather than explicitly saying WHERE col1 IS NULL and col2 IS NULL and col3 IS NULL …) would be to use Dynamic SQL.
For example, if you want to get all the column names from a SQL Server database, you could use something like this to return those names:
SELECT
name
FROM
sys.columns
WHERE
object_id = OBJECT_ID('DB.Schema.Table')
You could use FOR XML to create your WHERE clause:
SELECT Name + ' IS NULL AND ' AS [text()]
FROM sys.columns c1
WHERE object_id = OBJECT_ID('DB.Schema.Table')
ORDER BY Name
FOR XML PATH('')
Hope this helps get you started.
Good luck.
answered Jan 23, 2013 at 20:49
1
I don’t have such a table to test, assuming there is no 'x' as data in any field, I think this should work on Sql-Server; (DEMO)
NOTE: I have filtered keyColumn as c.name != 'keyColumn'
DECLARE @S NVARCHAR(max), @Columns VARCHAR(50), @Table VARCHAR(50)
SELECT @Columns = '66', --Number of cols without keyColumn
@Table = 'myTable'
SELECT @S = ISNULL(@S+'+ ','') + 'isnull(convert(nvarchar, ' + c.name + '),''x'')'
FROM sys.all_columns c
WHERE c.object_id = OBJECT_ID(@Table) AND c.name != 'keyColumn'
exec('select * from '+@Table+' where ' + @S + '= replicate(''x'',' + @Columns + ')')
answered Jan 23, 2013 at 20:55
KafKaf
32.9k7 gold badges56 silver badges78 bronze badges
For the SQL beginner user like me, all the query above seem so hard to digest. I think the quickest way is just to write query for all 67 columns. It’s just basically a copy&paste process.
E.g:
select count(*) from user where id is null or
name is null or
review_count is null or
yelping_since is null or
useful is null or
funny is null;
answered Jun 19, 2021 at 0:24
This is for SQLite, so slightly different, but I had the same problem and I ended up writing this small Python script findnulls.py to find all null values in a database:
import sqlite3
import sys
def main():
dbfile = sys.argv[1]
conn = sqlite3.connect(dbfile)
c = conn.cursor()
tables = c.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall()
for table in tables:
tablename = table[0]
cols = c.execute("PRAGMA table_info({0})".format(tablename)).fetchall()
for col in cols:
colname = col[1]
nullvals = c.execute("SELECT COUNT(*) FROM {0} WHERE {1} IS NULL;".format(tablename, colname)).fetchall()
if nullvals[0][0] != 0:
print("found {0} nulls in table: {1} column {2}".format(nullvals[0][0], tablename, colname))
main()
Where you would run it like this:
findnulls.py somedatabase.db
And the output looks like this:
found 1 nulls in table: Channels column MessageId
found 1 nulls in table: Channels column MessageChannel
found 1 nulls in table: Channels column SignalType
found 7 nulls in table: Channels column MinVal
found 7 nulls in table: Channels column MaxVal
found 9 nulls in table: Channels column AvgVal
found 9 nulls in table: Channels column Median
found 9 nulls in table: Channels column StdDev
answered Jan 20, 2022 at 23:23
skelliamskelliam
5143 silver badges10 bronze badges
из справки Excel
Формула массива может выполнить несколько вычислений, а затем вернуть одно значение или группу значений. Формула массива обрабатывает несколько наборов значений, называемых аргументами массива. Каждый аргумент массива должен включать одинаковое число строк и столбцов. Формула массива создается так же, как и другие формулы, с той разницей, что для ввода такой формулы используются клавиши CTRL+SHIFT+ENTER.
Константы массива могут использоваться вместо ссылок, если не требуется вводить каждую постоянную величину в отдельную ячейку на листе.
Некоторые встроенные функции являются формулами массива, и для получения верных результатов их необходимо ввести как массивы.
Вычисление одного значения . Этот тип формулы массива может упростить модель листа, заменив несколько отдельных формул одной формулой массива.
Например, далее вычисляется итоговое значение цен на акции, при этом не используются строки ячеек для вычисления и отображения отдельных значений для каждой акции.
Формула массива, вычисляющая одно значение
При вводе формулы ={СУММ(B2:D2*B3:D3)} в качестве формулы массива она умножает «Акции» и «Цена» для каждой биржи, после чего складывает результаты этих вычислений друг с другом.
Вычисление нескольких значений . Некоторые функции возвращают массивы значений или требуют массив значений в качестве аргумента. Для вычисления нескольких значений с помощью формулы массива необходимо ввести массив в диапазон ячеек, состоящий из того же числа строк или столбцов, что и аргументы массива.
Например, по заданному ряду из трех значений продаж (в столбце B) и ряду из трех месяцев (в столбце A) функция ТЕНДЕНЦИЯ определяет продолжение линейного ряда объемов продаж. Для отображения всех вычисляемых значений формула введена в три ячейки столбца C (C1:C3).
Формула массива, вычисляющая несколько значений
Формула =ТЕНДЕНЦИЯ(B1:B3;A1:A3), введенная как формула массива, возвращает три значения (22196, 17079 и 11962), вычисленные по трем объемам продаж за три месяца.
|
Найти ячейки на листе со значением 0 |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Например, в Excel у вас есть диапазон данных с числами, нулями и пустыми ячейками, как показано на скриншоте ниже, теперь вы просто хотите подсчитать нулевые ячейки в диапазоне, как вы можете это сделать?
Подсчет ячеек с нулями, но без пробелов в диапазоне с формулой
Подсчет ячеек с нулями, но не пустыми в диапазоне с Kutools for Excel
Подсчет ячеек с нулями, но без пробелов в диапазоне с формулой
Подсчет ячеек с нулями, но без пробелов в диапазоне с формулойЧтобы подсчитать ячейки с нулями, но непустые ячейки в диапазоне в Excel, есть формула, которая поможет вам быстро подсчитать только нули.
Выберите пустую ячейку и введите эту формулу = СЧЁТЕСЛИ (A1: H8,0) в него и нажмите Enter key, теперь подсчитываются все нулевые ячейки, за исключением пустых.
Наконечник: В приведенной выше формуле A1: H8 — это диапазон данных, из которого вы хотите посчитать нули, вы можете изменить его по своему усмотрению.
Подсчет ячеек с нулями, но не пустыми в диапазоне с Kutools for Excel
Однако в некоторых случаях вы можете захотеть подсчитать ячейки с нулями, а затем выделить их, чтобы сделать их выдающимися. С помощью формулы вы не можете определить нулевые ячейки. Но с Kutools for ExcelАвтора Выбрать определенные ячейки утилита, вы можете выполнять эти операции одновременно.
После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите диапазон данных, в котором нужно подсчитать нулевые ячейки, и нажмите Кутулс > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
2. в Выбрать определенные ячейки диалог, проверьте Ячейка вариант под Тип выбора раздел и выберите Равно из списка под Конкретный тип раздел, затем введите 0 в текстовое поле рядом с Равно. Смотрите скриншот:
3. Нажмите Ok, теперь появляется диалоговое окно, в котором указывается, сколько нулевых ячеек выбрано, и вы можете нажать OK чтобы закрыть его, и все нулевые ячейки остаются выделенными.
Примечание: Если вы хотите выделить нулевые ячейки, вы можете оставить нулевые ячейки выделенными, а затем выбрать цвет, нажав Главная > Цвет заливки. Смотрите скриншот:
Работы С Нами Kutools for ExcelАвтора Выбрать определенные ячейки утилита, и вы можете выбирать ячейки по нескольким критериям.
Нажмите здесь, чтобы скачать бесплатно Kutools for Excel
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (2)
Оценок пока нет. Оцените первым!
Как определить пустую ячейку в excel — IT Справочник
В этот момент, вы знаете, что третий столбец строки С2:К2 содержит первое непустое значение. Отсюда довольно просто, используя функцию ИНДЕКС, узнать само это первое непустое значение: =ИНДЕКС(МАССИВ;1;3) или =ИНДЕКС(C2:K2;1;ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0)).
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Если формулы создаются Вами и в дальнейшем планируются производить с этими ячейками математические действия, то лучше вместо ставить 0. Если же вы хотите что-то уточнить, я с радостью помогу!
Использование макросов сводит выполнение какой-либо команды до одного нажатия клавиши. Для заполнения пустых ячеек значением предыдущей ячейки также можно задать через макрос. Кроме того, этот макрос автоматически определяет используемый диапазон на листе, а затем он заполняет все пустые ячейки со значением над ними.
Как Найти Пустую Ячейку в Excel Формула. Формула массива | 📝Справочник по Excel
- Он есть в ячейках изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе(вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет — они просто заполняются строкой нулевой длины.
- в ячейке была записана формула, результатом которой стал текст нулевой длины. Самый простой пример такой формулы:
=ЕСЛИ( A1 =1;10;»»)
=IF(A1=1,10,»»)
в итоге, если в ячейке A1 записано любое значение, отличное от 1 формула вернет строку нулевой длины. И если впоследствии формулу заменять значением(Как удалить в ячейке формулу, оставив значения?), то получим нашу псевдо пустую ячейку.
Если формулы создаются Вами и в дальнейшем планируются производить с этими ячейками математические действия, то лучше вместо «» ставить 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл —Параметры —Дополнительно —Показывать нули в ячейках, которые содержат нулевые значения
Если вы хотите удалить строки целиком, то я рекомендую выделять их отмечая левой клавишей мыши за порядковые номера строк. Так, система выделит все колонки в строке и вы за пару кликов сможете их удалить.
Excel – просто! Ищем значение на пересечении
Этот Kutools for Excel Заполнить пустые ячейки может быстро заполнить все пустые ячейки значением выше или фиксированным значением в выбранном диапазоне. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Если применить к таким значениям сортировку, добавить новые строки, либо удалить существующие, формулы могут сбиться, поэтому для корректной дальнейшей работы желательно заменить формулы результатами их вычислений. Если же вы хотите что-то уточнить, я с радостью помогу!
Избежать таких ошибок очень легко и очень просто, нужно всегда следить постоянно за качеством вашей информации и при выявлении ненужных пробелов в ваших данных, удалять их. Найти и удалить незаполненые строки, столбцы и ячейки можно 5 способами:
Excel последнее значение в строке.
- Если у вас есть какой-либо текст в объединенных ячейках, при разделении этих ячеек весь текст перейдет в верхнюю левую ячейку в этой группе объединенных ячеек, которые теперь не объединены.
- Если в выделенном фрагменте нет объединенных ячеек, Excel объединит все ячейки. Вы можете отменить это, используя Control Z или просто снова используя сочетание клавиш.
А если такой файл делали не Вы — он достался «по наследству» или это выгрузка из другой программы, что делать тогда? Я предлагаю такой вот нехитрый код, который во всех выделенных ячейках заменит строки нулевой длины на нормальные пустые ячейки:
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
При работе с данными время от времени возникают ситуации, когда нам необходимо найти значение ячейки на пересечении определенных строки и столбца. Если же вы хотите что-то уточнить, я с радостью помогу!
У этого способа удаления пустых строк есть минус, он заключается в том, что для их удаления вам необходимо отфильтровать каждую колонку. Делать это следует внимательно, дабы не удалить строки с данными.
Excel если не пусто то формула
Шаг 3: Теперь найдите кнопку Найти и выделить в меню Главная и выберите Выделить группу ячеек. Появится окно с различными вариантами. Выберите Пустые ячейки.
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Функция ЕПУСТО в Excel используется для наличия текстовых, числовых, логических и прочих типов данных в указанной ячейке и возвращает логическое значение ИСТИНА, если ячейка является пустой. Если же вы хотите что-то уточнить, я с радостью помогу!
Функция ЕСЛИ выполняет проверку диапазона на наличие пустых ячеек (выражение СУММ(–ЕПУСТО(B3:B12))). Если СУММ вернула значение >0, будет выведено сообщение, содержащее количество незаполненных данными ячеек (СЧИТАТЬПУСТОТЫ) и строку «поля не заполнены», которые склеены знаком «&» (операция конкатенации).
Как в Excel удалить пустые строки — ЭКСЕЛЬ ХАК
Если ячейка содержит формулу, результатом которой является значение «» (Пустой текст ), то начинаются сложности, т.к. одни функции считают, что это пустая ячейка, а другие с ними не соглашаются и считают, что Пустой текст – это текстовая строка. Еще более все усложняет то, что ячейка с Пустым текстом выглядит как пустая (если Пустой текст результат вычисления формулы или запись =»»).
Как заполнить пустые ячейки в Excel?
Все достаточно просто, но перечислять кучу ссылок на ячейки не слишком удобно. К тому же, здесь, как и в предыдущем случае, формула не масштабируема: при изменении таблицы она нуждается в корректировке. Это не слишком удобно, да и забыть можно сделать это.
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Функция ЕПУСТО не разделяет такого подхода и говорит, что в С4 , что-то есть формула ЕПУСТО C4 введенная в ячейку С5 возвращает ЛОЖЬ. Если же вы хотите что-то уточнить, я с радостью помогу!
Рассмотрим работу формулы подробнее. Функция ЕПУСТО возвращает ИСТИНА, если ячейка является пустой, и ЛОЖЬ, если ячейка – не пустая. Посмотрите на строку данных в С2:К2. ЕПУСТО(С2:К2) возвратит массив: .
Найти первое непустое значение в строке
- Функция СЧИТАТЬПУСТОТЫ ведет подсчет пустых ячеек в указанном диапазоне. Так как столбцов в нашей таблице с данными 4 штуки, то если во вспомогательном столбце мы найдем значение «4» это будет означать, что эта строка в таблице пустая и ее можно удалить. Для этого отфильтруем значения таблицы по вспомогательному столбцу по числу «4» и определим пустые строки:
Третий способ, который лично мне очень нравится своей простотой. Единственное условие для его работы – названия в столбцах и строках должны быть без пробелов (их можно удалить или заменить на нижнее подчеркивание «_»).
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Представим, что в нашей таблице с данными есть строки, в которых всего лишь одна ячейка пустая, а остальные ячейки в строке содержат данные на примере ниже выделены ячейки выделены желтым цветом. Если же вы хотите что-то уточнить, я с радостью помогу!
Функция ЕПУСТО в Excel используется для наличия текстовых, числовых, логических и прочих типов данных в указанной ячейке и возвращает логическое значение ИСТИНА, если ячейка является пустой. Если в указанной ячейке содержатся какие-либо данные, результатом выполнения функции ЕПУСТО будет логическое значение ЛОЖЬ.
Использование функции
Если столбец содержит и текстовые и числовые значения, то для определения номера строки последней заполненной ячейки можно предложить универсальное решение:
=МАКС(ЕСЛИОШИБКА(ПОИСКПОЗ(«*»;$A:$A;-1);0);
ЕСЛИОШИБКА(ПОИСКПОЗ(1E+306;$A:$A;1);0))