таблица для тренировки сводных таблиц
Excel для финансиста
Поиск на сайте
Глава 7. Сводные таблицы
Cводные таблицы – мощный и очень удобный инструмент Excel для анализа больших объёмов данных. С помощью таблиц можно легко получать сводные отчёты, видоизменяя и настраивая их несколькими щелчками мыши. Рассмотрим сразу на практическом примере.
Скачайте файл svodnie-tablici. На листе данные этого файла находятся двести записей о продажах товаров (на практике число анализируемых записей обычно на один-два порядка больше). Каждая запись представляет собой строчку в таблице и содержит информацию:
Относительно этих данных может возникнуть множество вопросов:
На все эти вопросы помогают ответить сводные таблицы.
Обратите внимание на вспомогательные столбцы Год, Месяц, День листа данные в файле с примером. В этих столбцах хранятся соответствующие значения даты, которые будут нужны для некоторых видов отчётов.
Создание сводной таблицы
Перед тем, как сделать сводную таблицу, нужно задать данные, которые будут в ней отражены. В нашем случае – вся таблица. Проще всего выделить таблицу, выбрав любую ячейку в ней и нажав Ctrl-A. Теперь в меню Вставка нажмите кнопку Сводная таблица, в открывшемся окне проверьте выбранный диапазон данных, выберите, что создание сводной таблицы произойдёт на новом листе, ОК.
Поля сводной таблицы
На новом листе слева появилась заготовка сводной таблицы, справа окно со списком полей и четырьмя окошками: фильтр отчёта, названия столбцов, названия строк, суммарные значения.
Напомним, сводная таблица должна давать ответы на поставленные вопросы. Например, ответим на три первых вопроса: о сумме продаж, о самом активном покупателе и самом популярном товаре. Для этого нужно отметить в окне справа поля Наименование товара, Покупатель, Сумма. Программа разместит поле Сумма в окошко Суммарные значения (в самом низу справа), а остальные два поля – в окошко Названия строк. Перетащите одно из полей в окошко Названия столбцов. Получится примерно так:
Всего несколько кликов мышкой, и первая сводная таблица в Excel готова! Программа уже посчитала суммы продаж в двух разрезах: по покупателям и товарам, и вывела общий итог. Таким образом программа берёт и структурирует данные. Можно немного доработать сводную таблицу. Выделите финансовые данные таблицы (диапазон B5:E9), задайте этим ячейкам финансовый формат, суммы стали нагляднее. Выделите ячейку Е5 (общий итог – покупатель Автоматика), нажмите меню Параметры, в разделе Сортировка – большую кнопку Сортировка, в открывшемся окне – Параметры сортировки – По убыванию, ОК. Теперь и производители, и товары отсортированы по убыванию, ответы на первые три вопроса получены.
Как правило, данные анализируются за определённый период или несколько периодов. Структурируем данные по временным периодам. Например, можно узнать динамику продаж продуктов по годам. Для этого нужно отметить поле Год и перенести это поле в окошко Названия строк, убрав оттуда поле Покупатель обратно в окно списка полей. Получится наглядная таблица, данные которой удобно использовать для построения графика.
Одно окошко было пока обойдено вниманием: Фильтр отчёта. Перенесите туда поле Покупатель. В ячейках А1-А2 появился фильтр выбора значений этого поля, это полезно для более детального анализа. Добавив простую диаграмму-график на основе данных сводной таблицы, получаем хороший аналитический инструмент: выбирая покупателя, можно смотреть динамику продаж по каждому товару.
Скачать пример сводных таблиц Excel: svodnie-tablici
ВСЁ о сводных таблицах с нуля за полчаса
На позапрошлой неделе снимал получасовое видео о сводных таблицах в Excel. Тогда не решился выкладывать его на Пикабу, так как подумал, что плохо зайдёт (переводить все 30 минут видео в формат поста точно не охота, а закинуть лишь видео не решился, так как думал, что такой пост не будет особо хорошо принят). Тем не менее, видео-продолжение о построении дашбордов зашло на ура, так что сейчас также делюсь видеоуроком о всех особенностях работы со сводными таблицами с нуля:
В видео на наглядных примерах рассмотрено абсолютно всё самое важное и часто используемое, что может понадобиться – начиная от лучшей формы подготовки данных для сводных таблиц в виде умных таблиц, вплоть до структурирования и форматирования сводных таблиц и их управления срезами. Файл из видео можно скачать, чтобы также поупражняться.
MS, Libreoffice & Google docs
460 постов 12.7K подписчик
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Есть три стадии принятия эксель:
1. Тупой эксель, не понимает что мне нужно
2. Удобный эксель, легко растягивать столбики
3. Тупой я, на экселе можно запустить майнкрафт
Ну наконец-то! А то я вчера смотрела видео про дашборд и там было упоминание, что надо посмотреть предыдущий пост про сводные. Не поленилась пролистать в аккаунте у Вас несколько страниц с постами и не нашла. Спасибо, в общем!
А можете подсказать, почему при определённой компановке значения встают в шахматном порядке? Как это убрать?
Извините, мы ещё лаптем кашу едим, а где можно скачать файл из видео для упражнения?
Вот если бы бабл диаграммы на сводных со срезами сделал, я бы снял шляпу, а это ещё уровень, входящий в «уверенный пользователь Microsoft office». Если что, это возможно в excel(через костыли) ну и полноценно в power BI
Когда ты не умеешь работать в Excel
Фото сделано в маршрутке
Не единожды видел, как помогали по таким вопросам. Надеюсь и мне кто-то сможет 🙂
Есть простая гугл-ексель таблица. В ней 5 вкладок. Необходимо, что бы другие люди имели возможность смотреть-редактировать только свою вкладку. А я, соответственно, имел доступ ко всем.
Визуализация гистограммы фигурами и рисунками в Excel
Мы попытаемся несколько освежить их внешний вид и сделать его более приятным для восприятия и чтения информации. Мы уже разбирали как можно построить прогресс-бары (линейный и круговой), такие графики тоже могут отлично подойти для создания дашборда и отображения KPI.
Различные фигуры (прямоугольники с закругленными краями, полукруги, стрелки) и рисунки отлично помогут нам в создании и визуализации таких графиков, и в целом немного коснемся темы инфографики.
Как перевести данные в тысячи/миллионы/… в Excel?
Сегодня обсудим способы перевода числовых данных в тысячи, миллионы, миллиарды и другие порядки в Excel для сокращения записей (например, как число вида 123 456 перевести в 123,4 тыс. или 987 654 321 в 988 млн.).
Мы можем изменить вид числа как с помощью применения формул (делением содержимого ячейки на требуемый делитель), так и сделать перевод числа с помощью настройки формата отображения ячейки.
Также научимся добавлять различные подписи к записи, к примеру, «тыс. руб.» для перевода сумм в тысячи рублей и другие подобные подписи.
Построение радиальной гистограммы в Excel | Радиальная диаграмма
Чем же особенна радиальная гистограмма?
В общем и целом это гистограмма построенная в радиальной системе координат (всем нам привычная классическая линейная гистограмма использует евклидову геометрию). Грубо говоря мы берем обычную гистограмму и сворачиваем ее в круг, и сегодня мы как раз будем учиться как можно построить радиальную диаграмму.
Умная таблица в Excel | Как сделать умную таблицу?
Разберем как сделать умную таблицу в Excel (т.е. преобразовать обычную таблицу в умную), как сделать обратное действие (т.е. уже снаоборот, убрать умную таблицу и преобразовать ее в обычную), в принципе поймем какие свойства появляются при работе с умными таблицами и прочие полезные приемы.
Количество и сумма ячеек по цвету в Excel
Сегодня поговорим про простой способ как посчитать количество, и как суммировать ячейки по цвету в Excel.
С помощью пользовательских функций реализуем суммирование по цвету заливки ячейки (или по цвету текста), а также разберем аналогичный пример и для подсчета количества ячеек одинакового цвета.
Тайм-коды для удобства навигации
Лайфхаки с Excel
1. Как восстановить файл, который вы не сохранили:
Чтобы выделить все ячейки, не нужно долго прокручивать мышкой. Достаточно будет нажать Ctrl + A или специальную кнопку в углу листа.
3. Как превратить строки в столбцы и наоборот:
Фишки Excel
1. Как быстро объединить текст из нескольких ячеек:
Нажмите на ячейку, в которой вы хотите увидеть результат, и вбейте туда знак «=». После этого выделите ячейки, которые хотите сложить, ставя перед каждой символ «&».
Как перейти к нужному листу
Все, кто работает с Excel ежедневно, знают, как трудно бывает найти нужный лист, особенно если их количество перевалило за 10. Для быстрого перемещения кликните правой кнопкой по кнопке прокрутки (она находится в нижней левой части экрана). Теперь вы можете перейти на любой нужный лист мгновенно.
3. Как создать маленькие диаграммы (спарклайны):
В Excel есть прикольная функция, которая позволяет вывести в столбце маленькую диаграмму, отображающую положение дел в других ячейках. Благодаря этой штуке вы наглядно можете показать клиенту или руководителю, что все ваши труды не напрасны и за этот год видны улучшения. Нажмите Вставка, перейдите в группу Спарклайны и выберите опцию График или Гистограмма (что больше нравится).
Диаграмма в Эксель
Пять первых шагов к освоению программы Excel.
Базовый видеокурс по работе в программе Эксель.
— как на основе таблицы создать диаграмму
— как вывести книгу Эксель на печать
Создание сводной таблицы в Excel. Реальная задача
В этом видео рассмотрим создание сводной таблицы в Excel на примере реальной таблицы, которую мне прислал подписчик.
10 Трюков в Excel
В этом видео, я рассмотрел 10 трюков в Excel:
Формулы Excel – все особенности, тонкости и трюки в одном видео!
В сегодняшнем видео мы за тридцать минут рассмотрим все самые важные основы работы с формулами и функциями в Excel. Узнав и практикуя все советы из этого видео, ты сможешь вывести свой уровень работы с формулами в Excel на абсолютно новый, по-настоящему профессиональный уровень.
В этом видео мы рассмотрим как самые основы работы с формулами (например, относительность и абсолютность ссылок, использование смешанных ссылок, прописывание значений для формул, принцип их вычисления формул и т.д.), так и советы для уже продвинутых пользователей (такие, как, например, использование шорткатов и комбинаций клавиш, в том числе мнемоников, как быстро выбирать доступные функции и т.д.). Кроме того, мы рассмотрим, что из себя представляет специальная вставка при копировании, какие варианты специальной вставки существуют и в каких ситуациях их использовать.
MS Excel Сводные таблицы + Практический файл
Курс MS Excel. Продвинутый уровень. На этом уроке создаем СВОДНЫЕ ТАБЛИЦЫ! Не смотря на то, что все кажется легко, рекомендуется скачать ПРАКТИЧЕСКИЙ ФАЙЛ и проделать те же шаги (а может и больше) с тестовыми данными. Обязательно попрактикуйтесь!
Практический файл можно скачать здесь.
Еще один урок про настройку сводных таблиц:
Офисные будни
2.8K поста 12.9K подписчика
Правила сообщества
— добавляйте посты связанные с тематикой сообщества;
-делитесь опытом организации жизни в офисе и проживания на работе;
-делитесь управленческим опытом;
— не нарушайте правила Pikabu и чтите закон.
М-м-м. Сортировка. Итоги. Вычисляемые поля. ТС, ты не раскрыл и 20% сводных таблиц.
Отдельный батхерт вызывает ютуб-формат. У тебя проблемы с изложением своих мыслей, что ты не можешь всё обычным текстом с картинками изъяснить?
Позитивное «Подписывайтесь, пожалуйста, на канал. «
Большое спасибо за файл для тренировки и начала освоения сводных таблиц!
Макросы VBA для Excel: выделение значений цветом (PERSONAL.xlsb, переменные, цикл For Each, всплывающие окна, заливка ячеек цветом)
Вопреки сказанному в конце прошлого поста, решил второй пост писать про Excel, потому что на макросы для него спрос намного выше, судя по всему. Пример будет практически малополезный, но зато попроще.
Сразу договоримся вот о чем: у меня стоит Office 2016 на домашнем ноуте и 2019 на рабочем компьютере. Они почти не отличается интерфейсом. Я прошу разрешить мне не расписывать, где что нажимать в других версиях, чтобы не раздувать посты еще сильнее. Если у вас что-то не будет получаться, просто спросите в комментариях, и я вам помогу.
Посты я теперь буду называть так, как этот, чтобы было понятно, о чем он конкретно и какие темы VBA в нем разбираются.
Но сначала кое-что важное:
Материалы данного поста созданы непрофессиональным программистом.
Я не претендую на гордое звание преподавателя, коуча или сенсея.
Я буду показывать решения, которые просто будут работать.
Критика и советы горячо приветствуются.
При, по крайней мере, написании поста ни один настоящий программист не пострадал.
1. Что такое VBA? Зачем писать на нем макросы и что нужно, чтобы они работали?
Об этом писал в первом посте. В вашего позволения, повторяться не буду.
2. Мне прислали книгу в Excel, где там макросы?
Если VBA установлен в ваш эксель, то все манипуляции с макросами нужно проводить во вкладке «Разработчик».
Если вы открыли Excel, а вкладки нет, ее нужно включить. Для этого нажмите A, C, вверх, B, вверх, B, A, вниз откройте настройки («Параметры» в самом низу, если нажать «Файл» сверху слева). Слева в списке выберите «Настроить ленту». У вас будет два списка с набором команд и вкладок. В правом найдите строчку «Разработчик» и включите напротив нее галочку, вот так:
Кстати, неплохо бы еще прогуляться в «Центр управления безопасностью», нажать кнопку «Параметры центра управления безопасностью…», затем выбрать «Настройки макросов» и выбрать подходящий вам пункт. У меня лично так:
Не хочу вас пугать, но буду на VBA можно написать и вредоносную программу, которая запустится сразу после открытия файла, поэтому включать все макросы по умолчанию стоит только на ваш страх и риск, если вы часто ими пользуетесь и вам сильно надоели всплывающие окошки с предупреждениями. Лично я на всякий случай выключаю поддержку макросов перед тем, как открыть файл, который мне кто-то малознакомый присылает. За пять лет проблем не было.
Итак, если вы все сделали правильно, у вас появится такая вкладка:
Кнопка с подписью «Visual Basic» откроет редактор проектов VBA, который почти такой же, как в прошлом посте про AutoCAD.
Через кнопку «Макросы» можно увидеть и запустить макросы, которые сейчас доступны и загружены в Excel.
Еще есть кнопка поменьше, «Запись макроса». Нажав на нее, можно сделать макрос без программирования. Можете попробовать сами, я объяснять подробно не буду, потому что лично мне кажется, что написанные таким образом макросы подходят только для имитации бурной деятельности разовых и крошечных задач. Нам эта кнопка пригодится чуть ниже для кое-чего другого.
3. Как написать свой макрос для Excel?
В редакторе проектов VBA вы увидите объекты листов, книги, а также можете добавлять свои модули и формы.
Опять-таки, пока я советую добавлять новые модули, а не писать их в книгах или листах. Со временем нам попадется ситуация, когда это будет необходимо. Если вообще будет.
В общем, добавляем модуль, чтобы потом писать туда код:
Решить такую проблему помогает специальная книга PERSONAL.xlsb, которая в скрытом режиме открывается автоматически вместе с самим Excel. В ней можно хранить макросы, и тогда они всегда будут доступны. На предыдущем изображении ее как раз видно в редакторе VBA.
У вас ее, скорее всего нет.
Чтобы она появилась, нужно три раза позвать Битлджуса сделать макрос через «Запись макроса». Нажимаете эту кнопку, потом обязательно в списке «Сохранить в» выбираете «Личная книга макросов», что-нибудь делаете (например, выделяете ячейку, пишете в нее что-нибдуь) и нажимаете «Остановить запись». Потом нажимаете кнопку «Остановить запись», она будет на месте кнопки «Запись макроса».
Если вы теперь откроете редактор VBA (нажав кнопку «Visual Basic»), то в списке проектов увидите книгу PERSONAL.xlsb, а в ней модуль с кодом того, что вы делали, пока записывали макрос. Советую удалить его, но я вам не командир.
Теперь мы можем писать туда свой код, и он будет работать во всех книгах, если мы его запустим через кнопку «Макросы».
Давайте прикинем задачу.
Например, мы владеем приютом для кошек, и мы отслеживаем вес наших подопечных. Все же любят котиков? В целях демонстрации на еженедельном собрании мы хотели бы эффектно и быстро подстветить красным цветом тех котеек, кому пора на диету. Представим данные в таком виде:
Пока давайте заранее решим, что максимальная снаряженная масса котиков – 5 кг. Тогда код будет такой:
Работает он так: выделяете какой-то диапазон ячеек (или одну), запускаете макрос. Те ячейки, в которых написано число больше 5 заливаются красным.
Давайте разбирать код подробно.
В самой первой строке:
мы с помощью ключевого слова Sub объявляем процедуру (процедура просто выполняет какой-то список команд по порядку и завершает работу) findLargeValues без параметров, потому что после ее названия идут пустые круглые скобки ().
Дальше идут строки:
Dim maxWeight As Integer
здесь мы используем ключевое слово Dim, чтобы объявить переменную и через другое обязательное ключевое слово As задаем ей тип Integer (целое число).
Но нам мало объявить машине, что теперь будет такая переменная такого типа, VBA по умолчанию придаст ей значение 0 (это так для Integer и Double, например). Поэтому в следующей строке мы присваиваем ей значение 5, с помощью простого знака равенства.
процедуру мы объявляем через Sub
функцию (которая выполняет команды, но потом еще возвращает какое-то значение) через Function
все переменные через Dim.
Далее идет цикл For Each … In … Next.
Я не случайно уже второй раз использую его в примерах, потому что он очень часто оказывается полезен, поэтому лучше понять, как он работает, как можно раньше.
Я понимаю, что это непросто, но давайте представим, что вы – Гринч Санта Клаус.
Вся ваша ежегодная работа будет описываться циклом:
Для Каждого ребенка В спискеХороших
На языке VBA это будет выглядеть так:
For Each child In niceList
Этот цикл берет массив или коллекцию (простыми словами – какой-то набор чего-то), перебирает по очереди каждый ее элемент, к каждому из которых мы можем обращаться через переменную, чтобы производить с ним какие-то действия.
В примере с Сантой, коллекцией будет niceList, переменной – child. Циклю For Each … In … Next будет брать каждого child, который содержится в niceList, и вызывать для него метод makeAGift (читай – дарить подарок ему).
В макросе, который мы пишем, коллекцией послужит Selection. В Excel Selection – это коллекция того, что выделено на экране. Но может быть выделен диапазон ячеек, изображение и еще много чего. Мы этот момент сегодня рискованно опускаем, и считаем, что выделяется диапазон ячеек с числами. Переменную мы назвали cell, просто потому что это будет понятно.
Дальше идет условный оператор, If Then Else End if.
Я уже разобрал его в первом посте, но давайте еще раз.
— это что-то, что можно подать машине как Boolean, как величину True или False (Да или Нет).
Если = True, то выполняется код1. В противном случае выполняется код2.
Если Else вообще нету, то код1 выполняется, если = True, а в противном случае вообще ничего не происходит в программа просто идет по строчкам дальше, что там написано после End if.
Что мы имеем в нем:
cell – это та самая переменная из цикла For Each … in … Next. Она олицетворяет одну отдельно взятую ячейку из выделенного диапазона.
Через cell.Value мы получаем доступ к тому, что написано в этой ячейке, к ее значению.
Функция Val(“строка”) извлекает из строки число, если оно там вообще есть. Мы применяем ее, чтобы обезопасить себя от ситуации, когда в Excel e ячейки cell значание будет, например «5а». В этом случае функция Val(cell.Value) вернет нам просто число 5.
Далее все это выражение просто сравнивается с нашей переменной maxWeight.
Таким образом, все это выражение Val(cell.Value) > maxWeight будет True, если в ячейке cell будет написано число больше 5, и будет False, если оно будет меньше или равно 5.
Ну и дальше мы видим, что в случае True, то есть когда вес нашего отдельно взятого кота, который записан в cell, больше 5 кг, выполняется «код1»:
Он обращается к свойству Interior, которое есть у ячейки cell (а точнее, у всех ячеек и диапазонов ячеек в Excel, у каждой со своим значением). У этого свойства, в свою очередь, есть своё свойство Color, которому мы присвоили значение vbRed. Иными словами, назначает ей красный цвет заливки.
В случае False в условии происходит то же самое, только цвет присваивается xlNone, то есть «никакой», «без заливки».
Цвета, начинающиеся с vb… «вшиты» в сам Visual Basic, они бывают
xlNone – это специальный для Excel цвет, который обозначает отсутствие цвета. Кроме как в экселе работать код с его использованием нигде больше не будет.
Если вам хочется повыёживаться использовать какой-то другой цвет, то можно написать
cell.Interior.Color = RGB(rr, gg, bb),
а вместо rr, gg и bb подставить число от 0 до 255, обозначающее интенсивность красного, зеленого и синего соответственно.
Последняя строка в нашем коде End Sub обозначает, что все, что процедура кончилась.
Результат его выполнения будет такой:
Такой вот нехитрый макрос.
Но давайте добавим в него чуть-чуть универсальности и интерактивности.
Давайте очень легко и просто сделаем так, чтобы максимальное значение веса можно было менять прямо перед выполнением макроса.
Изменим код вот так
То есть, вместо скучной пятерки вы получаем число от пользователя через метод InputBox:
Вообще, этот метод в полной форме выглядит так:
InputBox(prompt, [ title ], [ default ], [ xpos ], [ ypos ], [ helpfile, context ])
prompt – обязательный параметр, сообщение в самом окошке, значение String (строка, пишется обязательно в кавычках);
[title] – необязательный параметр, заголовок этого окошка, тоже значение String;
[default] – необязательный, то, что по умолчанию уже будет написано, можете писать туда любое число или String;
[ xpos ], [ ypos ] – необязательные, положение окна на экране от верхнего левого угла, любое число;
Мы, как вы заметили, использовали только первые три параметра.
Если бы вы, например, хотели указать только prompt, title и xpos, ypos, то нужно было бы писать так:
InputBox(“Сообщение”,”Заголовок”,,100,150), то есть ставить запятую, перед тем местом, где должен был быть default, а потом еще одну, перед xpos. А после ypos просто закрывать скобку.
На этом, пожалуй, остановимся. Всем спасибо за внимание, желаю успехов в освоении VBA.
Буду рад видеть в комментариях ваши мнения, просьбы и советы.
Я долго вынашивал идею писать посты на тему макросов VBA в AutoCAD и Office, и решил все-таки попробовать.
Несмотря на то, что Microsoft уже давно заявил, что перестал развивать Visual Basic, VBA все еще пользуется спросом, что нет-нет, да доказывают мне окружающие, в том числе и пикабушники (@genrix4444 и @Alex0STR, привет!).
Я предлагаю вам учиться со мной на примерах из реальных задач.
Начнем мы сегодня с того, что определимся, зачем вообще могут пригодиться макросы на VBA, как их запускать и писать самим, а пример сегодня возьмем из AutoCAD.
Но сначала кое-что важное:
Материалы данного поста созданы непрофессиональным программистом.
Я не претендую на гордое звание преподавателя, коуча или сенсея.
Я буду показывать решения, которые просто будут работать.
Критика и советы горячо приветствуются.
При, по крайней мере, написании поста ни один настоящий программист не пострадал.
1. Что такое VBA? Зачем оно человечеству?
2. Как понять, что вам нужен макрос?
Если вы заметили, что в вашей работе в офисе или автокаде больше механической, чем умственной работы – нужен макрос. Если какие-то манипуляции вы проводите раз за разом, и они очень похожи – нужен макрос.
Появление макроса сделает вашу работу проще, у вас освободится время, чтобы поспать работать быстрее или играть в танки работать более качественно.
3. Что нужно, чтобы использовать макросы в AutoCAD? А в Excel, Word и т.д.?
Нужно установить эти программы с поддержкой VBA, либо установить отдельно. Если у вас будут сложности, пишите в комментариях, и я сделаю подробную инструкцию в одном из следующих постов.
Для этого нужно загрузить макрос в автокад.
Это делается во вкладке «Управление» кнопкой «Загрузить приложение», вот тут:
Макрос будет работать пока вы не закроете автокад. Выбирать файл нужно тут:
Если вас это не устраивает, и вы хотите, чтобы все примочки из этого макроса были отныне с автокадом навсегда, пока форматирование не разлучит их, то добавьте его в список автозагрузки (кнопка «Приложения» под портфельчиком с надписью «Автозагрузка»).
Чтобы автокад не пугал, что макрос неизвестно откуда и что «может ну его?», лучше папку, в которой лежит dvb, добавить в «Доверенные местоположения», вот тут:
Теперь можно нажать кнопку «Запустить макрос VBA», выбрать, собственно, что запускать, и нажать «Выполнить»:
Как видно, в списке тут прописано, из какого файла запускается макрос, потом после «!» идет имя модуля (подробнее о них – ниже), чаще он один на файл, и название «команды».
В моем случае файл запускает «draw_XY» из Module1 файла UTILS.dvb.
Если кому интересно, оно просто подписывает координаты X и Y в точке, в которую пользователь тычет мышкой.
Так вот, все, что произойдет после нажатия кнопки «Выполнить», решается программным кодом. Ничего сложного там нет, смелее читайте дальше. Я смог, и вы сможете.
5. Как написать свой макрос для AutoCAD?
Для начала нужно создать новый проект. Для этого нужно в командной строке автокада ввести VBAMAN (вы стали свидетелем рождения нового супергероя), либо мышкой нажать на список открыть «Диспетчер VBA» вот тут:
В появившемся окне, нужно нажать кнопку «Новый», тогда у вас появится строка Global# (в моем случае Global10):
Это и есть наш новый проект. Пока он не сохранен в файл нигде, поэтому так странно называется и путь к нему не указан.
Теперь нажмем кнопку «Редактор Visual Basic», и увидим что у него, проекта, внутри.
А внутри – пустота. Есть только объект «ThisDrawing», который создается по умолчанию. Его даже не удалить (и не надо). Можно писать код прямо туда, но лучше делать отдельный модуль под отдельный набор функций (для чего – расскажу позже, когда это станет легче объяснить). Для этого на папку «AutoCAD Objects» нажимаем правой кнопной, выбираем «Insert – Module».
Теперь у нас есть отдельный модуль. Самое время разобраться целиком с окошком редактора VBA.
1 – Структура проекта. Там всегда будет объект «ThisDrawing» (он тоже своего рода модуль). Еще туда можно надобавлять модулей и форм. Формы – это привычные нам окошки с кнопками, галочками и всем подобным. Окошки – это основная фишка Visual Basic (он поэтому так и называется), с ними оно, конечно, интереснее, но будем разбираться в следующих постах (если хотите вообще).
2 – Свойства выбранного объекта. В нашем случае единственный объект – модуль. Его единственное свойство – его имя. По щучьему велению я переименовал его в EasyMacro1.
Все, что находится правее 1 и 2 – редактор самого программного кода. Считай блокнот, только специальный.
3 – Список объектов внутри модуля. У нас их там нет, поэтому у нас только строка «(General)». Если вы выберите объект «ThisDrawing» (два раза щелкаем левой кнопкой), то там у нас будет еще «AcadDocument».
4 – Список процедур, функций и событий (те же процедуры) этого объекта. У нас оптяь только «(Declarations)». И теперь самое время сказать:
Какие еще процедуры, какие еще функции? Вы бы еще про переменные написали тут!
Вот и приехали. Теперь начинается само программирование.
Для начала чуть-чуть теории, определимся с тем, кто как называется, а потом начнем писать сами, и станет понятно.
VBA – процедурный язык программирования. Все, что он делает – выполняет по порядку, строчка за строчкой, либо процедуру, либо функцию. Обе эти штуки – наборы команд, и единственная разница между ними в том, что процедура просто выполняет какие-то действия и уходит в закат, а функция еще и выдает какой-то результат (число или строку, например).
И внутри всего этого хаоса могут быть переменные – заранее застолбленные «имена», по которым компьютер будет хранить значения, например, числа, строки, опять-таки, или даты, например.
Начнем с самой идеи макроса, обдумаем в голове, что он будет делать и для чего.
Вот есть у меня чертеж. Пусть это будет, условно, три электрических подземных кабеля, начерченных в плане (вид сверху) в миллиметрах.
Вот мы видим, что у одной из «веток» длина 52083.65 миллиметра, что, считай, 52 метра 8 сантиметров.
И представим, что кто-то мне сказал, что надо подписать длины этих кусочков. А мне лень. Или их очень много. Или и то, и другое.
Я подумал, попил кофейку, и решил написать макрос, который будет в начало каждой «ветки» вставлять текст, в который будет записывать строчку «L = X.XX м».
Для этого я создам процедуру (а мы помним, что она-то как раз что-то там делает), назову ее writeLengths, чтобы было понятно, что она делает.
Для этого я пишу Sub writeLengths() в редакторе кода, и жму Enter. А редактор сам за меня допишет End Sub.
Sub – это так в VBA обозначается процедура. Если бы мы хотели написать функцию, то у нас было бы
Function () … End Function
Все, что мы напишем промеж строк
и будет теми командами, которые будет выполнять процедура до своего ухода в закат.
В тех круглых скобках, при необходимости, пишут входные параметры. Когда появится в них необходимость, вы быстро поймете, как и зачем их использовать. Сегодня не будем.
А что нам надо? Да только свет в оконце. Нам надо, чтобы макрос брал все полилинии в чертеже (пускай пока вообще все полилинии, ок?), узнавал, какой они длины (ДАННЫЕ УДАЛЕНЫ) и вставлял в чертеж текст с ее значением.
Код будет очень простой, а он будет выглядеть вот так:
Первые две строки – это объявление переменных. Оно всегда начинается с ключевого слова Dim, потом идет имя переменной, затем ключевое слово As и ее тип.
Имена переменных не должны повторяться, не должны начинаться цифры, не должны содержать пробелов и не должны быть ключевыми словами. Еще хорошо бы, чтобы они были понятны хотя бы вам (а лучше всем, кто может увидеть ваш код).
Типов в VBA бывает не так много, какой когда использовать – отдельная микронаука. Для наших скромных целей давайте пока использовать:
String – строка текста. Пишется всегда, обязательно в кавычках: “СТРОКА”.
Boolean – логическая переменная. Ее значение может быть только либо Труъ True либо False. Одно или другое, просто и понятно.
Теперь еще раз посмотрите на код и на меня. Сразу все не так пошло, правда?
У нас объявлена переменная point, ее тип – Double. Но что это за скобки, почему там пробелы?
Дело в том, что это point – это не одно значение Double, а целый их массив.
Массив – это целый набор значений какого-то конкретного типа, со своим размером.
В нашем случае в нем есть три элемента, первый начинается с нуля. В скобках написано (0 to 2), что значит что в массиве будут элементы от 0 до 2, то есть 0, 1, 2. Чтобы получить конкретное число, нужно написать его номер в массиве, например point(1). Если написать point(4), то произойдет апокалипсис ошибка после запуска макроса. Можете попробовать.
Массив (для примера из трех элементов) можно задать так:
тогда его элементы будут считаться от 1.
Мы сделали от 0, потому что по умолчанию делается так, и это поможет нам избежать путанницы. Ниже увидите в чём.
Дальше у нас идет цикл For each … Next.
Если знание английского языка позволяет, то можно понять, что там творится просто прочитав.
А творится вот что:
объекта (это, кстати, тоже переменная, просто мы ее отдельно не объявляли вначале, потому что кроме как внутри цикла она нам больше нигде не нужна)
«этого чертежа» (имеется в виду тот, который был отрыт, когда мы запустили макрос)
пространстве модели (вы ведь знаете, что кроме модели еще есть листы в автокаде, да?)
выполняются какие-то действия.
Действия, как вы уже поняли, находятся между строк
For Each object In ThisDrawing.ModelSpace
Так как в пространстве модели могут быть разные объекты: полилинии, круги, отрезки, текст, штриховки и т.д., нам нужно как-то определять, какой из них – полилиния. И если это полилиния, тогда уже с ней что-то делать.
Это можно сделать с помощью условного оператора:
В нашем случае берется object.ObjectName (это такое «внутреннее текстовое название» объектов в автокаде, для полилиний оно всегда будет равно «AcDbPolyline»). Если оно как раз равно «AcDbPolyline», то у нас полилиния.
Еще, кстати, условный оператор может выглядеть так:
В этом случае действия1 выполнятся, если условие верно (то есть равно true, там как раз Boolean), а если не верно (равно false), то выполнятся действия2.
Теперь разберем, что будет происходить, если макрос нашел полилинию (то есть, условие выполнилось).
Мы хотим записать в массив point координаты X и Y первой точки найденной полилинии, чтобы туда вставлять текст.
Еще раз: получить массив с координатами первой вершины мы смогли при помощи записи object.Coordinate(0), а уже потом дописываем опять (0), чтобы получить его первый элемент.
потому что мы чертим в 2D и координата Z нас не волнует совсем.
А вот дальше – самое интересное.
С помощью ключевого слова Call мы вызываем метод (тоже, считай, процедура) AddText, который есть у пространства модели «этого чертежа».
То есть, получается
В скобках через запятую мы пишем параметры, с которыми оно вызывается:
значение текста, который надо написать (в нашем случае мы туда отправляем object.Length – длина объекта)
точка, в которую этот текст вставляется (наш массив point)
и высота текста (я выбрал 200 единиц, чтобы его было видно без микроскопа сильного приближения).
Теперь макрос можно запустить и посмотреть, что получилось.
Кстати, из окна редактора VBA можно запустить нажав зеленую стрелочку вверху (рядом с кнопками «Пауза» и «Стоп»).
А получилось вот что. Длина проставилась в первой точке. Но она проставилась в миллиметрах. И после запятой знаков слишком много.
Это можно исправить, если отформатировать строку со значением текста.
Формат выражения – это тоже строка, но которая описывает, как должно выглядеть значение.
Например, если мы напишем “#”, то получим только целые части чисел. Было 1,23 стало 1.
Если написать “#.#”, то получим один знак после запятой.
Если вместо решетки писать нули, то они как бы занимают место. То есть с форматом “00.00” число 1.2 будет написано как «01.20«. Причем если у нас было бы не 1.2, а 101.2, все рано получилось бы «101.2«. До запятой знаки не отрезаются. Логично же, это уже совсем другое число получится.
Еще в формат можно дописывать другие символы, они просто пропишутся как есть. Можно, например, написать “L = 0.00 м”, и мы получим из числа 52083.6472… запись «L = 52083.65».
А это как раз то, что нам нужно, только нам нужно еще и в метрах это все. Для этого object.Length надо просто разделить на 1000.
В общем, исправим код вот так:
Call ThisDrawing.ModelSpace.AddText(Format(object.Length / 1000, «L = 0.00 м»), point, 200)
И результат получим такой:
Макрос в определенной мере готов, осталось его сохранить, нажав Ctrl+S, либо “File – Save Global#”.
Конечно, такой макрос дает не очень удобный результат.
Можно сделать так, чтобы текст разворачивался вдоль линии, и вставлялся в ее середину. И в отдельный слой. И другим цветом, например.
Но пост получился уже достаточно объемный из-за долгого вступления, поэтому на этом пока остановимся.
Следующий пост, наверное, напишу тоже на тему автокада, потому что придумать что-то простое, но полезное для Excel пока не удается.
Буду рад видеть в комментариях ваши мнения, просьбы и советы.
Основы Excel. Форматирование данных
В этом видеоуроке автор разбирает, что представляет собой форматирование в Excel, и как им следует грамотно пользоваться.
Сводные таблицы
Продолжаем совершенствоваться в фехтовании данными любимого Excel. Сегодня, по многочисленным заявкам, мы капнем на пол штыка такую удобную штуку как сводные таблицы. Почему так не глубоко? Да потому что тема большая и ее придется растянуть на несколько постов. Что же это такое сводная таблица? Справка говорит нам, какие задачи решают сводные таблицы:
Запрос больших объемов данных различными понятными способами.
Подведение промежуточных итогов и вычисление числовых данных.
обобщение данных по категориям и подкатегориям
создание пользовательских вычислений и формул
Развертывание и свертывание уровней представления данных для выделения результатов и выполнение тщательного анализа сводных данных по интересующим вопросам.
Перемещение строк в столбцы или столбцов в строки («сведение») для просмотра различных сводок на основе исходных данных.
Фильтрация, сортировка, группировка и условное форматирование наиболее важных подмножеств данных для концентрации внимания на нужных сведениях.
Представление кратких наглядных отчетов с примечаниями на веб-страницах или в напечатанном виде.
В общем, какое то волшебство. Вполне большой и удобный перечень функционала для анализа и наглядного представления информации, не правда ли.
Также хочу отметить, что возможно вы знаете более удобные инструменты работы с базами данных, но мы имеем то, что имеем, то есть Excel, который тоже довольно таки удобен.
Крайне удобно работать сводной таблицей с большими объемами данных, представленных в формате простейшей таблички без всяких там объединенных ячеек (очень не люблю объединенные ячейки), а также лучше всего, чтобы все ячейки таблицы были заполнены данными. Дело в том, что хотя для наших глаз объединенные ячейки относятся к нескольким столбцам, Excel видит их так, как бы они выглядели, если снять объединение, то есть вся информация пишется в первой ячейке, а остальные пустые.
Вот эти пустые ячейки сводная таблица и не любит, особенно в заголовках столбцов. Первое правило сводной таблицы – (никому не говорить о сводной таблице) все столбцы исходных данных, из которых мы формируем сводную таблицу, должны быть озаглавлены, и озаглавлены понятно и по-разному.
Итак, давайте разбираться сначала и на примере. Есть у нас какой-то большой объем данных, для примера я накидал такую таблицу.
Не обращаем внимания на цены, которые взяты с потолка, и на то, что таблица не такая уж и большая, это пример.
Вот такой тип данных наиболее удобен для дальнейшей обработки сводной таблицей. Если бы имелись объединенные ячейки в заголовки столбцов, либо в строках, как если бы в примере столбец «вид продукта» эти виды были бы объединены, то нам пришлось бы сначала привести таблицу к виду «как положено». Как это сделать побыстрее расскажу отдельно, если кому будут желающие слушать.
Сводную таблицу можно формировать где угодно, хоть в другой книге. Для удобства сформируем ее на отдельном листе. Заходим во вкладку «Вставка», в разделе «Таблицы» нажимаем «Сводная таблица»
Если в дальнейшем планируется добавлять данные в нашу базу то лучше диапазон выбрать до конца листа, тогда нужно будет «обновить» сводную чтобы данные добавились. Единственное нужно будет в фильтре сводной выкинуть эти пустые ячейки. Также если в таблице есть промежуточные и конечные итоги их стоит убрать, чтобы сводная таблица их не учитывала. На крайняк их тоже можно будет выкинуть в фильтре сводной таблицы. У нас появилась такая картина:
Что, же уважаемый Excel, вызов принят! выбираем поля «вид продукта», «количество на складе». Получаем
красотень
жмакаем номер склада, немного не то что хотел, перетаскиваем поле № склада из раздела «Итоги» в раздел «Названия столбцов».
Если задействовать более 2 полей в сводной таблице в названиях строк появится подкатегории, и можно будет сворачивать и разворачивать разделы
Теперь у нас «Продукт» в отдельном столбце, появился Итог по приправам отдельной строкой, вся таблица немного изменилась и больше похожа на классическую таблицу. Может кому то такой вид больше пригодится, но позже я расскажу Вам как его можно использовать.
Вот ссылка на гугл диск https://drive.google.com/file/d/0B8QwhfN2DgusNDNtRjloN1E5MWV.
На этом давайте пока остановимся, продолжение следует.
Волшебная формула
Всем привет. Продолжаем постигать Excel. Если Вы не знакомы с моими предыдущими уроками, то советую ознакомиться. Сегодня я хочу продолжить тему, поднятую в предыдущем посте Как я делаю шаблоны о составлении шаблонных форм. Однажды мне потребовалось сделать такой лист, содержимое которого зависит от названия листа. Я нашел одну чудесную формулу и хочу ею с Вами поделиться. Также дополнительно я хочу показать, как в простом листе запутать формулы так, чтобы потом в них было практически невозможно разобраться. Спросите, зачем это нужно? Иногда бывает полезно – скидываешь человеку таблицу и если в ней нужно что-то переделать, то он опять обращается к Вам, только не забудьте сохранить у себя исходник. Но обо всем по порядку.
Сначала немного теории.
Рассмотрим работу функции ЯЧЕЙКА (тип_сведений;[ссылка]), она имеет 2 аргумента: тип сведений и ссылку на ячейку соответственно. Сейчас нас интересует тип «имяфайла», выбираем его и ссылаемся на саму ячейку, где мы это пишем (остальные типы вам на самостоятельное изучение). Для корректной работы этой функции необходимо чтобы файл был сохранен где-нибудь. Итак, в имени файла мы видим непосредственно имя файла и после него имя листа, на котором прописываем эту функцию (точнее на лист, куда ссылаемся ссылкой). Давайте попробуем вырезать то, что нам нужно, а именно имя листа. Для этого нам нужно знать длину текста в ячейке с именем файла, получаем ее с помощью функции ДЛСТР(),затем нам нужно найти позицию закрывающейся квадратной скобки, которая ограничивает имя файла при помощи функции ПОИСК, разница этих чисел и будет длиной имени листа. Отрезаем справа от первоначальной ячейки эту длину и получаем ячейку с именем файла. В результате у нас должно получиться нечто подобное.
Формулы при этом выглядят так:
Пробуем поменять имя листа и видим что содержимое нашей ячейки меняется. Для чего же это можно использовать. Давайте сделаем шаблон, в который будет подставляться данные по средствам функции ВПР, искомым значением которого будет эта наша ячейка с именем листа, а столбцы, из которых будет подставляться значения будут у нас разные и завесить от необходимой информации
подтягивать данные будем из такой таблицы:
назавем этот лист «База».
Здесь рассмотрим еще одну полезную функцию Excel, которая называется именованные области (или как-то так). Выделяем область к которой нам нужно будет часто обращаться, в нашей таблице это столбцы от А до G на листе «База», и жмакаем на строку которая расположена слева от значка формулы
теперь пишем там название нашей области «база».
Все, теперь в формулах можно вместо ссылки на область эту область писать просто база! Пишем в лист с волшебной формулой следующие формулы:
помним что в ячейке D5 у нас название листа, которое будет искаться в первом столбце базы, то есть для создания карточки сотрудника нам нужно будет назвать лист как фамилию нужного нам сотрудника. Я решил не просто тупо подставлять значения из таблицы (это же скучно), а склеить ФИО, вместо дня рождения выводить сколько полных лет, а вместо даты приема на работу стаж в годах. Но это просто в образовательных целях. Можно и эти данные в формуле прописать в базе.
Теперь проверим, назовём лист «Иванов»:
Во как!
на основании такого листочка и нехитрого макроса я на пример делал много отчетов с разбивкой по пятидневкам или по дням из месячной базы. Но создание макросов я намеренно не рассматриваю в своих статьях (сам только учусь их писать)
Теперь перейдем ко второй части нашего балета. То есть запутаем эту формулу так, чтобы сами не могли ничего разобрать. Смотрите, у нас в ячейке D1 прописана формула, ссылающаяся в принципе на любую ячейку этого листа, для удобства поменяем чтобы она ссылалась на ячейку A1. Теперь посмотрим, кто как у нас на этом листе ссылается: ставим курсор на A1, идем в пункт формулы, нажимаем «зависимые ячейки» несколько раз, получаем:
видим что все формулы зависят от А1. Теперь идем в D5, копируем все что там после знака «=», нажимаем поиск с заменой (Ctrl+H), пишем Найти D5, заменить на то что скопировали ПРАВСИМВ(D1;D4). Заменяем везде, затем идем в D4, копируем все что после знака «=»…. и так повторяем пока не придем в ячейку А1. После этого ячейки D1-D5 можно удалить, они у нас были как бы промежуточные. В итоге лист у нас работает также как и раньше, а что же в формулах? Заглянем в B4: «@=ВПР(ПРАВСИМВ(ЯЧЕЙКА(«имяфайла»;A1);ДЛСТР(ЯЧЕЙКА(«имяфайла»;A1))-ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1);1));база;1;0)&» «&(ВПР(ПРАВСИМВ(ЯЧЕЙКА(«имяфайла»;A1);ДЛСТР(ЯЧЕЙКА(«имяфайла»;A1))-ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1);1));база;2;0)&» «&ВПР(ПРАВСИМВ(ЯЧЕЙКА(«имяфайла»;A1);ДЛСТР(ЯЧЕЙКА(«имяфайла»;A1))-ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1);1));база;3;0))»
жуть какая! ничего не разберешь. но мы то с вами знаем что формула ПРАВСИМВ(ЯЧЕЙКА(«имяфайла»;A1);ДЛСТР(ЯЧЕЙКА(«имяфайла»;A1))-ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1);1)) просто показывает нам имя листа. И это у нас относительно простая зависимость была изначально. Посредством таких вставок с заменой мы убираем промежуточные вычисления, что очень сильно ухудшает читаемость формулы.
вот пример на гугл. докс., но только он не работает потому что файл не сохранен на диск, сохраните и ковыряйте
https://drive.google.com/file/d/0B8QwhfN2DgusTzIxQ1ZHTlZtclc.
На этом пока все, в следующий раз наверное все таки будут сводные таблицы (по многочисленным просьбам)
Как я делаю шаблоны
Всем добра. Продолжаем совершенствоваться в овладении великим и могучим Excel.
Сегодня я решил показать один из способов, как в excel`е можно сделать заполняемые формы. Тут есть что то из старого материала (смотри предыдущие уроки), ну и кое-что новенькое покажу. Итак, допустим у нас есть какая либо форма, которую периодически нужно заполнять (на пример какие ни будь заявления, приказы, объяснения и т.д.). Данные в форме немного варьируются, некоторые слова немного меняются, но основной текст остается прежней, некоторые слова (имена, фамилии) нужно выбирать из списка, какие то значения вбивать и т.д. В таком случае можно просто брать шаблон и вбивать туда данные, но при частом использовании взгляд «замыливается» и возможны допущения ошибок. Так что сделаем такой шаблон, который можно было бы «настраивать». Мой пример будет иметь малое практическое применение, я просто в нем хочу показать варианты использования элементов управления формами. Для начала давайте их найдем. Находятся они на вкладке «Разработчик». Если ее не видно идем в параметры и включаем ее во вкладке «Настройка ленты». Жмакаем вставить и смотрим что нам тут есть полезного (функционал я расскажу сразу в примере):
Теперь накидаем пункты, которые будут переключаться.
в столбце H стоят вот такие формулы (есть волшебная комбинация клавиш Ctrl+ё, которая на листе показывает вместо значений формулы в ячейках):
которые подставляют данные посредством функций ВПР и ИНДЕКС из табличек: