MS, Libreoffice & Google docs

Есть очень немного вещей от которых у меня пригорает. И Excel с VBA уверенно занимают места в ТОПе вещей из-за которых у меня пригорает особо.

Я не буду хейтить сами продукты, у меня есть ровно один вопрос - нахера вы его тыкаете везде?

Второй вопрос, вытекающий из первого, нахера вы его настолько глубоко изучаете?

А сейчас я "на пальцах" объясню смысл этих вопросов. Для начала такая вот эмпирическая (то есть основанная на личном опыте) диаграмма.

Это сравнение производительности труда по времени в зависимости от используемой технологии.

Безусловно, что для неподготовленного человека разбираться в СУБД и SQL сложнее, чем в Excel, но изучая Excel очень быстро достигаешь потолка технологии и останавливаешься, в то время как возможности почти любой СУБД несопоставимо больше.

Не скажу, что задолбался, но меня постоянно раздражает то, что "гуру Excel" ведут себя как вегетарианцы. "Самый лучший продукт" и прочие дифирамбы. И при этом настойчиво пытаются впарить... Я, честно говоря, так и не понял что. Ну давайте немного "попаримся" и сравним Excel и СУБД.

Начнём с плюсов Excel - он "в одно лицо" может данные хранить, отображать и показывать графики.

Второй плюс... Подскажите мне в комментариях, ибо лично я не знаю.

Теперь поехали по минусам. На самом деле их очень много, но чтобы не грузить, ТОП-3 на мой взгляд:

Первый - сказочная ресурсоёмкость. Из-за перечисленных плюсов программа вынуждена выполнять очень много не нужных для решения задачи операций. Можно было бы сказать, что "программа же делает, а не человек", но тут есть два больших НО:

1. Для решения задач обработки +/- существенных объёмов данных системник под Excel будет стоить раза в два дороже. Если в компании 20 - 25 человек работают с большими таблицами Excel, то минимум миллион принесен в жертву Excel. Это бы меня не волновало, если бы не тот факт, что компания этот миллион будет закладывать в стоимость продукции или торговую наценку, а значит в т.ч. и я плачу за этот бред.

2. Производительность труда при работе с Excel на порядки ниже, чем с СУБД. Когда мне на тех или иных работах передавали формирование отчетов в БД, то на их составление стало уходить до 10 - 12 раз меньше времени, при этом исчезли кучи багов в этих самых отчетах. Это абсолютно бессмысленные затраты на зарплаты сотрудников. За которые в т.ч. и я опять же вынужден платить.

Второй - Excel от слова совсем не поддерживает реляционную модель. До 90% работы с таблицами Excel (кроме ввода данных) это выполнение руками работы, которую делает SQL из коробки. Нужно раз заморочиться с запросом и забыть тему. Так что офисный планктон можно называть "Человек-СУБД" ;-)

Третье - очень серьезные ограничения на объём обрабатываемых данных, как по количеству листов(таблиц), так и строк. Да и по формулам не всё радужно. СУБД переваривает на том же железе на порядки больше и гораздо быстрее.

Ну и пара моментов, которые просто бесят:

1. Excel зачастую считает, что знает лучше пользователя что он имел ввиду. Ряд длинных числовых реквизитов внезапно может обрасти нулями в конце (с потерей данных);

2. Excel и "срочно" на более-менее приличных объёмах данных, это взаимоисключающие вещи. И если вдруг возникает ситуация, когда надо срочно или потеряем деньги (что бывает крайне редко, но таки бывает) при использовании Excel остается только смириться с потерей денег.

Так вот возвращаясь к вопросу в начале поста - ради чего вы учите "фишки" Excel и тратите на него время? Чтобы потом неделю делать то, что любая СУБД за минуту сделает?

Про VBA скажу коротко - научиться программировать на VBA и на Python (простые задачи) по времени и силам примерно одинаково. Возможности VBA и Python (даже в рамках примитивных задач) вообще несопоставимые. Это как детский пластиковый совочек и карьерный экскаватор. Опять вопрос - зачем?

Ну и, пожалуйста, не надо писать, что Excel это что-то серьезное. Для своих задач, а именно - прикинуть хрен к носу на паре десятков тысяч строк максимум или оформить табличку со сравнительно небольшим объёмом информации - топчик, спорить не буду. Но он топчик ТОЛЬКО для таких задач...

И возвращаясь к вопросам выше, гуру Excel, ответьте мне - зачем вы тратите своё время и деньги работодателя на возню с детской игрушкой? В Интернете есть 100500 курсов по СУБД, тот же Excel/LibreCalc более чем умеют в импорт. Или просто нравится играться, а не работать?

P.S. Сразу отвечу на вопрос про формулы - тот же PostgreSQL умеет работать с Python, внешние модули так же никто не отменял. Формулы Excel и возможности ЯП опять же несопоставимы и сравнение опять не в пользу Excel.

P.P.S. Сегодня делал отчет. 6 таблиц, от 200к до 35М записей, связка построчно, причём к "центральной" таблице на 6,5 млн. строк привязывались остальные 5. 2 таблицы привязывались по нескольким ключам. Аналог в Excel - ВПР по нескольким полям (т.е. по агрегированному полю). Время формирования отчета в СУБД - минута, данные теряют актуальность за час, время работы над отчетом от получения ТЗ до готового воспроизводимого "по кнопке" результата - 4 часа.

Я бы написал "Слабо в Excel повторить?", но не буду. Потому что насмотрелся на таблицы в сотни мегабайт которые открываются десятки минут и только на компах с 8+ ядрами, 16 Гб ОЗУ и шустрыми SSD. И которые после аплоада данных в PgSQL спокойно формируются за секунды (максимум минуты) на древних компах с характеристиками Raspberry PI.

Друзья, всем привет. Сегодня хочу рассказать вам про несколько полезных и интересных (с моей скромной и субъективной точки зрения) трюков при работе со сводными таблицами. Здесь не будет подробного разбора про работу в сводных таблицах: что это, для чего и почему. Материал предназначен для тех, кто уже хоть как-то знаком с этим прекрасным инструментом и знает, что это за зверь такой. Если же вы ещё не работали со сводными таблицами, при этом проводите много времени в Excel и строите разного рода отчёты, аналитику, то я вам настоятельно рекомендую как можно скорее освоить этот поистине чудесный инструмент. Поверьте, вы откроете для себя абсолютно новый мир :)

1 - Фильтры для полей сводной, которые по умолчанию фильтровать нельзя.

Поля, которые находятся в области значений, нельзя фильтровать привычным для нас образом. Там просто нет фильтров, и установить их обычным способом нельзя, команда не активна:

Но если нельзя, но очень хочется, то можно.

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

2 - Изменение порядка элементов в списке.

Вообще, есть несколько способов менять порядок элементов в сводной. Я хочу рассказать вам про наиболее удобный. Предположим, мы хотим, чтобы список менеджеров начинался с Чайниковой:

Решение: выбираем ячейку с Баранкиным и вероломно начинаем печатать фамилию Чайниковой. Целиком печатать не нужно, программа сама предложит готовый вариант. Нажимаем Enter. Вуаля! Теперь Чайникова на вершине нашего списка:

Ещё один способ: это обычное копирование и вставка. Выбираем ячейку с Чайниковой, копируем её. Далее выбираем ячейку с Баранкиным (место, куда хотим переместить наше значение), и вставляем.

3 - Использование собственных списков для сортировки.

Прошлый способ хорош, если нужно сделать это однократно. Но если у нас уже есть какой-то устоявшийся порядок, он вот совсем не по алфавиту, а бездушный Excel всегда всё сортирует именно по алфавиту?

Решение: многие знают, что если мы напишем в ячейке "январь" ("февраль", "март" и т.д.), потом протащим ячейку за правый нижний угол вниз, то всемогущий Excel автоматически заполнит следующие ячейки месяцами по порядку. Но не только лишь все знают, что такую штуку можно сделать и по своему списку.
Шаг 1. Создаём свой список (с блэкджеком и порядком). Где-нибудь на листе Excel создаём список в том порядке, в котором нам нужно. Далее заходим в Файл - Параметры - Дополнительно - Изменить списки:

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

Шаг 2. Для сортировки данных используем наш список. Теперь можно применить сортировку от А до Я в столбце с менеджерами. И вот ведь неожиданность, сортировка будет не по алфавиту, а согласно нашему списку:

К сожалению, данный приём имеет ограничения в части обновления списка. Если добавится новый сотрудник, то при обновлении сводной сотрудник будет в самом конце списка независимо от того, какая у него там первая буква фамилии. Дальше либо добавлять его в список, либо вручную перетаскивать в нужное место.
Если нужно будет вернуть стандартную православную сортировку по алфавиту для данного списка, необходимо раскрыть значок фильтра, выбрать Дополнительные параметры сортировки - по возрастанию - Дополнительно - снять галочку с "Автоматическая сортировка..." - в поле ниже выбрать "Без вычислений":

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

4 - Отображение пустых строк.

При разбивке какого-то показателя на несколько групп/категорий в сводных таблицах не отображаются данные, которых нет в источнике. В целом, это логично. Но согласитесь, что если мы, к примеру, хотим проанализировать показатель по сотрудникам по месяцам, то вот такая картина будет только нас запутывать:

Месяцы идут по порядку, да, но у кого-то пропущен июль, у кого-то февраль и октябрь.

Решение - щёлкаем правой кнопкой мыши по любой ячейке того поля, в котором хотим отображать ВСЕ элементы (в том числе и пустые) - далее Параметры поля... - вкладка Разметка и печать - ставим галочку Отображать пустые элементы.

У этого приёма есть один побочный эффект - если речь идёт про работу с датами, то отображаются даты до самого раннего числа и после него (видно на гифке). Избавиться от этого довольно просто: нужно убрать их через фильтр, сняв галочки.

5 - Топ-N сотрудников/месяцев/товаров и так далее.

Вообще, отображение топ-3/5/10 сотрудников или любого другого - это не привилегия фильтра в сводной таблице. Работая с фильтрами в обычных таблицах, у нас есть точно такая же возможность. Но про эту возможность, исходя из моего хоть и небогатого, но всё же опыта, мало кто знает. То ли нафиг никому не нужно, то ли просто пользователи не знают. Как по мне, вещь полезная. Особенно с учётом того, что в сводной таблице фильтр будет обновляться вместе с внесением изменений в источник (и обновления самой сводной, конечно же), чего в обычных таблицах, увы, нет.

Решение - всё просто. Раскрываем фильтр по тому полю сводной, по которому хотим выделить топ-N чего-нибудь - далее Фильтр по значению - Первые 10 - указываем нужные нам настройки.

Правда, без побочных эффектов тут, к сожалению, тоже не обошлось. Если у какого-то сотрудника не будет хватать данных для того, чтобы отобразить топ-3, то по этому сотруднику (или по тому, по чему вы ищете топ-N) будут вообще все данные. Поведение более чем странное, но как есть :(

6 - Повтор подписей и промежуточные итоги для отдельных полей, а не для всей таблицы.

Когда у нас с вами в сводной таблице в области строк больше двух полей, то сразу возникает вопрос про промежуточные итоги. И если идти стандартным путём, то есть через вкладку Конструктор - группа Макет - Промежуточные итоги, то там мы можем играться с промежуточными итогами для всей таблицы, а не для конкретного поля. Порой, это неудобно. Как в примере ниже:

Я бы хотел оставить промежуточный итог по сотруднику, а вот по месяцу убрать.
И сразу второй момент. Когда меняем макет сводной на табличный или форму структуры, то возникает вопрос с повторением подписей элементов, чтобы не было пустых ячеек. Если перейти на вкладку Конструктор - группа Макет - раскрыть Макет отчёта и выбрать Повторять все подписи элементов, то это сработает для всей таблицы. А я, например, хочу только по месяцам подписи.

Решение - великая и могучая правая кнопка мыши. Для того, чтобы добавить/убрать промежуточные итоги у определённого поля сводной таблицы, щёлкаем правой кнопкой мыши по любой ячейке этого поля и в контекстном меню находим Промежуточный итог...
Что касается подписей элементов, то щёлкаем правой кнопкой мыши по тому полю, где хотим/не хотим повторять подписи - далее Параметры поля... - на вкладке Разметка и печать находим галочку Повторять все подписи элементов.

7 - Объединение ячеек и выравнивание подписи по центру.

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

Решение - для объединения ячеек и выравнивания подписи по центру щёлкаем по любой ячейке сводной таблицы правой кнопкой мыши (данная настройка будет работать для всех полей сводной, для какого-то конкретного поля настроить возможности нет) - в контекстном меню находим Параметры сводной таблицы - вкладка Макет и формат - находим галочку Объединить и выровнять по центру ячейки с подписями.
А чтобы убрать плюсы/минусы, нужно выбрать любую ячейку сводной - далее вкладка Анализ сводной таблицы (в предыдущих версиях просто Анализ) - группа Показать - иконка Кнопки.

Заключение. На этом, пожалуй, всё. Вообще, всяких разных интересных штук при работе со сводной таблицей огромное множество. Про все и не напишешь. Делитесь в комментариях своими интересными приёмами. Думаю, многим будет интересно и полезно узнать что-то новое (мне так точно). Спасибо всем огромное, кто потратил своё драгоценное время и внимание на прочтение данного поста. Надеюсь, что-то из описанного было для вас полезным и вы сможете использовать это при работе со сводными таблицами.