ЗДРАВСТВУЙТЕ, ДРУЗЬЯ!

НА СВЯЗИ ИЛЬЯ ГРИШИН – АВТОР И ВЕДУЩИЙ ПРОЕКТА «БЮДЖЕТИРОВАНИЕ И УПРАВЛЕНЧЕСКИЙ УЧЁТ В EXCEL».
Фишки Excel - Как сделать, чтобы пустые строки не попадали в диаграмму?

Давненько я не писал ничего про Excel. Как раз сидел и думал – чем бы вас порадовать. И тут мне на ящик приходит очередной вопрос от слушательницы семинара "Бюджетирование и управленческий учёт в Excel".
Здравствуйте, Илья!Если есть такая возможность ответьте на следующий вопрос: При выполнении вертикального анализа баланса мы строим диаграммы, они основываются на таблицах (я и прикрепила к файлу). Для автоматизации процесса построения диаграммы необходимо выбрать из всего перечня статей баланса ТОЛЬКО ТЕ, которые имеют значение больше 0. Иначе попадают в диаграмму и пустые значения, что загромождает её. Через функцию ЕСЛИ решить эту проблему нам не удалось.
Подскажите, что нам делать!!! Не хочется строить диаграммы заново при любом изменении. :)

Заранее спасибо!

Марина Гудыма, ЗАО "Уралмостострой" ф-л "Мостотряд 16"
Я думаю, что вопрос как исключить пустые строки из диаграммы – будет интересен многим нашим читателям. И я с удовольствием на него отвечаю! :)

На самом деле технология простейшая, однако, есть парочка интересных моментов. Но начнём с самого простого.

Предположим, что у нас есть таблица, которая содержит исходные данные. Пускай это будут краткосрочные оборотные активы. Предположим, что данные в эту таблицу мы получаем с других листов с помощью формул (хотя на самом деле это не важно).

Нам надо построить диаграмму, которая наглядно покажет – сколько каких у нас активов. Тут все стандартно. Вставка – диаграмма – выбираем нужный нам тип диаграммы и т.д..

В этом примере для простоты восприятия мы будем вставлять диаграмму на этот же лист, но и на другом листе всё будет работать точно так же. После вставки диаграммы у нас получается вот такая штука:

А теперь давайте попробуем «обнулить» одно из значений таблицы. Например, у нас изменились исходные данные на другом листе, и одна из статей «обнулилась».
В диаграмме появился пустой столбец и это снижает её наглядность. Как теперь его убрать?

Конечно можно построить диаграмму заново. Но каждый раз при изменении того или иного значения «скакать» с листа на лист и перестраивать диаграммы – крайне неудобно.

Самый простой способ автоматизировать процесс – воспользоваться автофильтром. Для этого я выделю строку 2 и активирую автофильтр (данные – фильтр – автофильтр). Теперь я использую автофильтр в ячейке В2 и задам условие. Кстати, условия в автофильтре – штука невероятно полезная, однако мало кто ими пользуется.

В условии я задам, что значение ячейки должно быть больше нуля.
Теперь нажимаем кнопку ОК и видим, что пустая строчка исчезла из таблицы, а пустой столбец – из диаграммы.
Но как построить диаграмму, если в таблице насколько столбцов и нулевые значения везде разные? Пускай наша таблица выглядит так:
Очевидно, что если значение в одном столбце равно нулю, а в другом – нет, данные в диаграмме все равно нужны. Но если значение в двух столбцах равно нулю – данные не нужны. На приведенном ниже рисунке зеленым отмечены строки, которые нужно выводить в диаграмму, а желтым – строка, которая не нужна.
Для начала немного изменим внешний вид диаграммы.
Очевидно, что столбец «Краткосрочные финансовые вложения» в диаграмме нам совершенно ни к чему.

А теперь плохие новости. Автофильтр по нескольким столбцам одновременно не работает. Но есть и хорошие новости – этот недостаток легко обойти.

Для этого мы должны сделать третий столбец (назовём его «Контроль»), значения которого будут реагировать на значение первых двух столбцов. А на него поставим автофильтр.

Для создания третьего столбца воспользуемся функцией «И». Подробно мы разбирать эту функцию не будем, но если вкратце, то работает она так: проверяет несколько условий и возвращает значение «ИСТИНА», если все условия выполнены или «ЛОЖЬ», если выполнены не все условия.

Введём формулу в первую строку.

А теперь протянем формулу вниз. Вот что должно получиться:
Как мы видим – функция «И» вернула значение «ИСТИНА» в строке, где оба значения в столбцах нулевые.

А теперь в ячейке D3 в автофильтре установим условие, что значение не равно «ИСТИНА».

И что мы видим? Лишние строчки исчезли из диаграммы!
Вот такая вот интересная фишка! :) Да. Ещё. Скачать файл с примером, который мы разобрали - вы можете здесь.
И напоследок.

В своих статьях я показываю лишь мизерную часть возможностей Excel. Однако, применив только этот инструмент в своей работе, вы уже сэкономите достаточно времени и сил, чтобы увидеть результат. А если вы хотите сделать большой рывок и вывести свою работу на новый, осознанный уровень, хотите научится собирать любые управленческие отчёты любой сложности буквально в 2 клика, то предлагаю вашему вниманию мой флагманский видеокурс "Бюджетирование и управленческий учёт в Excel".

Посмотреть подробное описание тренинга можно на сайте

Задать интересующие вопросы можно по телефону 8 800 201-49-35.