Как скрыть пустые строки в excel автоматически
На отдельном листе, защищенным паролем, имеется таблица, откуда автоматически переносятся вычисления с другого листа и эта таблица отправляется на печать в производство. В самой таблице строчек сделано под максимально возможное количество значений (40 строчек), но при этом за раз туда переносится в среднем от двух до пяти значений Таким образом более 30 строчек чаще всего всегда остаются пустыми.
Можно ли без помощи макросов автоматически скрывать пустые строки, оставляя только те, где есть значение? Приложил файл с пояснениями
На отдельном листе, защищенным паролем, имеется таблица, откуда автоматически переносятся вычисления с другого листа и эта таблица отправляется на печать в производство. В самой таблице строчек сделано под максимально возможное количество значений (40 строчек), но при этом за раз туда переносится в среднем от двух до пяти значений Таким образом более 30 строчек чаще всего всегда остаются пустыми.
Можно ли без помощи макросов автоматически скрывать пустые строки, оставляя только те, где есть значение? Приложил файл с пояснениями
На отдельном листе, защищенным паролем, имеется таблица, откуда автоматически переносятся вычисления с другого листа и эта таблица отправляется на печать в производство. В самой таблице строчек сделано под максимально возможное количество значений (40 строчек), но при этом за раз туда переносится в среднем от двух до пяти значений Таким образом более 30 строчек чаще всего всегда остаются пустыми.
Можно ли без помощи макросов автоматически скрывать пустые строки, оставляя только те, где есть значение? Приложил файл с пояснениями
Спасибо Автор - Saigon
Дата добавления - 16.09.2019 в 22:35
Хотя если действия пользователя это и есть скрытие пустых строк, то можно считать, что это произошло автоматически и без макросов.
Пример не очень хороший, если три таблицы на листе, то скрытие строк одной таблицы приведет к скрытию этих же строк в другой таблице, а там есть данные. Возможно вам нужно при заполнении данных на другом листе, чтобы на одном листе формировались таблицы без пустых строк? Если это не так, то значит я ничего не понял, что вы хотите и какой будет конечный вид ваших таблиц.
Хотя если действия пользователя это и есть скрытие пустых строк, то можно считать, что это произошло автоматически и без макросов.
Пример не очень хороший, если три таблицы на листе, то скрытие строк одной таблицы приведет к скрытию этих же строк в другой таблице, а там есть данные. Возможно вам нужно при заполнении данных на другом листе, чтобы на одном листе формировались таблицы без пустых строк? Если это не так, то значит я ничего не понял, что вы хотите и какой будет конечный вид ваших таблиц. gling
Хотя если действия пользователя это и есть скрытие пустых строк, то можно считать, что это произошло автоматически и без макросов.
Пример не очень хороший, если три таблицы на листе, то скрытие строк одной таблицы приведет к скрытию этих же строк в другой таблице, а там есть данные. Возможно вам нужно при заполнении данных на другом листе, чтобы на одном листе формировались таблицы без пустых строк? Если это не так, то значит я ничего не понял, что вы хотите и какой будет конечный вид ваших таблиц. Автор - gling
Дата добавления - 16.09.2019 в 23:37
Да, наверно действительно немного путано объяснил.
Таблица будет одна. Грубо говоря у владельца файла есть 10 операций (они на другом листе). Он вносит значения в первую операцию, специальная формула их обрабатывает и полученные значения переносит в требуемую таблицу. На производстве на основании этих данные из таблицы делают процесс, дают обратную связь и владелец файла вносит некие другие значения уже во вторую операцию. Формула их снова обрабатывает, данные снова переносятся в требуемую таблицу, полностью заменяя параметры после первого процесса, снова на печать, на производство, потом третья операция и так далее до десятой операции.
Так вот, для всех операции есть общий набор неких параметров-значений (это как раз строчки, и их около 40). Допустим, что в первую операцию владелец файла заносит данные для первого и пятого значения. Соответственно в сводной таблице требуется отобразить расчетные значения только для первой и пятой строчек. Во второй операции это могут быть уже, например, данные для четвертого, восьмого и десятого значения, следовательно после второй операции в сводной таблице должны отображаться только строки 1,8 и 10.
Прошу прощения, что не прикрепляю таблицу - там конфеденциальные сведения. Но если будет по прежнему не понятно, то что нибудь придумаю и выложу пример.
Да, наверно действительно немного путано объяснил.
Таблица будет одна. Грубо говоря у владельца файла есть 10 операций (они на другом листе). Он вносит значения в первую операцию, специальная формула их обрабатывает и полученные значения переносит в требуемую таблицу. На производстве на основании этих данные из таблицы делают процесс, дают обратную связь и владелец файла вносит некие другие значения уже во вторую операцию. Формула их снова обрабатывает, данные снова переносятся в требуемую таблицу, полностью заменяя параметры после первого процесса, снова на печать, на производство, потом третья операция и так далее до десятой операции.
Так вот, для всех операции есть общий набор неких параметров-значений (это как раз строчки, и их около 40). Допустим, что в первую операцию владелец файла заносит данные для первого и пятого значения. Соответственно в сводной таблице требуется отобразить расчетные значения только для первой и пятой строчек. Во второй операции это могут быть уже, например, данные для четвертого, восьмого и десятого значения, следовательно после второй операции в сводной таблице должны отображаться только строки 1,8 и 10.
Прошу прощения, что не прикрепляю таблицу - там конфеденциальные сведения. Но если будет по прежнему не понятно, то что нибудь придумаю и выложу пример.
Да, наверно действительно немного путано объяснил.
Таблица будет одна. Грубо говоря у владельца файла есть 10 операций (они на другом листе). Он вносит значения в первую операцию, специальная формула их обрабатывает и полученные значения переносит в требуемую таблицу. На производстве на основании этих данные из таблицы делают процесс, дают обратную связь и владелец файла вносит некие другие значения уже во вторую операцию. Формула их снова обрабатывает, данные снова переносятся в требуемую таблицу, полностью заменяя параметры после первого процесса, снова на печать, на производство, потом третья операция и так далее до десятой операции.
Так вот, для всех операции есть общий набор неких параметров-значений (это как раз строчки, и их около 40). Допустим, что в первую операцию владелец файла заносит данные для первого и пятого значения. Соответственно в сводной таблице требуется отобразить расчетные значения только для первой и пятой строчек. Во второй операции это могут быть уже, например, данные для четвертого, восьмого и десятого значения, следовательно после второй операции в сводной таблице должны отображаться только строки 1,8 и 10.
Прошу прощения, что не прикрепляю таблицу - там конфеденциальные сведения. Но если будет по прежнему не понятно, то что нибудь придумаю и выложу пример.
Спасибо Автор - Saigon
Дата добавления - 17.09.2019 в 00:18
Пример результата облегчил бы понимание, а раз нет примера приходится угадывать. Может так надо?
Выписка из правил форума:
- Чтобы понять и помочь - достаточно таблицы на 10-20 строк
- При этом старайтесь сохранить структуру, расположение таблиц, имена листов - аналогично оригиналу
- Если файл содержит конфиденциальную информацию - обязательно замените Ваши данные на нейтральные
Пример результата облегчил бы понимание, а раз нет примера приходится угадывать. Может так надо?
Выписка из правил форума:
- Чтобы понять и помочь - достаточно таблицы на 10-20 строк
- При этом старайтесь сохранить структуру, расположение таблиц, имена листов - аналогично оригиналу
- Если файл содержит конфиденциальную информацию - обязательно замените Ваши данные на нейтральные
- Чтобы понять и помочь - достаточно таблицы на 10-20 строк
- При этом старайтесь сохранить структуру, расположение таблиц, имена листов - аналогично оригиналу
- Если файл содержит конфиденциальную информацию - обязательно замените Ваши данные на нейтральные
Спасибо, концепция абсолютно верная. Я приложил пример. Заполняется последовательно с 1 по 9 операции, а на листе "сводная" всегда отображаются вычисления с последней операции. Вот там то и надо убрать строчки.
Спасибо, концепция абсолютно верная. Я приложил пример. Заполняется последовательно с 1 по 9 операции, а на листе "сводная" всегда отображаются вычисления с последней операции. Вот там то и надо убрать строчки. Saigon
Спасибо, концепция абсолютно верная. Я приложил пример. Заполняется последовательно с 1 по 9 операции, а на листе "сводная" всегда отображаются вычисления с последней операции. Вот там то и надо убрать строчки. Автор - Saigon
Дата добавления - 17.09.2019 в 20:26
Оказывается зря надеялся.
Ну можно сделать чтобы параметр 4 записался в сводной в первую строку, а следующий параметр какой должен быть? или в следующую строку запишетс параметр у которого тоже есть максимальное значение? Т.е. все параметры с максимальным значением должны записаться по порядку в сводной? Пример покажите како вы видете конечный результат. В первом файле примере были значения 300; 300 и 1200 в одной таблице. В каком порядке они должны собраться в одной сводной и какие критерии из отбора в сводную? Покажите.
Оказывается зря надеялся.
Ну можно сделать чтобы параметр 4 записался в сводной в первую строку, а следующий параметр какой должен быть? или в следующую строку запишетс параметр у которого тоже есть максимальное значение? Т.е. все параметры с максимальным значением должны записаться по порядку в сводной? Пример покажите како вы видете конечный результат. В первом файле примере были значения 300; 300 и 1200 в одной таблице. В каком порядке они должны собраться в одной сводной и какие критерии из отбора в сводную? Покажите. gling
Оказывается зря надеялся.
Ну можно сделать чтобы параметр 4 записался в сводной в первую строку, а следующий параметр какой должен быть? или в следующую строку запишетс параметр у которого тоже есть максимальное значение? Т.е. все параметры с максимальным значением должны записаться по порядку в сводной? Пример покажите како вы видете конечный результат. В первом файле примере были значения 300; 300 и 1200 в одной таблице. В каком порядке они должны собраться в одной сводной и какие критерии из отбора в сводную? Покажите. Автор - gling
Дата добавления - 17.09.2019 в 21:49
пример 1 - то, как должна выглядеть сводная таблица после внесения значений в первую операцию, пример 2 - после внесения во вторую операцию, пример 3 - в третью, пример 4 - в четвертую и т.д. до десятой операции
пример 1 - то, как должна выглядеть сводная таблица после внесения значений в первую операцию, пример 2 - после внесения во вторую операцию, пример 3 - в третью, пример 4 - в четвертую и т.д. до десятой операции Saigon
пример 1 - то, как должна выглядеть сводная таблица после внесения значений в первую операцию, пример 2 - после внесения во вторую операцию, пример 3 - в третью, пример 4 - в четвертую и т.д. до десятой операции Автор - Saigon
Дата добавления - 17.09.2019 в 22:05
вариант решения в файле. Для меня все 4 файла примера одинаковые, по одной строке заполняется в каждом. Разница в том что берутся они из разных строк общей таблицы. Может еще какая то сортировка должна быть?
А уж ежели хотите заполнять по одной строке в сводной таблице, то в формуле столбца С, СТРОКА() замените на единицу. Получится так
вариант решения в файле. Для меня все 4 файла примера одинаковые, по одной строке заполняется в каждом. Разница в том что берутся они из разных строк общей таблицы. Может еще какая то сортировка должна быть?
А уж ежели хотите заполнять по одной строке в сводной таблице, то в формуле столбца С, СТРОКА() замените на единицу. Получится так
вариант решения в файле. Для меня все 4 файла примера одинаковые, по одной строке заполняется в каждом. Разница в том что берутся они из разных строк общей таблицы. Может еще какая то сортировка должна быть?
А уж ежели хотите заполнять по одной строке в сводной таблице, то в формуле столбца С, СТРОКА() замените на единицу. Получится так
Именно что не из строк, а из столбцов! Для каждой операции есть два столбца. Зеленый - туда вносим, желтый - оттуда получаем результат. Я понял свою ошибку. Видимо без какого-то макроса тут никак. В сводной уже есть формулы, их трогать нельзя.
Подскажите, может есть какой-то макрос или настройка, чтобы все пустые строчки в сводной убирал?
Именно что не из строк, а из столбцов! Для каждой операции есть два столбца. Зеленый - туда вносим, желтый - оттуда получаем результат. Я понял свою ошибку. Видимо без какого-то макроса тут никак. В сводной уже есть формулы, их трогать нельзя.
Подскажите, может есть какой-то макрос или настройка, чтобы все пустые строчки в сводной убирал? Saigon
Именно что не из строк, а из столбцов! Для каждой операции есть два столбца. Зеленый - туда вносим, желтый - оттуда получаем результат. Я понял свою ошибку. Видимо без какого-то макроса тут никак. В сводной уже есть формулы, их трогать нельзя.
Подскажите, может есть какой-то макрос или настройка, чтобы все пустые строчки в сводной убирал? Автор - Saigon
Дата добавления - 17.09.2019 в 22:58
А автофильтром нельзя? Ведь любой макрос или надстройку нужно запустить нажав на кнопку, так почему эта кнопка не кнопка автофильтра? Можно конечно привязать макрос к какому то событию, но это нужно знать что и как делается на других листах.
А почему нельзя, если другие формулы выполняют тот же результат только отсортированный? Возможно у вас в дальнейших расчетах участвуют жестко привязанные ячейки сводной таблицы. Вам решать, как вам удобнее.
У вас нет пустых строк. Во всех ячейках таблицы формулы. Если писать макрос то зачем нужны формулы в сводной, если можно макросом создавать готовую сводную таблицу без скрытия строк?
А автофильтром нельзя? Ведь любой макрос или надстройку нужно запустить нажав на кнопку, так почему эта кнопка не кнопка автофильтра? Можно конечно привязать макрос к какому то событию, но это нужно знать что и как делается на других листах.
А почему нельзя, если другие формулы выполняют тот же результат только отсортированный? Возможно у вас в дальнейших расчетах участвуют жестко привязанные ячейки сводной таблицы. Вам решать, как вам удобнее.
У вас нет пустых строк. Во всех ячейках таблицы формулы. Если писать макрос то зачем нужны формулы в сводной, если можно макросом создавать готовую сводную таблицу без скрытия строк? gling
А почему нельзя, если другие формулы выполняют тот же результат только отсортированный? Возможно у вас в дальнейших расчетах участвуют жестко привязанные ячейки сводной таблицы. Вам решать, как вам удобнее.
У вас нет пустых строк. Во всех ячейках таблицы формулы. Если писать макрос то зачем нужны формулы в сводной, если можно макросом создавать готовую сводную таблицу без скрытия строк? Автор - gling
Дата добавления - 17.09.2019 в 23:20
Автофильтром увы никак. Лист защищен паролем.
У меня цель, если уж совсем приземленно. Вот таблица, строки с 1 по 10. При каждом вычислении в любую из этих строк может записаться одно или несколько значений. 9 остальных пустые. Их надо автоматически скрыть.
Может записаться 2, 3, 5 значений. Но оставшиеся строки все равно должны быть скрыты автоматически
Автофильтром увы никак. Лист защищен паролем.
У меня цель, если уж совсем приземленно. Вот таблица, строки с 1 по 10. При каждом вычислении в любую из этих строк может записаться одно или несколько значений. 9 остальных пустые. Их надо автоматически скрыть.
Может записаться 2, 3, 5 значений. Но оставшиеся строки все равно должны быть скрыты автоматически Saigon
Автофильтром увы никак. Лист защищен паролем.
У меня цель, если уж совсем приземленно. Вот таблица, строки с 1 по 10. При каждом вычислении в любую из этих строк может записаться одно или несколько значений. 9 остальных пустые. Их надо автоматически скрыть.
Может записаться 2, 3, 5 значений. Но оставшиеся строки все равно должны быть скрыты автоматически Автор - Saigon
Дата добавления - 17.09.2019 в 23:30
Зациклились вы на этом скрытии. Какая разница скрыты они или просто игнорированны при формировании таблицы? Вам нужно напечатать таблицу без пустых строк, так в файле примере так и получается. Какие то тонкости вы наверное не договариваете. А на защищенном листе без снятия защиты вы скрыть пустые строки не сможете. Нужно будет писать макрос который снимает защиту скрывает строки и вновь устанавливает защиту. Первоначально про защиту не сообщалось. А сколько ещё таких подводных камней? Если не подходит предложенное, попытайтесь написать макрос или найти готовый. Удачи.
Зациклились вы на этом скрытии. Какая разница скрыты они или просто игнорированны при формировании таблицы? Вам нужно напечатать таблицу без пустых строк, так в файле примере так и получается. Какие то тонкости вы наверное не договариваете. А на защищенном листе без снятия защиты вы скрыть пустые строки не сможете. Нужно будет писать макрос который снимает защиту скрывает строки и вновь устанавливает защиту. Первоначально про защиту не сообщалось. А сколько ещё таких подводных камней? Если не подходит предложенное, попытайтесь написать макрос или найти готовый. Удачи. gling
Предположим, что у нас имеется вот такая таблица, с которой приходится "танцевать" каждый день:
Кому таблица покажется маленькой - мысленно умножьте ее по площади в двадцать раз, добавив еще пару кварталов и два десятка крупных российских городов.
Задача - временно убирать с экрана ненужные в данный момент для работы строки и столбцы, т.е.,
- скрывать подробности по месяцам, оставляя только кварталы
- скрывать итоги по месяцам и по кварталам, оставляя только итог за полугодие
- скрывать ненужные в данный момент города (я работаю в Москве - зачем мне видеть Питер?) и т.д.
В реальной жизни примеров таких таблиц - море.
Способ 1. Скрытие строк и столбцов
Способ, прямо скажем, примитивный и не очень удобный, но два слова про него сказать можно. Любые выделенные предварительно строки или столбцы на листе можно скрыть, щелкнув по заголовку столбца или строки правой кнопкой мыши и выбрав в контекстном меню команду Скрыть (Hide) :
Для обратного отображения нужно выделить соседние строки/столбцы и, щелкнув правой кнопкой мыши, выбрать в меню, соответственно, Отобразить (Unhide) .
Проблема в том, что с каждым столбцом и строкой придется возиться персонально, что неудобно.
Способ 2. Группировка
Если выделить несколько строк или столбцов, а затем выбрать в меню Данные - Группа и структура - Группировать (Data - Group and Outline - Group) , то они будут охвачены прямоугольной скобкой (сгруппированы). Причем группы можно делать вложенными одна в другую (разрешается до 8 уровней вложенности):
Более удобный и быстрый способ - использовать для группировки выделенных предварительно строк или столбцов сочетание клавиш Alt+Shift+стрелка вправо, а для разгруппировки Alt+Shift+стрелка влево, соответственно.
Такой способ скрытия ненужных данных гораздо удобнее - можно нажимать либо на кнопку со знаком "+" или "-", либо на кнопки с цифровым обозначением уровня группировки в левом верхнем углу листа - тогда все группы нужного уровня будут сворачиваться или разворачиваться сразу.
Кроме того, если в вашей таблице присутствуют итоговые строки или столбцы с функцией суммирования соседних ячеек, то есть шанс (не 100%-ый правда), что Excel сам создаст все нужные группировки в таблице одним движением - через меню Данные - Группа и структура - Создать структуру (Data - Group and Outline - Create Outline) . К сожалению, подобная функция работает весьма непредсказуемо и на сложных таблицах порой делает совершенную ерунду. Но попробовать можно.
В Excel 2007 и новее все эти радости находятся на вкладке Данные (Data) в группе Структура (Outline) :
Способ 3. Скрытие помеченных строк/столбцов макросом
Этот способ, пожалуй, можно назвать самым универсальным. Добавим пустую строку и пустой столбец в начало нашего листа и отметим любым значком те строки и столбцы, которые мы хотим скрывать:
Теперь откроем редактор Visual Basic (ALT+F11), вставим в нашу книгу новый пустой модуль (меню Insert - Module) и скопируем туда текст двух простых макросов:
Как легко догадаться, макрос Hide скрывает, а макрос Show - отображает обратно помеченные строки и столбцы. При желании, макросам можно назначить горячие клавиши (Alt+F8 и кнопка Параметры), либо создать прямо на листе кнопки для их запуска с вкладки Разработчик - Вставить - Кнопка (Developer - Insert - Button) .
Способ 4. Скрытие строк/столбцов с заданным цветом
Допустим, что в приведенном выше примере мы, наоборот, хотим скрыть итоги, т.е. фиолетовые и черные строки и желтые и зеленые столбцы. Тогда наш предыдущий макрос придется немного видоизменить, добавив вместо проверки на наличие "х" проверку на совпадение цвета заливки с произвольно выбранными ячейками-образцами:
Однако надо не забывать про один нюанс: этот макрос работает только в том случае, если ячейки исходной таблицы заливались цветом вручную, а не с помощью условного форматирования (это ограничение свойства Interior.Color). Так, например, если вы с помощью условного форматирования автоматически подсветили в своей таблице все сделки, где количество меньше 10:
. и хотите их скрывать одним движением, то предыдущий макрос придется "допилить". Если у вас Excel 2010-2013, то можно выкрутиться, используя вместо свойства Interior свойство DisplayFormat.Interior, которое выдает цвет ячейки вне зависимости от способа, которым он был задан. Макрос для скрытия синих строк тогда может выглядеть так:
Ячейка G2 берется в качестве образца для сравнения цвета. К сожалению, свойство DisplayFormat появилось в Excel только начиная с 2010 версии, поэтому если у вас Excel 2007 или старше, то придется придумывать другие способы.
Пустые строки и столбцы могут быть головной болью в таблицах во многих случаях. Стандартные функции сортировки, фильтрации, подведения итогов, создания сводных таблиц и т.д. воспринимают пустые строки и столбцы как разрыв таблицы, не подхватывая данные, расположенные за ними далее. Если таких разрывов много, то удалять их вручную может оказаться весьма затратно, а удалить сразу всех "оптом", используя фильтрацию не получится, т.к. фильтр тоже будет «спотыкаться» на разрывах.
Давайте рассмотрим несколько способов решения этой задачи.
Способ 1. Поиск пустых ячеек
Это, может, и не самый удобный, но точно самый простой способ вполне достойный упоминания.
Предположим, что мы имеем дело вот с такой таблицей, содержащей внутри множество пустых строк и столбцов (для наглядности выделены цветом):
Допустим, мы уверены, что в первом столбце нашей таблицы (колонка B) всегда обязательно присутствует название какого-либо города. Тогда пустые ячейки в этой колонке будут признаком ненужных пустых строк. Чтобы быстро их все удалить делаем следующее:
- Выделяем диапазон с городами (B2:B26)
- Нажимаем клавишу F5 и затем кнопку Выделить (Go to Special) или выбираем на вкладке Главная - Найти и выделить - Выделить группу ячеек (Home - Find&Select - Go to special) .
- В открывшемся окне выбираем опцию Пустые ячейки (Blanks) и жмём ОК – должны выделиться все пустые ячейки в первом столбце нашей таблицы.
- Теперь выбираем на вкладке Главная команду Удалить - Удалить строки с листа (Delete - Delete rows) или жмём сочетание клавиш Ctrl + минус - и наша задача решена.
Само-собой, от пустых столбцов можно избавиться совершенно аналогично, взяв за основу шапку таблицы.
Способ 2. Поиск незаполненных строк
Как вы, возможно, уже сообразили, предыдущий способ сработает только в том случае, если в наших данных обязательно присутствую полностью заполненные строки и столбцы, за которые можно зацепиться при поиске пустых ячеек. Но что, если такой уверенности нет, и в данных могут содержаться и пустые ячейки в том числе?
Взгляните, например, на следующую таблицу - как раз такой случай:
Здесь подход будет чуть похитрее:
-
Введём в ячейку A2 функцию СЧЁТЗ (COUNTA) , которая вычислит количество заполненных ячеек в строке правее и скопируем эту формулу вниз на всю таблицу:
К сожалению, со столбцами такой трюк уже не проделать – фильтровать по столбцам Excel пока не научился.
Способ 3. Макрос удаления всех пустых строк и столбцов на листе
Для автоматизации подобной задачи можно использовать и простой макрос. Нажмите сочетание клавиш Alt + F11 или выберите на вкладке Разработчик - Visual Basic (Developer - Visual Basic Editor) . Если вкладки Разработчик не видно, то можно включить ее через Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) .
В открывшемся окне редактора Visual Basic выберите команду меню Insert - Module и в появившийся пустой модуль скопируйте и вставьте следующие строки:
Закройте редактор и вернитесь в Excel.
Теперь нажмите сочетание Alt + F8 или кнопку Макросы на вкладке Разработчик. В открывшемся окне будут перечислены все доступные вам в данный момент для запуска макросы, в том числе только что созданный макрос DeleteEmpty. Выберите его и нажмите кнопку Выполнить (Run) - все пустые строки и столбцы на листе будут мгновенно удалены.
Способ 4. Запрос Power Query
Ещё один способ решить нашу задачу и весьма частый сценарий - это удаление пустых строк и столбцов в Power Query.
Сначала давайте загрузим нашу таблицу в редактор запросов Power Query. Можно конвертировать её в динамическую "умную" сочетанием клавиш Ctrl+T или же просто выделить наш диапазон данных и дать ему имя (например Данные) в строке формул, преобразовав в именованный:
Теперь используем команду Данные - Получить данные - Из таблицы/диапазона (Data - Get Data - From table/range) и грузим всё в Power Query:
Дальше всё просто:
- Удаляем пустые строки командой Главная - Сократить строки - Удалить строки - Удалить пустые строки (Home - Remove Rows - Remove empty rows).
- Щёлкаем правой кнопкой мыши по заголовку первого столбца Город и выбираем в контекстном меню команду Отменить свёртывание других столбцов (Unpivot Other Columns). Наша таблица будет, как это технически правильно называется, нормализована - преобразована в три столбца: город, месяц и значение с пересечения города и месяца из исходной таблицы. Особенность этой операции в Power Query в том, что она пропускает в исходных данных пустые ячейки, что нам и требуется:
Имеем диапазон ячеек с данными, в котором есть пустые ячейки:
Задача - удалить пустые ячейки, оставив только ячейки с информацией.
Способ 1. Грубо и быстро
- Выделяем исходный диапазон
- Жмем клавишу F5, далее кнопка Выделить(Special) . В открывшмся окне выбираем Пустые ячейки(Blanks) и жмем ОК.
Способ 2. Формула массива
Для упрощения дадим нашим рабочим диапазонам имена, используя Диспетчер Имен (Name Manager) на вкладке Формулы (Formulas) или - в Excel 2003 и старше - меню Вставка - Имя - Присвоить (Insert - Name - Define)
Диапазону B3:B10 даем имя ЕстьПустые, диапазону D3:D10 - НетПустых. Диапазоны должны быть строго одного размера, а расположены могут быть где угодно относительно друг друга.
Теперь выделим первую ячейку второго диапазона (D3) и введем в нее такую страшноватую формулу:
В английской версии это будет:
=IF(ROW()-ROW(НетПустых)+1>ROWS(ЕстьПустые)-COUNTBLANK(ЕстьПустые),"",INDIRECT(ADDRESS(SMALL((IF(ЕстьПустые<>"",ROW(ЕстьПустые),ROW()+ROWS(ЕстьПустые))),ROW()-ROW(НетПустых)+1),COLUMN(ЕстьПустые),4)))
Причем ввести ее надо как формулу массива, т.е. после вставки нажать не Enter (как обычно), а Ctrl+Shift+Enter. Теперь формулу можно скопировать вниз, используя автозаполнение (потянуть за черный крестик в правом нижнем углу ячейки) - и мы получим исходный диапазон, но без пустых ячеек:
Способ 3. Пользовательская функция на VBA
Если есть подозрение, что вам часто придется повторять процедуру удаления пустых ячеек из диапазонов, то лучше один раз добавить в стандартный набор свою функцию для удаления пустых ячеек, и пользоваться ей во всех последующих случаях.
Для этого откройте редактор Visual Basic (ALT+F11), вставьте новый пустой модуль (меню Insert - Module) и скопируйте туда текст этой функции:
Не забудьте сохранить файл и вернитесь из редактора Visual Basic в Excel. Чтобы использовать эту функцию в нашем примере:
При работе в программе Excel довольно часто можно встретить ситуацию, когда значительная часть массива листа используется просто для вычисления и не несет информационной нагрузки для пользователя. Такие данные только занимают место и отвлекают внимание. К тому же, если пользователь случайно нарушит их структуру, то это может произвести к нарушению всего цикла вычислений в документе. Поэтому такие строки или отдельные ячейки лучше вообще скрыть. Кроме того, можно спрятать те данные, которые просто временно не нужны, чтобы они не мешали. Давайте узнаем, какими способами это можно сделать.
Процедура скрытия
Спрятать ячейки в Экселе можно несколькими совершенно разными способами. Остановимся подробно на каждом из них, чтобы пользователь сам смог понять, в какой ситуации ему будет удобнее использовать конкретный вариант.
Способ 1: группировка
Одним из самых популярных способов скрыть элементы является их группировка.
-
Выделяем строки листа, которые нужно сгруппировать, а потом спрятать. При этом не обязательно выделять всю строку, а можно отметить только по одной ячейке в группируемых строчках. Далее переходим во вкладку «Данные». В блоке «Структура», который располагается на ленте инструментов, жмем на кнопку «Группировать».
Способ 2: перетягивание ячеек
Самым интуитивно понятным способом скрыть содержимое ячеек, наверное, является перетягивание границ строк.
-
Устанавливаем курсор на вертикальной панели координат, где отмечены номера строк, на нижнюю границу той строчки, содержимое которой хотим спрятать. При этом курсор должен преобразоваться в значок в виде креста с двойным указателем, который направлен вверх и вниз. Затем зажимаем левую кнопку мыши и тянем указатель вверх, пока нижняя и верхняя границы строки не сомкнутся.
Способ 3: групповое скрытие ячеек перетягиванием
Если нужно таким методом скрыть сразу несколько элементов, то прежде их следует выделить.
-
Зажимаем левую кнопку мыши и выделяем на вертикальной панели координат группу тех строк, которые желаем скрыть.
Если диапазон большой, то выделить элементы можно следующим образом: кликаем левой кнопкой по номеру первой строчки массива на панели координат, затем зажимаем кнопку Shift и щелкаем по последнему номеру целевого диапазона.
Способ 4: контекстное меню
Два предыдущих способа, конечно, наиболее интуитивно понятны и простые в применении, но они все-таки не могут обеспечить полного скрытия ячеек. Всегда остается небольшое пространство, зацепившись за которое можно обратно расширить ячейку. Полностью скрыть строку имеется возможность при помощи контекстного меню.
- Выделяем строчки одним из трёх способов, о которых шла речь выше:
- исключительно при помощи мышки;
- с использованием клавиши Shift;
- с использованием клавиши Ctrl.
Способ 5: лента инструментов
Также скрыть строки можно, воспользовавшись кнопкой на ленте инструментов.
-
Выделяем ячейки, находящиеся в строках, которые нужно скрыть. В отличие от предыдущего способа всю строчку выделять не обязательно. Переходим во вкладку «Главная». Щелкаем по кнопке на ленте инструментов «Формат», которая размещена в блоке «Ячейки». В запустившемся списке наводим курсор на единственный пункт группы «Видимость» — «Скрыть или отобразить». В дополнительном меню выбираем тот пункт, который нужен для выполнения поставленной цели – «Скрыть строки».
Способ 6: фильтрация
Для того, чтобы скрыть с листа содержимое, которое в ближайшее время не понадобится, чтобы оно не мешало, можно применить фильтрацию.
-
Выделяем всю таблицу или одну из ячеек в её шапке. Во вкладке «Главная» жмем на значок «Сортировка и фильтр», который расположен в блоке инструментов «Редактирование». Открывается список действий, где выбираем пункт «Фильтр».
Способ 7: скрытие ячеек
Теперь поговорим о том, как скрыть отдельные ячейки. Естественно их нельзя полностью убрать, как строчки или колонки, так как это разрушит структуру документа, но все-таки существует способ, если не полностью скрыть сами элементы, то спрятать их содержимое.
-
Выделяем одну или несколько ячеек, которые нужно спрятать. Кликаем по выделенному фрагменту правой кнопкой мыши. Открывается контекстное меню. Выбираем в нем пункт «Формат ячейки…».
Как видим, существует несколько разных способов, с помощью которых можно спрятать строки в Экселе. Причем большинство из них используют совершенно разные технологии: фильтрация, группировка, сдвиг границ ячеек. Поэтому пользователь имеет очень широкий выбор инструментов для решения поставленной задачи. Он может применить тот вариант, который считает более уместным в конкретной ситуации, а также более удобным и простым для себя. Кроме того, с помощью форматирования имеется возможность скрыть содержимое отдельных ячеек.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Читайте также: