что такое кросс таблица в excel

Excel-plus

Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel

Как построить сводную таблицу в Excel. Настройки сводной таблицы. Примеры и описание.

Как построить сводную таблицу в Excel. Настройки сводной таблицы. Примеры и описание.

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

Исходная таблица.

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

Как построить сводную таблицу в Excel.

Сначала необходимо выделить исходную таблицу или диапазон данных. После этого в закладке Вставка выбрать Сводная таблица.

Появляется диалоговое окно Создание сводной таблицы.

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

Далее выбираем, куда поместить непосредственно Сводную таблицу. На новый лист или На существующий лист. Как правило Сводную таблицу помещают на новый лист. Если ее поместить на существующий лист, то в этом диалоговом окне, в соответствующем поле Диапазон, можно указать место куда разместить Сводную таблицу.

Открылся новый лист в правой части которого появился блок настройки Сводной таблицы — Поля сводной таблицы.

Он содержит в себе следующие элементы:

После того, как выбраны (поставлены галочки) поля для добавления в отчет, они отобразятся в блоках (полях) Фильтры, Столбцы, Строки и Значения. Далее, их необходимо перетащить в соответствующие поля исходя их того, какую Сводную таблицу необходимо получить в результате.

Пункт Отложить обновление макета, позволяет настраивать обновление Сводной таблицы. Если галочка не стоит, настройки сделанные в блоке Поля сводной таблицы отобразятся в Сводной таблице сразу. Если галочка стоит, то только после того, как нажата кнопка Обновить.

Настройки сводной таблицы в Excel.

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

После того, как галочки поставлены, элементы появились в блоках (областях) Фильтры, Столбцы, Строки и Значения.

И уже будет сформирована Сводная таблица.

Продолжим настройку Сводной таблицы. Наша цель, чтобы в столбцах Сводной таблицы отражалась сумма проданных товаров, во всех пяти магазинах, помесячно (январь — июнь). А в строках отражались товары, по видам (Товар №1, Товар №2 и т.д.). На выходе мы получим суммарное количество проданных товаров, каждого вида, указные по месяцам. В качестве фильтра используем магазины. Таким образом, в случае необходимости, мы сможем отразить в Сводной таблице только нужный нам магазин (Магазин №1, Магазин №2 и т.д.).

Как это сделать.

Перетаскиваем элемент Магазин в поле Фильтры.

Наша таблица готова.

В ячейки В1 располагается фильтр. Если нажать на стрелочку фильтра в этой ячейки, появиться возможность выбрать одни или несколько магазинов, после чего в Сводной таблице отобразятся соответствующие этим магазинам продажи. Но предварительно нужно поставить галочку в пункте Выделить несколько элементов.

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

Если Поля сводной таблице закроются, вернуть их можно вызвав контекстное меню, нажав правой клавишей мыши в поле построенной Сводной таблицы и выбрав пункт: Показать список полей.

Источник

Преобразуйте сводную кросс-таблицу в плоский список быстро и точно

Кросс-таблицы также называют сводными, двумерными (2D) или таблицами в «пользовательском» представлении. Они преподносят информацию в сжатой и наглядной матрице с заголовками столбцов и строк. Но такое представление данных не подходят для построения сводных таблиц PivotTable, графиков, фильтрации, экспорта данных в сторонние системы, т.д. Поэтому перед анализом данных так важно аккуратно преобразовать сводные таблицы в «плоский» список.

Инструмент «Редизайн таблицы» точно преобразует сводные таблицы в плоский список без написания макросов:

Перед началом работы добавьте «Редизайн таблицы» в Excel

«Редизайн таблицы» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Как преобразовать сводную таблицу Excel в плоский список

Укажите размер заголовков:

В простой таблице: Заглавных строк = 1, Заглавных столбцов = 1

Укажите, следует ли поместить результат на новый или на существующий лист.

Чтобы вставить плоский список на существующий лист, укажите начальную ячейку (верхняя левая).

Как преобразовать сложную сводную таблицу с многоуровневыми заголовками

Некоторые сводные таблицы могут иметь сложную структуру и многоуровневые заголовки. Их также можно сделать плоскими с помощью XLTools:

Укажите размер заголовков:

Как выполнить редизайн таблицы с пустыми ячейками

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

Как выполнить редизайн таблицы с объединёнными ячейками

Отметьте флажком Дублировать значение в объединённых ячейках :

Как выполнить редизайн таблицы с сохранением заголовков

Отметьте флажком Сохранить заголовки :

Как выполнить редизайн таблицы с сохранением формата ячеек

Отметьте флажком Сохранить формат ячеек :

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

Какие таблицы обрабатывает надстройка «Редизайн таблицы»

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

Термином «Таблица» в Excel часто обозначают разные понятия:

Надстройка XLTools «Редизайн таблицы» позволяет преобразовать в плоский список «настоящие» таблицы и диапазоны. Чтобы произвести редизайн PivotTable, сначала скопируйте диапазон такой таблицы и вставьте значения — это создаст простой диапазон, который далее можно преобразовать.

Источник

Сводная таблица в Excel. Как сделать?

Сводная таблица – это один из наиболее полезных инструментов в Excel. С ее помощью появляются широкие возможности для анализа больших массивов данных и быстрых вычислений.

Видеоурок: Как создать сводную таблицу в Excel

Что такое сводные таблицы в Excel? Пошаговая инструкция

Сводные таблицы это инструмент Excel для суммирования и анализа больших объемов данных.

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

Она содержит данные:

Теперь, представим, что наш руководитель поставил задачу вычислить:

Для поиска ответа на эти вопросы вы можете использовать различные функции и формулы. Но что, если задач по этим данным будет не три, а тридцать? Каждый раз вам придется менять формулы и функции и подстраивать под каждый тип расчета.

Ниже мы разберем, как в решении этих задач нам поможет сводная таблица.

Как сделать сводную таблицу в Excel

Для создания таблицы выполните следующие действия:

После нажатия кнопки “ОК” таблица будет создана.

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

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

Области сводной таблицы в Excel

Для эффективной работы со сводными таблицами, важно знать принцип их работы.

Ниже вы узнаете подробней об областях:

Что такое кэш сводной таблицы

При создании сводной таблицы, Excel создает кэш данных, на основе которых будет построена таблица.

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

Кэш данных увеличивает размер Excel-файла.

Область “Значения”

Область “Значения” включает в себя числовые элементы таблицы. Представим, что мы хотим отразить объем продаж регионов по месяцам (из примера в начале статьи). Область закрашенная желтым цветом, на изображении ниже, отражает значения размещенные в области “Значения”.

На примере выше создана таблица, в которой отражены данные продаж по регионам с разбивкой по месяцам.

Область “Строки”

Заголовки таблицы, размещенные слева от значений, называются строками. В нашем примере это названия регионов. На скриншоте ниже, строки выделены красным цветом:

Область”Столбцы”

Заголовки вверху значений таблицы называются “Столбцы”.

На примере ниже красным выделены поля “Столбцы”, в нашем случае это значения месяцев.

Область “Фильтры”

Область “Фильтры” используется опционально и позволяет задать уровень детализации данных. Например, мы можем в качестве фильтра указать данные “Тип клиента” – “Продуктовый магазин” и Excel отобразит данные в таблице касающиеся только продуктовых магазинов.

Сводные таблицы в Excel. Примеры

На примерах ниже мы рассмотрим, как с помощью сводных таблиц ответить на три вопроса:

Прежде чем анализировать данные, важно решить каким образом должны выглядеть данные таблицы (какие данные разметить в колонки, строки, значения, фильтры). Например, если нам нужно отобразить данные продаж клиентов по регионам, то следует поместить названия регионов в строки, месяцы в колонки, значения продаж в поле “Значения”. Как только вы представили каким образом вы видите итоговую таблицу – начинайте её создание.

В окне “Поля сводной таблицы” размещены области и поля со значениями для размещения:

Поля создаются на основе значений исходного диапазона данных. Раздел «Области» – это место, где вы размещаете элементы таблицы.

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

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

Пример 1. Какой объем выручки у региона Север?

Для вычисления объема продаж региона Север, рекомендую разместить в таблице данные продаж по всем регионам. Для этого нам потребуется:

Получим ответ: продажи региона Север составляют 1 233 006 966 ₽:

Пример 2. ТОП пять клиентов по продажам

Для того чтобы вычислить рейтинг ТОП пяти клиентов, нам нужно:

У нас получится следующая таблица:

По-умолчанию, система Excel сортирует данные в таблице в алфавитном порядке. Для сортировки данных по объему продаж выполните следующие действия:

Как результат мы получим отсортированный список клиентов по объему выручки.

Пример 3. Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

Для расчета места по объему выручки клиента Лудников ИП в регионе Восток рекомендую сформировать сводную таблицу, в которой будут отображены данные выручки по регионам и клиентам внутри этого региона.

В итоге мы получили таблицу, в которой отражены данные выручки клиентов в рамках каждого региона.

Для сортировки данных выполните следующие шаги:

В полученной таблице мы можем определить какое место занимает клиент Лудников ИП среди всех клиентов региона Восток.

Существует несколько вариантов для решения этой задачи. Вы можете перенести поле “Регион” в область “Фильтры” и в строчках разместить данные продаж клиентов, таким образом отразив данные по выручке только клиентов региона Восток.

Еще больше полезных приемов в работе со сводными таблицами Excel вы узнаете в практическом курсе “ Сводные таблицы в Excel“. Успей зарегистрироваться по ссылке!

Источник

Как создать сводную таблицу в excel 2010

Обрабатывать большие объемы информации и составлять сложные многоуровневые отчеты достаточно непросто без использования средств автоматизации. Excel 2010 как раз и является инструментом, позволяющим упростить эти задачи, путем создания сводных (перекрестных) таблиц данных (Pivot table).

Сводная таблица в Excel 2010 используется для:

Создавать сводные таблицы можно двумя способами. Рассмотрим каждый из них.

Способ 1. Создание сводных таблиц, используя стандартный инструмент Excel 2010 «Сводная таблица»

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

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

2. Откройте вкладку «Вставка» и выберите из раздела «Таблицы» инструмент «Сводная таблица».

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

3. В открывшемся диалоговом окне «Создание сводной таблицы» выберите только что созданную таблицу с данными или ее диапазон. Для этого выделите нужную область.

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

4. Далее нужно будет указать, где размещать отчет сводной таблицы. Удобнее всего это делать на новом листе.

5. После подтверждения действия нажатием кнопки «ОК», будет создан и открыт макет отчета. Рассмотрим его.

В правой половине окна создается панель основных инструментов управления — «Список полей сводной таблицы». Все поля (заголовки столбцов в таблице исходных данных) будут перечислены в области «Выберите поля для добавления в отчет». Отметьте необходимые пункты и отчет сводной таблицы с выбранными полями будет создан.

Расположением полей можно управлять – делать их названиями строк или столбцов, перетаскивая в соответствующие окна, а так же и сортировать в удобном порядке. Можно фильтровать отдельные пункты, перетащив соответствующее поле в окно «Фильтр». В окно «Значение» помещается то поле, по которому производятся расчеты и подводятся итоги.

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

Способ 2. Создание сводной таблицы с использованием инструмента «Мастер сводных таблиц и диаграмм»

Чтобы применить этот способ, придется сделать доступным инструмент, который по умолчанию на ленте не отображается. Откройте вкладку «Файл» — «Параметры» — «Панель быстрого доступа». В списке «Выбрать команды из» отметьте пункт «Команды на ленте». А ниже, из перечня команд, выберите «Мастер сводных таблиц и диаграмм». Нажмите кнопку «Добавить». Иконка мастера появится вверху, на панели быстрого доступа.

Мастер сводных таблиц в Excel 2010 совсем не многим отличается от аналогичного инструмента в Excel 2007. Для создания сводных таблиц с его помощью выполните следующее.

1. Кликните по иконке мастера в панели быстрого допуска. В диалоговом окне поставьте переключатель на нужный вам пункт списка источников данных:

2. После этого выбирается вид создаваемого отчета – «сводная таблица» или «сводная диаграмма (с таблицей)».

При выборе «Создать поля страницы» прежде всего придется указать диапазоны источников данных: выделите первый диапазон, нажмите «Добавить», потом следующий и т.д.

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

После завершения нажмите кнопку «Далее», выберите месторасположение будущей сводной таблицы – на текущем листе или на другом, нажмите «Готово» и ваш отчет, собранный из нескольких источников, будет создан.

Источник

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+ё, которая на листе показывает вместо значений формулы в ячейках):

которые подставляют данные посредством функций ВПР и ИНДЕКС из табличек:

Источник