Удаляем в EXCEL пропуски в списке (формулы)
Если список значений содержит пропуски (пустые ячейки), то это может существенно затруднить его дальнейший анализ. С помощью формул уберем пустые ячейки из колонки с данными. Также напишем формулу, чтобы удалить нули из списка значений. В конце статьи научимся удалять вообще любое заданное значение из списка: символ, число, текстовую строку.
Пусть имеется список с пустыми ячейками (столбец А ).
Задача
Убрать пустые ячейки из списка, сформировав формулами список в соседнем столбце. То есть под словом «убрать» будем понимать не удаление значения из исходного списка, а формирование еще одного списка, но уже без лишних символов. Чтобы действительно убрать значения из списка нужно использовать макросы — программу на VBA.
Решение
Для избавления от пустых ячеек запишем в ячейке B2 формулу массива и скопируем ее вниз (см. файл примера): =ЕСЛИОШИБКА(ДВССЫЛ(«A»&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($A$2:$A$14);»»;СТРОКА($A$2:$A$14));СТРОКА(A1)));»»)
Получим в соседнем столбце B список со значениями из исходого, но уже без пропусков. Формула работает одинакового и для текстовых значений и для чисел. Алгоритм работы формулы следующий:
- ЕСЛИ(ЕПУСТО($A$2:$A$14);»»;СТРОКА($A$2:$A$14)) – если ячейка не пуста, то эта часть формулы возвращает номер строки. То есть формируется массив номеров строк, НЕ содержащих пустоты <2:"":4:5:6:"":"":9:10:"":"":13:14>На месте пустых ячеек в массиве будет символ «» (пустой текст), но можно его заменить в формуле на любую текстовую строку, например «ккк». Проверить результат можно выделив эту часть формулы и нажав клавишу F9 ;
- Функция НАИМЕНЬШИЙ() сортирует массив строк по возрастанию. В сортированном списке сначала будут идти номера строк затем значения «», т.к. в EXCEL считается, что любое текстовое значение больше любого числа (значение пустой текст — текстовое значение);
- Далее для функции ДВССЫЛ() формируются адреса ячеек с непустыми значениями. Например, ДВССЫЛ(«A»&2) возвращает значение из ячейки А2 . Для пустых ячеек будет формироваться ошибочные адреса ячеек, состоящие только из символа А. Это вызовет ошибку после применения функции ДВССЫЛ();
- Функция ЕСЛИОШИБКА() вместо ошибки будет возвращать «». Этот символ не отображается в ячейке и ячейка выглядит пустой.
На самом деле в соседнем столбце B список будет отличаться не только тем, что в нем значения будут идти подряд без пропусков. Если в исходном списке пустые ячейки действительно не содержали ничего, то в новом списке в пустых ячейках будут значения Пустой текст «». Хотя внешний вид пустых ячеек из обоих списков будет неотличим, но формулы увидят разницу. То есть, если Вы планируете делать дальнейшие манипуляции с новым списком, то имейте ввиду, что теперь пустые ячейки в конце списка теперь не совсем пусты — они содержат текстовое значение «». Подробнее про это специфическое значение читайте здесь .
Изменим немного формулу: =ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ( ЕПУСТО($A$2:$A$14);»»;$A$2:$A$14);СТРОКА(A1));»»)
Получим тот же список, но еще и сортированный по возрастанию (работает только для чисел).
Список также можно сформировать в столбце С другой формулой массива :
СписокСпропусками в формуле — это динамический диапазон , который образован формулой:
Длину списка с пропусками можно вычислить с помощью формулы:
СОВЕТ:
Для удаления и выделения пустых строк и ячеек традиционными средствами EXCEL, пользуйтесь идеями из статей Удаляем пустые строки в таблице и Выделение группы ячеек . О том, что EXCEL понимает под пустыми ячейками, читайте в статье Подсчет пустых ячеек .
Удаляем заданные символы из списка
Часто в списке присутствуют ненужные для дальнейшего анализа значения, например 0 (нуль).
Немного модернизировав вышеуказанную формулу получим универсальный подход для удаления вообще любого символа, числа или текстовой строки из исходного списка (см. файл примера, в котором на листе Произвольный символ приведена универсальнаяформула).
Пусть исходный список находится в диапазоне A12:A24, а в ячейке B6 содержится значение которое нужно удалить из ячеек списка.
Единственным отличием является выражение $A$12:$A$24=$B$6, которое заменило формулу с ЕПУСТО(. )
Теперь если значение в исходном списке не равно искомому значению, то вместо него будет выведено значение «» (в конце списка).
Как исключить ячейку из формулы в excel
mashabul, здравствуйте.
В Excel можно почти всё. Один из способов для Excel 2007 и выше:
1). Выделить Вашу таблицу полностью. На панели инструментов выбрать вкладку «Главная» => «Стили» => «Форматировать как таблицу».
2). Поставить галочку «есть заголовки» и жать «Ок».
3). Выбрать любую ячейку в таблице и перейти во вкладку «Конструктор» => «Параметры стилей таблиц» и поставить галочку напротив «Строка итогов».
Теперь в самом низу таблицы у Вас появилась дополнительная строка с подписью «Итог». На этой строке, кликнув по ячейке нужного столбца, можете выбрать сумму из раздвижного списка.
Теперь, если Вы нажмёте правой клавишей мыши на любые строки и выберете «скрыть» — в сумме они учитываться НЕ будут.
У работы с данными, форматированными как таблицы, есть несколько преимуществ, о которых можете почитать в интернете.
mashabul, здравствуйте.
В Excel можно почти всё. Один из способов для Excel 2007 и выше:
1). Выделить Вашу таблицу полностью. На панели инструментов выбрать вкладку «Главная» => «Стили» => «Форматировать как таблицу».
2). Поставить галочку «есть заголовки» и жать «Ок».
3). Выбрать любую ячейку в таблице и перейти во вкладку «Конструктор» => «Параметры стилей таблиц» и поставить галочку напротив «Строка итогов».
Теперь в самом низу таблицы у Вас появилась дополнительная строка с подписью «Итог». На этой строке, кликнув по ячейке нужного столбца, можете выбрать сумму из раздвижного списка.
Теперь, если Вы нажмёте правой клавишей мыши на любые строки и выберете «скрыть» — в сумме они учитываться НЕ будут.
У работы с данными, форматированными как таблицы, есть несколько преимуществ, о которых можете почитать в интернете. Rioran
Сообщение mashabul, здравствуйте.
В Excel можно почти всё. Один из способов для Excel 2007 и выше:
1). Выделить Вашу таблицу полностью. На панели инструментов выбрать вкладку «Главная» => «Стили» => «Форматировать как таблицу».
2). Поставить галочку «есть заголовки» и жать «Ок».
3). Выбрать любую ячейку в таблице и перейти во вкладку «Конструктор» => «Параметры стилей таблиц» и поставить галочку напротив «Строка итогов».
Теперь в самом низу таблицы у Вас появилась дополнительная строка с подписью «Итог». На этой строке, кликнув по ячейке нужного столбца, можете выбрать сумму из раздвижного списка.
Теперь, если Вы нажмёте правой клавишей мыши на любые строки и выберете «скрыть» — в сумме они учитываться НЕ будут.
У работы с данными, форматированными как таблицы, есть несколько преимуществ, о которых можете почитать в интернете. Автор — Rioran
Дата добавления — 29.05.2014 в 17:26
Excel исключить ячейку из диапазона
Вы можете быстро найти и выделить определенные ячейки или диапазоны, введя их имена или ссылки на ячейки в поле имя , которое расположено слева от строки формул.
Для выбора именованных и неименованных ячеек или диапазонов можно также использовать команду Перейти к ( F5 или CTRL + G).
Важно: Для выбора именованных ячеек и диапазонов необходимо сначала определить их. Дополнительные сведения приведены в разделе Определение и использование имен в формулах .
Чтобы выбрать именованную ячейку или диапазон, щелкните стрелку рядом с полем имя , чтобы отобразить список именованных ячеек или диапазонов, а затем выберите нужное имя.
Чтобы выделить несколько именованных диапазонов или ссылок на ячейки, щелкните стрелку рядом с полем Имя и выберите имя первого диапазона или первой ссылки на ячейку из тех, которые нужно выделить. Затем, удерживая нажатой клавишу CTRL , щелкните имена других ячеек или диапазонов в поле имя .
Чтобы выделить неименованную ссылку на ячейку или диапазон, введите ссылку на ячейку или диапазон ячеек, который нужно выделить, а затем нажмите клавишу Ввод. Например, введите B3, чтобы выделить эту ячейку, или введите B1: B3, чтобы выделить диапазон ячеек.
Примечание: Вы не можете удалить или изменить имена, определенные для ячеек или диапазонов в поле имя . Имена можно удалять и изменять только в диспетчере имен (вкладка " формулы ", Группа " определенные имена "). Дополнительные сведения см. в статье Определение и использование имен в формулах.
Нажмите клавишу F5 или CTRL + G , чтобы открыть диалоговое окно "перейти ".
В списке Перейти щелкните имя ячейки или диапазона, который вы хотите выделить, или введите ссылку на ячейку в поле ссылка , а затем нажмите кнопку ОК.
Например, в поле ссылка введите B3 , чтобы выделить эту ячейку, или введите B1: B3 , чтобы выделить диапазон ячеек. Чтобы выделить несколько ячеек или диапазонов, введите их в поле ссылки , разделенные запятыми. Если вы ссылаетесь на диапазон с сбросом, созданный с помощью динамической формулы массива, вы можете добавить оператор Range. Например, если в ячейках A1: A4 есть массив, вы можете выбрать его, введя a1 # в поле ссылка , а затем нажать кнопку ОК.
Совет: Чтобы быстро найти и выделить все ячейки с данными определенного типа (например, с формулами) или только ячейки, соответствующие определенным условиям (например, видимые ячейки или последнюю ячейку листа, содержащую данные или форматирование), в контекстном меню выберите пункт выделить , а затем выберите нужный параметр.
Перейдите в раздел формулы > определенные имена > Диспетчер имен.
Выберите имя, которое вы хотите изменить или удалить.
Выберите команду изменить или Удалить.
Удерживая нажатой клавишу CTRL , щелкните каждую ячейку или диапазон ячеек, которые вы хотите включить, в левой части экрана. Если вы выберете этот параметр, вы можете щелкнуть ненужную ячейку, чтобы отменить ее выделение.
Если вы выбрали данные для карт Bing, убедитесь, что они находятся в названиях городов, названий стран и т. д. В противном случае Bing не будет сопоставляться.
Чтобы выделить ячейки, вы можете ввести ссылку в диалоговом окне Выбор данных или развернуть стрелку в диалоговом окне и выделить ячейки, щелкнув их. Дополнительные параметры выбора нужных ячеек описаны в предыдущем разделе.
Если вы выберете данные для преобразования в евро, убедитесь, что они являются данными денежных единиц.
Чтобы выделить ячейки, введите ссылку в поле исходные данные или щелкните стрелку рядом с полем и выделите ячейки, щелкнув их. Дополнительные параметры выбора нужных ячеек описаны в предыдущем разделе.
Вы можете выделить смежные ячейки в Excel в Интернете, щелкнув ячейку и перетащив ее, чтобы увеличить диапазон. Однако вы не можете выделить отдельные ячейки или диапазон, если они не находятся рядом друг с другом. Если у вас есть классическое приложение Excel, вы можете открыть книгу в Excel и выделить несмежные ячейки, щелкнув их, удерживая нажатой клавишу CTRL . Дополнительные сведения можно найти в разделе выделение конкретных ячеек или диапазонов в Excel.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
У меня есть диапазон, который я хочу суммировать, это A2: M35. Однако, если столбец "N" имеет номер 1 в нем, я хочу исключить эту целую строку из суммы. Итак, если N3 содержит 1, я хочу исключить диапазон A3: M3 из расчета суммы. Возможно ли это?
Я также должен указать, что 1 или 0 в столбце N является флагом, указывающим, следует ли исключать эту строку или нет (1 = да, 0 = нет). Однако это значение получается путем проверки того, будут ли любые значения в этой строке = "исключены". Таким образом, дополнительное усложнение здесь заключается в том, что даже если строки с "исключенными" в них должны быть исключены, расчет суммы покажет "#VALUE", поскольку он считает, что некоторые из значений имеют неправильный тип данных (хотя они не следует включать).
Я создал таблицу посещаемости в Excel 2016, который отсчитывает случаи отсутствия и опоздания для каждого ученика и всего класса.
Я также добавил ПУНКТУАЛЬНОСТЬ RATE строку, которая показывает мне, что% класс на время каждый день (я исключен отсутствующие учащиеся из подсчета).
Заключительный шаг, чтобы выяснить текущую среднюю ставку срок пунктуальности, и это где я застрял, потому что я хочу, чтобы исключить те дни, на которые класс не имеет место (что бывает много). У меня есть ряд в верхней части, где записываю взял ли класс место или нет (я использую символы Y или N).
Как сделать Excel учитывать ежедневно пунктуальность записи (для текущего срока пунктуальности рисунка) только если класс проходил? Другими словами, как мне сделать это включать только те ячейки, в среднем диапазоне, которые имеют соответствующий «Y» значение в верхней строке?
Надеюсь, что это имеет смысл для кого-то . Большое спасибо,
Как включить или исключить ячейку из среднего диапазона, в зависимости от значения ячеек в другой строке?
Я создал таблицу посещаемости в Excel 2016, который отсчитывает случаи отсутствия и опоздания для каждого ученика и всего класса.
Я также добавил ПУНКТУАЛЬНОСТЬ RATE строку, которая показывает мне, что% класс на время каждый день (я исключен отсутствующие учащиеся из подсчета).
Заключительный шаг, чтобы выяснить текущую среднюю ставку срок пунктуальности, и это где я застрял, потому что я хочу, чтобы исключить те дни, на которые класс не имеет место (что бывает много). У меня есть ряд в верхней части, где записываю взял ли класс место или нет (я использую символы Y или N).
Как сделать Excel учитывать ежедневно пунктуальность записи (для текущего срока пунктуальности рисунка) только если класс проходил? Другими словами, как мне сделать это включать только те ячейки, в среднем диапазоне, которые имеют соответствующий «Y» значение в верхней строке?