Інформація по темі "" | Публікації по тегу
24.02.13
30504 2
Друкувати
Обране

MS Excel: как скрыть нулевые значения

Материал для этой статьи подсказало письмо одного нашего постоянного читателя: «Приветствую всех сотрудников «Б & К»! Давно собираюсь задать такой вопрос по работе с MS Excel. Есть таблица, в ней много нулевых значений. В принципе, ничего страшного в этом нет. Но читать таблицу, где от нулей буквально рябит в глазах, очень неудобно. Подскажите, можно ли скрыть нулевые значения в таблице Excel, причем все сделать так, чтобы расчеты при этом не пострадали? Думаю, такая тема будет интересна многим моим коллегам». Что ж, вопрос понятен. Предлагаю посмотреть на практические приемы его решения.




 

Скрыть нулевые значения можно разными способами. Основные из них — при помощи формул, через параметры программы Excel и с применением автофильтра. Каждый из этих способов имеет свои преимущества и недостатки. В чем они заключаются, мы сейчас и посмотрим. Начнем c формул.

 

 

Скрываем нулевые значения при помощи функции Если()

Как скрыть нулевые значения при помощи формул, я покажу на примере отчета, который нам уже знаком по одной из предыдущих публикаций. Почему был выбран именно этот отчет, я скажу чуть позже. Итак, есть база данных, изображенная на рис. 1. По этой базе сформирован отчет о динамике закупок для нескольких контрагентов за определенный период (рис. 2). Значения в итоговом отчете посчитаны при помощи функции «СУММПРОИЗВ()». Например, в ячейке «B3» формула выглядит так: «=СУММПРОИЗВ((БД!$C$2:$C$65536=B$1)*(БД!$D$2:$D$65536=$A2)*(БД!$F$2:$F$65536)*(БД!$G$2:$G$65536))». С таким приемом применения функции мы уже знакомы. Данные за выбранный период оказались таковы, что в итоговом отчете есть много нулевых значений (рис. 2). Избавиться от них можно, изменив расчетную формулу. Для этого нужно при помощи функции «ЕСЛИ()» проверить результат, т. е. предварительно посчитать эту формулу. Если результат будет равен нулю, функция «ЕСЛИ()» должна вернуть в ячейку пустую строку. В противном случае — она должна еще раз пересчитать формулу и вернуть результат без изменений. Иными словами, все выглядит так. Предположим, что в определенной ячейке таблицы у нас записано выражение. Я обозначу его как «Формула». Мы хотим проанализировать результат расчета по этому выражению и, если он будет равен нулю, заменить его пустой строкой. Тогда расчетную формулу мы должны переписать так: «=ЕСЛИ(Формула=0;"";Формула)». Здесь я хотел бы сделать важное замечание. Использование функции «ЕСЛИ()» может существенно усложнить формулу. Это понятно, ведь исходное выражение в функции «=ЕСЛИ()» будет фигурировать два раза. Однако это лишь видимое усложнение. Главная «фишка» состоит в том, что исходная формула у нас уже есть. И все, что остается сделать, — это подставить ее в функцию проверки два раза. А если вспомнить, что при работе с формулами можно использовать буфер обмена и редактировать их в текстовом режиме, то задачу можно решить в считанные секунды. И сейчас я подробно покажу, как это сделать для таблицы на рис. 2. Кстати, такую таблицу я выбрал не случайно. В ней используется довольно сложная формула на базе функции «СУММПРОИЗВ()». Результат формулы с проверкой на нули будет еще длиннее. Тем не менее, нам ничего не придется печатать. Конечное выражение мы получим без особых усилий. Делаем так:

1. Открываем таблицу с итоговым отчетом (рис. 2).

2. Становимся на ячейку «B3». Здесь записано выражение для расчета объема закупок по контрагенту «ТОВ «УкрСнаб»» за «13.08.2012».

3. Щелкаем левой кнопкой в строке формул (или нажимаем клавишу «F2»). Станет доступно содержимой ячейки «B3».

4. Выделяем текст формулы без знака «=» и нажимаем комбинацию «Ctrl+C» (копируем его в буфер обмена). Этот текст мы сейчас вставим внутрь функции «ЕСЛИ()».

5. Нажимаем «Ecs» — выходим из режима редактирования ячейки.

6. В ячейку «B3» вводим текст «=ЕСЛИ(». Тем самым мы приступили к вводу формулы.

7. Нажимаем «Ctrl+V» (вставляем содержимое из буфера обмена). В данный момент у нас должно получиться выражение: «=ЕСЛИ(СУММПРОИЗВ((БД!$C$2:$C$65536=B$1)* (БД!$D$2:$D$65536=$A2)*(БД!$F$2:$F$65536)*(БД!$G$2:$G$65536))». Полужирным начертанием показан результат добавления в формулу содержимого буфера обмена. Пока ничего не нажимаем, продолжаем вводить формулу!

8. Печатаем текст «=0;». Мы ввели условие для проверки значения на равенство нулю. Формула сейчас должна выглядеть так: «=ЕСЛИ(СУММПРОИЗВ((БД!$C$2:$C$65536=B$1)* (БД!$D$2:$D$65536=$A2)*(БД!$F$2:$F$65536)*(БД!$G$2:$G$65536)=0;)» (изменения выделены полужирным). Из режима редактирования пока не выходим — формула еще не готова. Теперь мы должны ввести второй параметр функции «ЕСЛИ()». То есть указать, что нужно делать, когда выражение будет равно «0». Мы решили, что в этом случае нужно вернуть пустую строку.

9. Вводим текст «"";». Это и будет второй параметр функции «ЕСЛИ()». Переходим к третьему параметру. Здесь все просто. В качестве этого параметра будет исходное выражение для определения объема закупок в ячейке «B3». А такое выражение у нас есть, и находится оно в буфере обмена. Так что дальнейшие наши действия четко определены.

10. Не покидая режим редактирования формулы, нажимаем «Ctrl+V». Выражение в «B3» станет таким: «=ЕСЛИ(СУММПРОИЗВ((БД!$C$2:$C$65536=B$1)*(БД!$D$2:$D$65536=$A10)* (БД!$F$2:$F$65536)*(БД!$G$2:$G$65536))=0;"";СУММПРОИЗВ((БД!$C$2:$C$65536=B$1)*(БД!$D$2:$D$65536=$A10)*(БД!$F$2:$F$65536)*(БД!$G$2:$G$65536)))» (изменения показаны полужирным).

11. Нажимаем «Enter» (завершаем редактирование формулы).

12. Копируем содержимое ячейки «B3» на всю таблицу. Результат показан на рис. 3. Как и следовало ожидать, нулевых значений в отчете нет. Вместо них вставлен символ «""» (пустая строка).

Важно! Такой прием нужно применять осторожно. После замены нулей на пустую строку некоторые функции Excel могут работать неправильно.

Один из примеров такой ситуации — функция «СЧЕТ()». Напомню, что она позволяет определить количество чисел в заданном блоке ячеек рабочего листа. При этом пустые ячейки функция не считает. Вернемся к отчету на рис. 2, в котором есть нулевые значения. В этой таблице результат работы формулы «=СЧЁТ(B3:D3)» будет равен «3». Для отчета на рис. 3 та же формула вернет значение «1».

Преимущество описанного подхода: при помощи формул можно выборочно скрыть нулевые значения в любой(!) группе ячеек рабочего листа. Кроме того, вместо пустой строки можно использовать пробел, дефис, комбинации «-"-»,«Х» — все что угодно.

В качестве недостатка я бы указал на необходимость корректировать формулы, которые могут оказаться довольно большими. Хотя на самом деле никакой проблемы в этом нет. Кроме того, замена нулей пустыми строками может привести к неправильной работе некоторых функцией MS Excel.

 


Скрываем нулевые значения при помощи параметров Excel

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

Чтобы скрыть нулевые значения в программе Excel 2010, делаем так:

1. Открываем документ.

2. Вызываем меню «Файл → Параметры». Откроется окно «Параметры Excel», как на рис. 4.

3. Переходим в раздел «Дополнительно».

4. В группе «Указать параметры для следующего листа» отключаем флажок «Показывать нули в ячейках, которые содержат нулевые значения» (рис. 4).

5. В окне «Параметры Excel» нажимаем «ОК».

Важно! Параметр отображения нулевых значений является свойством листа документа. То есть он действует на все ячейки рабочего листа MS Excel. Этот параметр Excel сохраняет вместе с рабочей книгой.

Чтобы изменить параметр отображения нулевых значений в программе Excel 2003, делаем так:

1. Открываем документ.

2. Вызываем меню «Сервис → Параметры…». Откроется одноименное окно «Параметры», как на рис. 5.

3. Переходим на закладку «Вид».

4. В группе «Параметры окна» отключаем флажок «нулевые значения» (рис. 5).

5. В окне «Параметры» нажимаем «ОК». Программа Excel скроет все нулевые значения на текущем листе.

Преимущество описанного подхода:

— параметр отображения нулевых значений просто изменить;

— изменение параметра действует сразу на все ячейки рабочего листа MS Excel.

Кстати, последний пункт из перечня преимуществ в некоторых ситуациях может оказаться недостатком. Я имею в виду, что параметры убирают сразу все нулевые значения на рабочем листе. Формулы в этом плане позволяют работать более избирательно.

 

 

Скрываем нулевые значения при помощи автофильтра

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

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

Посмотрим, как это выглядит практически для отчета, изображенного на рис. 2.

Применительно к Excel 2010 делаем так:

1. Открываем документ. Переходим в ячейку «F1» и вводим заголовок рабочей колонки (я назвал ее «Пр»).

2. Форматируем ячейку, как показано на рис. 6.

3. В ячейку «F3» вводим формулу «=СУММ(B3:E3)». В нашем случае такая формула покажет строки, где есть только нулевые значения.

4. Копируем формулу вниз на всю высоту таблицы.

5. Выделяем все колонки документа (на рис. 6 это столбцы «A:F»).

6. Вызываем меню «Главная», щелкаем на иконке «Сортировка и фильтр» (она расположена в группе «Редактирование»). Откроется меню из шести пунктов, как на рис. 7.

7. Из этого меню выбираем «Фильтр». В области заголовков таблицы появятся значки выбора.

8. Щелкаем на таком значке в поле «Пр». Откроется меню, как на рис. 8.

9. В нем последовательно выбираем «Числовые фильтры», затем «не равно…». Откроется окно «Пользовательский автофильтр», как на рис. 9.

10. Здесь в область значения вводим «0», как показано на рис. 9.

11. В окне «Пользовательский автофильтр» нажимаем «ОК». Excel скроет строки документа, полностью состоящие из нулевых значений.

В программе Excel 2003 описанная процедура выглядит так:

1. Открываем документ.

2. Выделяем все столбцы документа (на рис. 6 — это колонки «A:F»).

3. Вызываем меню «Данные → Фильтр → Автофильтр». В области заголовков таблицы появятся значки выбора.

4. Щелкаем на значке в поле «Пр». Откроется меню, как на рис. 10.

5. Из этого меню выбираем вариант «(Условие…)». Откроется окно «Пользовательский автофильтр», как на рис. 9.

6. Щелкаем на значке выпадающего списка с названием «Пр».

7. Из предложенных вариантов выбираем «не равно» (рис. 10).

8. В область значения вводим «0», как показано на рис. 9.

9. В окне «Пользовательский автофильтр» нажимаем «ОК». Строки с нулевыми значениями станут на экране не видны.

Преимущества работы с автофильтром: можно скрыть целые строки документа, где стоят только нулевые значения.

Недостаток применения автофильтра:

— нельзя выборочно скрывать нули в произвольной группе ячеек таблицы;

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

На сегодня все. Надеюсь, что материал этой статьи поможет вам быстро справиться с проблемой отображения нулевых значений в ваших документах. Файл с примерами из этой статьи вы можете скачать на сайте редакции. До новых встреч и удачной работы!

 

Жду ваших вопросов, замечаний и предложений на bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum.

env_icon Отримуйте новини поштою!

Ми будемо надсилати вам найсвіжіші матеріали кожного тижня

Для цього вкажіть своє ім'я та електронну скриньку

Помітили помилку? Виділіть її та натисніть Ctrl+Enter, щоб повідомити нас про це



коментарі
(2)
0mega
20.09.2015
Внесу и я свои "5 копеек" . выделяем "больные столбцы" - правая мышка - формат - число - пользовательский формат 0;-0;;@ Подробиці: https://buhgalter.com.ua/articles/other/438022/
Людмила
27.08.2016
Дякую!
Залишити коментар:
pw-image
Читайте сайт за ціною чашки кави
Платне користування buhgalter.com.ua передбачає:
  • доступ до новин, статей, довідників
  • безлімітні завантаження бланків
  • відсутність реклами.
Передплатники «Бюджетної бухгалтерії», «Оплата праці» та користувачі «FIT-Бюджет» отримують безкоштовний доступ до порталу бонусом до передплати.
Передплата подовжується автоматично щомісяця, але відмовитися можна у будь-який момент. Я погоджуюсь з користувацькими умовами
30
грн/
місяць
Вже передплатили? Увійти
Передплата подовжується автоматично щомісяця, але відмовитися можна у будь-який момент. Я погоджуюсь з користувацькими умовами
ic-acc-pay
×

 

Договір публічної оферти

Угода

про умови використання інформаційних ресурсів сайту

Ця Угода укладена між ТОВ «НВП «ФАКТОР» (далі - Компанія) і будь-яким Користувачем (далі загально названі - Сторони) веб-сайту (-ів) buhgalter.com.ua і / або budget.factor.ua, далі іменуються - Сайт.

Загальні положення

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

Ця угода (Договір) носить характер публічної оферти, тобто є еквівалентом "усної угоди" і відповідно до чинного законодавства України має належну юридичну силу.

Сайт є об'єктом авторського права і як складного твору, а також "компіляції даних" ("бази даних") охороняється на території України, відповідно до Закону України "Про авторське право і суміжні права".

Всі авторські майнові права на Сайт належать Компанії. Позначення «buhgalter», «Factor» є знаком для товарів і послуг (торговою маркою) і не може використовуватися Користувачем без отримання відповідної ліцензії.

Компанія докладає всіх зусиль щодо забезпечення достовірності і актуальності інформації, представленої на Сайті. Однак Компанія не бере на себе фінансової та іншої відповідальності за результати застосування цієї інформації.

Підписка - повний доступ до всіх матеріалів, розміщених на Сайті. Підписка надається за певну плату на певний строк відповідно до умов відповідного тарифу Підписки.

Автоподовження підписки - сервіс автоматичного продовження підписки.

 

 

1. Прийняття Угоди

 

1.1. Для того, щоб отримати право доступу до інформаційних ресурсів Сайту, Користувачеві необхідно спочатку висловити свою згоду з цією угодою. Користувач не має санкціонованого доступу до інформаційних ресурсів Сайту, якщо він не прийняв або не згоден з умовами цієї угоди.

1.2 Згідно з умовами Договору Компанія надає Користувачам Передплату на платній основі з метою перегляду користувачами окремих матеріалів, розміщених на Сайті. Категорії платної інформації визначаються Товариством самостійно.

1.3. За згодою Користувача з умовами цієї угоди, Користувачеві надається доступ до інформаційних ресурсів Сайту. Інформаційні ресурси надаються «як є» і не припускають наступних доопрацювань.

1.4. Користувач має право припинити користуватися Сайтом в будь-який момент, без спеціального повідомлення Компанії про припинення користування Сайтом.

1.5. Для отримання доступу до ресурсів від Користувача може знадобитися надання інформації про себе (наприклад, відомості про особу, відомості про юридичну особу та інші контактні дані) з метою продовження користування Користувачем послугами Компанії.

1.6. Беручи до уваги безперервне впровадження нових розробок, Користувач визнає і погоджується з тим, що Компанія має право на власний розсуд припинити (тимчасово або остаточно) надання доступу до ресурсів Сайту (або будь-яких окремих функцій Сайту) всім користувачам взагалі або Користувачеві зокрема, без попереднього повідомлення.

1.7. Компанія на постійній основі впроваджує нові розробки для того, щоб надавати найкращі можливі послуги своїм користувачам. Користувач визнає і погоджується з тим, що форма і характер послуг (дизайн сайту, обсяг наданої інформації, тематика і ін.) можуть час від часу змінюватися без попереднього повідомлення Користувача.

 

 

2. Відповідальність сторін

 

2.1. Матеріали Сайту видаються без будь-яких гарантій, неявних або інших. Компанія не несе відповідальність за повноту і точність інформації, що міститься в матеріалах сайту, відповідність її вимогам Користувача.

2.2. Всі ризики, що виникають при використанні матеріалів (ресурсів) Сайту, несе Користувач. Компанія ні за яких умов не несе відповідальності за будь-які понесені Користувачем збитки, у чому б вони не виражалися, в тому числі, в разі якщо компанія була повідомлена про можливість заподіяння збитків.

2.3. Ризик настання негативних наслідків в результаті використання рекомендацій, думок і тверджень, що містяться на Сайті, в повному обсязі несе Користувач. Компанія не гарантує і не підтверджує точність і достовірність, рекомендацій, думок і тверджень, що містяться на Сайті.

2.4. Компанія не несе ніякої відповідальності за будь-які помилки, неточності, втрати часу, порушення в роботі обладнання і ліній комунікацій, проникнення комп'ютерних вірусів, втрати і зміни даних, виникнення дефектів в роботі програм. які виникли, незалежно від причин, в результаті використання Сайту.

2.5. У разі здійснення Користувачем дій, передбачених ст. 50 Закону України «Про авторське право і суміжні права» (порушення авторського права і суміжних прав), Компанія залишає за собою право, без попереднього повідомлення Користувача, за своїм вибором здійснювати дії щодо захисту немайнових і майнових авторських прав та суміжних прав в порядку, встановленому адміністративним, цивільним і кримінальним законодавством.

 

3. Порядок і умови оформлення Передплати

 

3.1 Для оформлення Передплати Користувачеві необхідно:

3.1.1 Авторизуватись або зареєструватися на сайті buhgalter.com.ua шляхом заповнення всіх необхідних полів реєстраційної форми

3.1.2 Уважно ознайомитися з умовами договору і прийняти його,

3.1.3 Ввести необхідні для оплати дані, якщо того вимагає Платіжна система; підтвердити оплату і оплатити.

3.2 Оплата доступу до сайту buhgalter.com.ua проводиться Користувачем на умовах 100% передплати.

3.3 Компанія попереджає користувачів, а Користувачі розуміють і погоджуються з тим, що Платіжна система може стягувати понад вартості послуг будь-якого роду комісії та збори, включаючи комісії мобільних операторів (за їх наявності), надаючи свої послуги тільки в разі, якщо такі комісії і збори будуть оплачені користувачем. Інформацію про такі комісії і збори (за їх наявності) Користувач самостійно може дізнатися в Платіжній системі. Комісії та збори не повертаються і не відшкодовуються Компанією. Користувач, який скористався послугами платіжної системи, вважається попередженим і згодним з їх умовами.

3.4 Компанія має право в односторонньому порядку і без попереднього повідомлення Користувача змінювати умови цього договору, наповнення пакетів Підписка, розміри тарифів і т.п. шляхом розміщення відповідного документа в новій редакції на сайті.

3.5 Зміни умов цього Договору набирають чинності з моменту опублікування на сайті buhgalter.com.ua

3.6 Зміни, що стосуються наповнення пакетів Підписка, розмірів тарифів застосовуються тільки для підписок, придбаних після такої зміни.

3.7 Відмова від послуг після оплати неможлива.

3.8 Користувач не має права:

3.8.1 реєструватися як інша особа; вводити в оману інших користувачів щодо своєї особи, використовуючи реєстраційні дані іншої людини; навмисне спотворювати уявлення про самого себе, свій вік або свої відносини з іншими особами або іншими організаціями; передавати реєстраційні дані, логін і пароль для доступу до системи іншим особам;

 

4. Автоподовження підписки

 

4.1 Учасники оплачують доступ до сайту buhgalter.com.ua щомісяця, надають згоду на послуги автоматичного регулярного продовження Підписки на новий термін (автоподовження Підписки). Після активації даної послуги оплата нового терміну Підписки здійснюється шляхом автоматичного безакцептного списання коштів з рахунку користувача. Послуга автоподовження Підписки надається до тих пір, поки Користувач не повідомить про її скасування в особистому кабінеті користувача, заповнивши необхідну форму.

4.2 Послуга вважається скасованою з наступного оплатного періоду.

4.3 Послуга активується при здійсненні першої або чергової оплати Підписки, що означає згоду користувача з тим, що після закінчення оплаченого строку дії Передплати Договір автоматично пролонгується на тих же умовах на той же термін з автоматичним списанням вартості Передплати з рахунку Користувача

4.4 У разі недостатності коштів на рахунку користувача або неможливості проведення платежу Підписка на новий термін не надається, а послуга «автоподовження Підписки» відключається, до моменту появи коштів на рахунку користувача або заміни користувачем рахунку в особистому кабінеті).

4.5 При зміні Товариством тарифів на підписку автоподовження Підписка на черговий новий термін, наступний за періодом, протягом якого відбулася така зміна буде здійснено за новим тарифом.

 

5. Припинення відносин з Компанією. зміна угоди

 

5.1. Компанія і Користувач домовилися, що компанія має право припинити підтримувати Сайт (надавати доступ / надавати послуги) в будь-який час на власний розсуд.

5.2. Компанія залишає за собою право припинити доступ до Сайту, якщо: доступ до Сайту втрачений через відсутність зв'язку на маршруті від Користувача до Сайту; Користувачеві відмовлено в доступі до Сайту внаслідок порушення ним «Інтрукціі користування сайтом» (http://budget.factor.ua/faq.php) або перевищення обсягу прав на використання Сайту.

5.3. Компанія залишає за собою право за своїм вибором без попереднього повідомлення та / або роз'яснення причин: змінювати, виправляти, обмежувати, припиняти чи зупиняти дію Сайту і будь-яких його розділів (матеріалів, ресурсів, сервісів); відмовляти Користувачеві в доступі до користування Сайтом і / або будь-якого його розділу. Задоволення / розгляд потреб Користувача при використанні Сайту не є обов'язком Компанії.

 

6. Заключні положення

 

6.1. Сайт може містити посилання на інші Інтернет - сайти. Дані сайти не перебувають під контролем Компанії і Компанія не несе відповідальності за зміст даних сайтів та / або зміст сайтів, посилання на які знаходяться на даних сайтах. Посилання на інші сайти включені в Сайт тільки для зручності користувачів і не означають згоду Компанії та його посадових осіб з вмістом даних сайтів. Інші Інтернет - сайти можуть містити посилання на Сайт без спеціального дозволу Компанії.

6.2. У разі незгоди Користувача з будь-яким з умов цієї угоди він зобов'язаний негайно припинити використання Сайту і / або будь-якого з його розділів.

6.3. У разі якщо компетентними органами / особами частина цієї угоди буде визнана недійсною, такою, що суперечить законодавству і не підлягає застосуванню, це не тягне неможливість застосування інших положень цієї угоди. Ця угода діє повною мірою в рамках, встановлених чинним законодавством.

6.4. До відносин в рамках цієї Угоди застосовується право України. Користувач і Компанія домовилися підкорятися при виникненні будь-яких спорів, що виникають у зв'язку зі справжньою угодою (використанням Сайту), виключно юрисдикції судів України.

Для того, чтоб распечатать текст необходимо авторизоваться или зарегистрироваться
Закрити
icon-block
Заважає реклама?
Ви маєте можливість її відключити всього за 30 грн