Автоматический график с помощью сводной таблицы
Вставка
- Опубліковано 4 гру 2022
- 👉Курсы по Excel - andreysukhov.ru
В предыдущем видео я рассмотрел процесс создания графика ревизий на основе исходной таблицы. Однако задача оказалась несколько сложнее и предложенное мною решение на основе условного форматирования в данном случае не подошло. В этом видео покажу еще один вариант создания автоматического графика на основе сводной таблицы.
Видео по теме:
► График с помощью условного форматирования: • Автоматический график ...
► Условное форматирование: • Условное форматировани...
► Числовой формат: • Пользовательские форма...
Дополнительные материалы:
➤ Скачать файл с примером: disk.yandex.ru/i/BXj4U4FPVdBZEw
➤ Текстовая версия: dzen.ru/a/Y43oa-IyJE3Tk2FV
★ Авторские книги и курсы: andreysukhov.ru
★ Скачать курс по Excel: andreysukhov.ru/education/exc...
★ Телеграм: t.me/excelmate
★ Дзен: zen.yandex.ru/id/5c4d8a530aae...
★ Группа ВК: public212583018
#Excel
У сводной таблицы есть один серьезный минус (кстати, озвученный в ролике) - отсутствие автообновления. К тому же для данного примера сводная таблица явно избыточна. Задачу можно решить проще, с использованием формулы массива. Для данного примера:
{=ЕСЛИОШИБКА(ПОИСКПОЗ(1;($E4=Ревизии[Подразделение])*(F$3=Ревизии[Дата проведения]);0);"")}
Формула вставляется в ячейку F4, а затем растягивается на нужный диапазон.
Уникальные значения в столбце E также можно получить формулой:
=ЕСЛИ(СЧЁТЕСЛИ($A$4:Ревизии[@Подразделение];Ревизии[@Подразделение])=1;Ревизии[@Подразделение];"")
UPD.
Для условного форматирования в данном случае указывается правило:
=F4""
Да, была идея решить задачу с помощью формулы массива, но я бы не назвал такой подход более простым. Все же многим тяжело дается понимание логики формул массива. Предложенный вами способ скорее всего работать не будет. Да и автоматизация создания списка уникальных значений - задача не из примитивных и простая связка функций ЕСЛИ и СЧЕТЕСЛИ вряд ли ее решит. Здесь либо применять функцию УНИК, доступную далеко не всем, либо заморачиваться с более сложными вычислениями. Подходов много, но ни одного простого.
Как доберусь до Экселя обдумаю предложенное вами решение, но сходу оно мне не кажется рабочим.
Добрался до Экселя и теперь могу ответить по существу.
Предложенный вами вариант формулы массива работать не будет. Вот намного более простое решение - =СУММ(($A$4:$A$13=$E4)*($B$4:$B$13=F$3)). В нем я указал конкретные диапазоны таблицы, но это сути не меняет. В итоге получаем таблицу, заполненную нулями и единицами. Далее можно либо скрыть нули (например, функция ЕСЛИ), либо на все применить условное форматирование, скрывая нули и раскрашивая ячейки с единицами.
Что же касается получения уникальных значений, то и здесь предложенная вами формула работать не будет. Вы не учитываете, что ваша формула в итоге выдаст массив значений, в котором дубликаты будут считаться функцией СЧЕТЕСЛИ, а значит их будет больше двух и функция ЕСЛИ их не учтет при формировании списка. На выходе вы получите не список всех уникальных значений, а только тех из них, у которых нет дубликатов.
@@andreysukhov мне скинуть файл, где формула работает?)
Не зря же указал, что формула задаётся для одной ячейки, а потом растягивается на диапазон.
Связка ЕСЛИ+СЧЕТЕСЛИ вполне рабочая. Не без недостатков, конечно, но это то, что родилось навскидку.
UPD. Ээ... А разве список без дубликатов не является, по сути, уникальным?)
@@xenoz1626 скиньте. Скорее всего вы что то не дописали, так как в представленном вами виде ни первая, ни вторая формулы не работают.
@@xenoz1626 Список без дубликатов по сути является уникальным, но не содержит значения, имеющие дубликаты:)
Спасибо за труд!🤝
Как то что мне нужно! Андрей, благодарю за вашу помощь в изучении Эксель! Очень классно, спасибо!
Второй вариант получился отработанным, легким в исполнении и эффективным. Актуальный ролик, спасибо.
Каждое Ваше видео про работу в Excel снижает мою самооценку на несколько пунктов ))) спасибо!)
Ну зачем так. Сформулируем иначе немного - поднимает уровень знания Excel на несколько пунктов. Мы все тут учимся.
Отличная работа с использованием сводных таблиц легко и просто , проще некуда
Оригинально. Спасибо.
Познавательно!
Красиво 👏
Добрый день, Андрей! Благодарю за ваши подробные уроки по этим базовым и необходимым программам. С недавнего времени решила пободаться, всё-таки, с excel-ем. Не хочется, чтобы строка в резюме "владею excel" стояла просто так. Дело в том, что пыталась так же поделать упражнения на английском языке (нужно по работе), а интерфейс стоит русский и автозаполнение, видимо, не работает. Например, когда нужно сделать расчет по месяцам, то автоматически заполнить этот столбец не получается. Пыталась через параметры настроить, но безрезультатно. Что делать?
P.S. лицензия для Windows только на один язык (русский).
P.P.S. я совсем зеленый дуб в excel
Здравствуйте. Автозаполнение использует готовые списки. Вы можете создать свой. Вот видео - ua-cam.com/video/nJW2Bn11ToE/v-deo.html
@@andreysukhov спасибо большое!!! Очень помогло! Успехов вам!!!
Андрей, приветствую. А как в экселе сделать, чтобы он сам посчитал данные из нужных ячеек, ища данные в строке, в которой находится ячейка. Т.е, например есть строка с допустим количеством конфет одной марки, и есть еще 3 строки с другими марками, и надо, чтобы он на этих строках искал определенные марки, и считал количество конфет, при условии, что марки могут располагаться в разном порядке, но сами марки не меняются?
Все же предпочитаю видеть документ, а не представлять его:) но если я правильно понял задачу, то обратите внимание на функцию СУММЕСЛИ. Вроде бы отдельного видео по этой функции я не делал. Можем на основе вашей задачи разобрать эту функцию. Если согласны, то перешлите мне документ (можно упрощенный) и описание задачи. admin@pcsecrets.ru
@@andreysukhov Спасибо. Я об этой функции знал, но забыл, пока вы не напомнили о ней. Я давно работал с функцией СУММЕСЛИ, и забыл про нее. Но я всё равно перешлю вам документ для контента.
@@andreysukhov А с проблемой с флешкой вы можете помочь?
У проблемы может быть две причины - программная и аппаратная. Иногда при неправильном извлечении флешки из компьютера повреждается ее файловая система. В результате при подключении флешки или при обращении к ней появляется ошибка. Такая проблема лечится форматированием. Вся информация на устройстве будет потеряна, но и файловая система будет восстановлена. Если же проблема аппаратная, то проще купить новую флешку, так как ремонт "на коленке" вряд ли возможен, а обращаться за этим в сервис нецелесообразно, по причине дороговизны. Уж проще купить новую. Разве что на флешке находится очень важная информация и не жалко денег на попытку ее восстановления.
@@andreysukhov дело в том, что, все время, когда я вставляю флешку в пк, у меня в ней есть ярлык флешки со значком диска, в котором находится моя инфа. Я файлы от туда переношу на флешку, а когда подключаю снова, всё повторяется опять: во флешке ее ярлык со значком диска и в нем файлы мои. Боюсь, как бы файлы не исчезли / не повредились
Слишком сложно, слишком много телодвижений. А пользы от этого мало. 90% пользователей excel не станут так заморачиваться. Программа должна облегчать жизнь, а не усложнять.
Это только пример использования. А главное принцип использования. Очень полезная информация!
99% пользователей Эксель не столкнутся с подобной задачей, а те кто столкнется, будет искать оптимальное решение. В данном подходе не используются сложные формулы, применяется самый обычный функционал Эксель. Поэтому, если честно, то я не понимаю, в чем тут "заморочки". Подобный график создается за минуту, если, конечно, пользователь имеет представление о том, что такое сводная и условное форматирование. Ну а если не имеет, то Эксель вряд ли облегчает ему жизнь:)
Это твои проблемы, что тебе сложно. Учись! Тут ничего сложного нет, просто надо запомнить последовательность действий и всё. Ну и твоя претензия "слишком много телодвижений" вообще необоснована. Не нравится? Сам делай как тебе угодно и покажешь как ты сделал тоже самое, но быстрее
Не вижу ничего сложного, наоборот, теперь на весь месяц всегда готов график. А строить его всего лишь раз.