Veseliy.4el
114К рейтинг
9990 подписчиков
12 подписок
126 постов
95 в горячем
Награды:
Диаграмма «план-факт»
Диаграмма «план-факт»
Создание диаграмм – это творческий процесс. Умение работниками красиво и понятно преподнести информацию, заслуживает уважения и высоко ценится нормальными руководителями. Не сомневаюсь в компетентности опытных экспертов Excel, но мои посты предназначены для повышения уровня знаний и навыков работы с Excel обычных пользователей, которым это может пригодиться при составлении отчётов или презентаций.
Итак, рассмотрим на примере небольшой и скучной таблицы создание диаграммы «план-факт», в которой визуально будет отображено выполнен план или нет.
Выделяем таблицу без заголовка и на вкладке «Вставка» - «Диаграммы» выбираем «График с маркерами»:
Дважды нажав левой клавишей мыши на диаграмму, в появившемся разделе «Конструктор» можно выбрать различные стили диаграммы.
Так как при просмотре диаграммы визуально идёт сравнение данных, добавим для наглядности «Полосы повышения и понижения» во вкладке «Добавить элемент диаграммы»
В результате получим диаграмму, которую можно подкорректировать на свой вкус в меню «Формат области диаграммы»:
Второй способ, это применение диаграммы с областями.
Для этого к имеющейся таблице добавим столбец «Разность», в который вписав формулу =C4-B4 получим разницу между фактом и планом.
Далее, удерживая Ctrl выделим содержимое столбцов A, B и D, на вкладке «Вставка» выбираем диаграмму «С областями и накоплением»:
Теперь выделяем столбцы «План» и «Факт», копируем их и вставляем в диаграмму, в результате получаем своеобразные горы:
Нам нужны лишь две верхние полосы. Выделяем первую полосу и нажатием правой клавиши в контекстном меню задаём зелёный цвет, вторую окрашиваем в красный.
Далее также нажатием правой клавиши мыши по диаграмме выбираем «Изменение типа диаграммы», где в открывшимся окошке для «плана и факта» выбираем «График с маркерами». Нажимаем ОК.
Затем выделяем нижнюю полосу и в контекстном меню выбираем «Нет заливки». В итоге получаем такую диаграмму:
Теперь можно навести красоту окрасив её в подходящие цвета, удалить в легенде ненужные пояснения, а также задать название нажав на заголовок и в строке состояния ввести формулу =Лист2!$A$1, в результате не придётся заново писать название диаграммы.
Показать полностью
9
Создание красивого отчёта
Допустим у нас имеется вот такая база данных по продажам, из которой нужно сделать отчёт:
Вы создали небольшую сводную таблицу (Вставка – Сводная таблица):
Но вашему начальнику не нравится внешний вид отчёта, и он хочет видеть что-то похожее на это:
То есть мы имеем несколько ощутимых трудностей:
1. Исходный внешний вид сводной таблицы не подходит - дизайн отчёта должен соответствовать корпоративным стандартам (цвета, логотипы, спарклайны, стрелки и т.д.). "Дорабатывать напильником" дизайн сводной - долгий и мучительный процесс. И не факт, что красота не слетит после пересчёта и обновления.
2. Из всей сводной для отчёта вам нужны не все данные, а только конкретные модели Ford по Питеру - придётся руками фильтровать.
3. Стандартные итоги в сводной нам не подходят, т.к. нужны суммы по выручке в зелёных ячейках, но среднее по месяцу в итогах - сводная так не умеет.
4. Полученные в сводной результаты - ещё не конец, нам необходимо произвести с ними какие-то дополнительные вычисления: пересчитать выручку в тысячах, добавить прогноз на апрель, сравнить этот год с прошлым. Многое из перечисленного в сводных или невозможно в принципе, или делается весьма мучительно и долго с помощью вычисляемых полей и объектов.
5. Нужно построить по результатам хитрую диаграмму (обычные сводные диаграммы имеют много ограничений).
Таким образом, перед нами стоит задача вытащить данные из сводной таблицы в другую таблицу - нужной нам конструкции, с дополнительными формулами и корпоративным дизайном. Сделать это можно разными способами.
1 способ. Прямая ссылка на ячейку в сводной
Это, что называется, решение проблемы «в лоб». Сделаем на отдельном листе заготовку отчёта:
Теперь в ячейку D7 можно вручную прописать ссылку:
=Лист1!B8
Где Лист1 – имя листа со сводной таблицей, а B8 – нужная нам ячейка в сводной с данными по продажам Ford Fiesta за январь.
При внешней простоте и очевидности у этого способа есть две проблемы:
1. Если сортировка моделей и дат в нашем красивом «отчёте для шефа» отличается от сводной, то скопировать созданную ссылку не получится и придётся делать их для каждой модели автомобиля персонально. А если нужно извлечь много данных, то придётся делать много ссылок вручную.
2. Завтра, после обновления, структура сводной таблицы может измениться - например, Fiesta может оказаться уже не третьей, а седьмой строкой, Focus переехать во вторую и т.д. И тогда все ссылки придётся переделывать.
2 способ. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Изящным решением всех этих проблем может стать функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA), которая умеет извлекать нужные нам данные из сводной, чтобы использовать их в других таблицах или расчетах.
Чтобы её использовать, убедитесь, что при выделении любой ячейки сводной таблицы на вкладке Анализ (Analysis) или Параметры (Options) в выпадающем списке Параметры (Options) включена галочка Создать GetPivotData:
Теперь выделите первую ячейку зелёного диапазона, введите знак "равно" и щёлкните по ячейке в сводной, которая содержит нужные данные, т.е. по B8, где лежит выручка Fiesta за январь. Вместо привычной ссылки Excel вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
("Выручка";'Сводная для отчета'!$A$4;
"Наименование";$C7;"Дата";1)
Давайте разберём её подробно:
1. Первый её аргумент ("Выручка") – это имя извлекаемого поля.
2. Второй (Лист1!$A$4) - это адрес первой ячейки сводной таблицы, откуда мы берём данные. Этот параметр нужен, т.к. на листе может быть несколько сводных и Excel должен понимать, из какой именно нужно вытащить число.
3. Все остальные аргументы начиная с третьего – это попарно название поля и его значение, т.е. в нашем случае это имя модели (Наименование="Fiesta") и временной период (Дата=1). Поскольку в сводной была применена группировка дат по месяцам, то в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ мы получили не имя месяца, а его номер. Если бы в исходной базе данных был столбец не с датой, а с названием месяца, то группировка была бы не нужна и вместо единички был бы просто "январь".
У этой функции есть несколько серьёзных преимуществ перед обычной ссылкой на B8, которая приводила бы, на первый взгляд, к тому же результату. Главный плюс в том, что если завтра после обновления в сводной таблице изменится количество строк/столбцов или Ford Fiesta станет не третьей, а пятой строкой, то нам об этом волноваться уже не придётся – функция корректно извлечёт нужное нам значение. Достаточно только обновить сводную правой кнопкой мыши – и наша красивая форма отчёта «для шефа» пересчитается автоматически.
Далее, замените в формуле "Fiesta" на $С7 (т.е. на ячейку с названием модели), а единичку на D$5 (т.е. ячейку с номером месяца) и допишите в конце формулы деление на 1000, т.к. нам нужно отобразить данные в тысячах. Затем нажмите на Enter и протяните формулу на оставшиеся зелёные ячейки.
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
("Выручка";'Сводная для отчета'!$A$4;"Наименование";
$C7;"Дата";D$5)/1000
Функция извлечёт из сводной нужные нам данные, заполнив нашу корпоративную форму отчёта.
Теперь с данными в диапазоне D7:F10 можно работать как с обычными формулами, а не как со сводной таблицей с её жёсткими ограничениями. Дальше можно спокойно считать любые итоги, динамику, прогнозы, строить любую диаграмму и т.д. Как украсить документ можно посмотреть здесь Минидиаграммы в ячейках
Формула для прогноза: =ПРЕДСКАЗ($G$5;D7:F7;$D$5:$F$5)
=FORECAST($G$5;D7:F7;$D$5:$F$5)
Формула для среднего значения: =СРЗНАЧ(D7:D10)
AVERAGE(D7:D10)
Формула для динамики: =D13/D14-1
Ссылка на файл в комментариях. Желаю всем без проблем сдать отчёты)) Кто знает способы лучше, можете поделиться опытом в комментариях.
Показать полностью
6
Скрытие и отображение ненужных строк и столбцов
Вот и наступил декабрь, у кого-то началась подготовка к празднованию Нового года, а кто-то, сидя в офисе, готовится к сдаче годового отчёта.
Декабрьские посты по Excel будут посвящены визуализации данных путём создания красивых и удобных диаграмм и один урок про сводную таблицу, которые я буду публиковать два раза в неделю в понедельник и пятницу, чтобы, если кому-то пригодятся эти способы, смогли применить их на практике. Итак, приступим))
Допустим у нас имеется вот такая маленькая таблица (в реальной жизни она бывает гораздо больше), в которой, для удобства в работе, мы скроем ненужные области, оставив лишь ту часть куда вводятся данные.
Пример принципа работы группировки таблицы в гифке:
Чтобы создать группировку таблицы выделяем нужное количество строк или столбцов, а затем выбираем на вкладке Данные – Группировать (Data – Group), либо нажатием сочетания клавиш Alt+Shift+стрелка вправо, а для разгруппировки Alt+Shift+стрелка влево, соответственно. В результате они будут сгруппированы.
В итоге у нас получится вот такая таблица
Нажатием на «+/-» или цифры можно сворачивать и разворачивать ячейки и столбцы. Группы можно делать вложенными одна в другую (до 8 уровней вложенности).
Также, если в таблице имеются итоговые строки или столбцы с функцией суммирования соседних ячеек, то есть шанс (не 100%-ный), что Excel сам создаст все нужные группировки в таблице одним движением – с помощью команды Данные – Группировать – Создать структуру (Data – Group – Create Outline). Однако, данная функция работает весьма непредсказуемо на сложных таблицах и порой выдаёт полный бред, но проверить можно.
Показать полностью
4
Самарканд
История Самарканда насчитывает более 2500 лет. Он видел разных великих правителей, пережил многочисленные войны, набеги и землетрясения. В современном Самарканде сохранились многочисленные древние памятники эпохи Тамерлана и его предков. Город, который называют Городом знаменитых теней притягивает внимание туристов и учёных. (Описание к фотографиям снизу)
Регистан - огромная площадь, замощенная обожженным кирпичом и булыжниками, на которой стоит ансамбль из трех величественных средневековых университетов, включенный в 2001 году в Список всемирного наследия ЮНЕСКО. Центром этого ансамбля, считающегося одним из величайших сооружений исламского мира, является медресе Тилля-Кари, слева от него построено медресе Улугбека, а справа - медресе Шердор.
Медресе Тилля-Кари («Отделанное золотом») начали строить в 1646 году на месте оставшегося еще со времен Улугбека караван-сарая Мирзои. По замыслу Ялангтуша бий Баходура, это медресе должно было своим главным фасадом замкнуть ансамбль на площади Регистан с северной стороны, а в самом здании должна была разместиться мечеть, чтобы студенты могли совершать молитву, не уходя из медресе.
Особенно впечатляет в медресе стенная живопись и богатая позолота внутренних стен главного помещения мечети.
Стены и купол полностью покрыты росписью в технике кундаль (мелкими растительными узорами) с огромным количеством позолоты.
Михраб — ниша в стене мечети. Она нужна для того, чтобы в ней молился имам, который должен стоять впереди остальных прихожан.
Свод потолка (круглая часть) украшен так, что кажется будто это купол, но на самом деле там ровный потолок
Боковая часть медресе Тилля-Кари.
К началу прошлого века большая часть облицовки медресе была разрушена. Восстановлением оригинального декора и сохранением уцелевших частиц занимались до конца 70-х годов.
Медресе Шердор ("Обитель львов") назвали так из-за изображенного на портале символа власти в Самарканде - барсов с солнцем на спине.
Шердор построено из кирпича, украшенного яркими орнаментами, а башни и стены полностью расписаны различными цитатами из Корана на арабском языке.
Мозаика над главным входом в Шердор.
Двор Медресе Шердор.
Медресе Улугбека было построено внуком Великого Тамерлана в 1417-1420 годах и стало самым прекрасным из всех его творений. Гордые громады древних стен и башен медресе Улугбека возвышаются над легендарной площадью Регистан.
Главный фасад медресе, обращенный к площади Регистан, выделен величественным порталом. Орнамент из резной мозаики, майоликовой плитки, резного мрамора, облицовка здания украшена каллиграфическими письменами изумительной работы - художественное совершенство в наивысшей его степени.
После окончания строительства медресе Улугбека стало одним из самых престижных университетов мусульманского востока 15 века. Именно благодаря ему Самарканд получил статус крупнейшего научного центра.
В учебном заведении читались лекции по математике, геометрии, логике, естественным наукам и богословию, а преподаванием занимались самые известные учёные того времени.
Изначально никакого архитектурного ансамбля вокруг Регистана не было. На открытом пространстве власти собирали народ для оглашения указов, здесь велась торговля, проводились праздники, публичные казни и сборы армий.
Несколько раз в месяц на площади Регистан проводят лазерные шоу.
Шоу рассказывает об истории Узбекистана, проводя зрителя от древности до современности. В нем отражены важнейшие моменты развития страны, ее культура и роль в мировой истории.
Мемориальный комплекс Шахи Зинда — это ансамбль из одиннадцати мавзолеев для знати. Их постепенно пристраивали друг к другу в течение 14–15 веков.Но основным мавзолеем, откуда и начинается некрополь, считается мнимая могила двоюродного брата пророка Мухаммеда - Кусама ибн-Аббаса. Его так и называли «Шахи Зинда», что в переводе с персидского означает «Живой Царь».
Средняя группа мавзолеев — это постройки времен Тимура, где похоронены его родственники, видные военные и религиозные деятели. Здесь же были найдены руины построек 11 века.
Все мавзолеи комплекса Шахи Зинда отмечены единым композиционным решением. Это квадратные купольные здания, вход в которые выделен портиком. Поражает богатейший архитектурный декор зданий, в котором использованы поливные кирпичи, майоликовые плитки, резная мозаика.
При раскопках на территории комплекса нашли более старые строения 11–12 веков, так что история этого места началась гораздо раньше 14 века. Шахи Зинда — единственный в Самарканде археолого-архитектурный памятник, где, в том или ином виде, отразилась почти 25-вековая история города.
Мавзолей Ширин-Бека-ака, здесь покоится сестра Тимура.
Этот мавзолей украшен изящной наборной изразцовой мозаикой. При помощи работы с цветом мастера создали объёмное изображение: синие и голубые тона создают глубокий фон, а красный, желтый, оранжевый и белый цвета — передний план. Ещё стены украшает надпись, авторство которой приписывают Сократу: «Поистине люди мира этого подобны птицам радующимся».
Купол мавзолея Шади-Мульк-ака, построенного в 1372 году для племянницы Амира Тимура. Это первая постройка времени правления Тамерлана, не только в самом комплексе, но и во всём Самарканде.
В древности арабские захоронения не декорировали цветной плиткой и росписями, поэтому помещение для паломников, прибывших к могиле Кусама ибн Аббаса, пришлось украшать в более позднее время.
Мечеть Биби-Ханум была воздвигнута по приказу Тамерлана после его победоносного похода в Индию. Строительство было начато в мае 1399 года. Месторасположение будущей мечети выбирал сам Тимур. В строительстве были задействованы мастера из различных стран: Индии, Ирана, Хорезма, Золотой Орды. К сентябрю 1404 года основная часть комплекса была уже построена. Во дворе мечети могли одновременно молиться 10 тысяч человек.
Мечеть Биби Ханым задумывалась как сооружение, которое должно было намного превзойти все аналогичные постройки. Однако средневековые мастера не владели необходимыми для строительства столь масштабных зданий технологиями и материалами, к тому же они не учли риск сейсмичности. Глубокие фундаменты из рваного камня и кирпичные стены не спасли Биби Ханым - еще при жизни Тимура здание стало разрушаться, и на головы молящихся сыпались камни из потрескавшегося купола.
Сейчас из всего комплекса можно увидеть только пять уцелевших построек: портал, большую мечеть в глубине двора, две малые мечети и минарет. Кроме того, центр двора до сих пор украшает огромный мраморный пюпитр для священного Корана, изготовленный при Улугбеке.
Но благодаря кропотливой работе историков, археологов и искусствоведов мы имеем возможность представить первоначальный облик Биби Ханым.
Ещё больше фотографий новосибирского фотографа Славы Степанова по ссылке
Показать полностью
25
Доломитовые Альпы в Италии
Показать полностью
1
Использование срезов для поиска и фильтрации
В этом уроке я покажу красивый, простой и удобный способ фильтрации данных с помощью срезов, который можно применять в больших таблицах.
Для этого, уже имеющуюся таблицу с данными, необходимо отформатировать как таблицу «Главная» - «Форматировать как таблицу» (Home – Format as Table), где выбираем любой дизайн. В моей таблице 3000 строк, ссылку на файл оставлю в комментариях.
Теперь, поставив курсор на ячейку в шапке, переходим на появившуюся динамическую вкладку Работа с таблицами: Конструктор (Table Tools: Design) и нажимаем кнопку Вставить срез (Insert Slicer). В открывшемся окне отмечаем флажками названия столбцов, по которым будем фильтровать.
Появившиеся срезы размещаем над таблицей в удобном для вас порядке.
Если срез содержит очень много элементов (кнопок), то их можно расположить в несколько столбцов, как на срезах «Месяц» и «Бригадир». Для этого выделите срез и увеличьте для него количество столбцов на вкладке Инструменты для среза: Параметры (Slicer Tools: Options). Здесь же можно выбрать Стили срезов.
Дополнительные параметры выделенного среза можно настроить с помощью кнопки Настройка среза (Slicer Settings) на той же вкладке:
Удерживая клавиши Ctrl или Shift можно выделять сразу несколько элементов среза. Для сброса фильтра нажимаем кнопку Удалить фильтр (Clear Filter) в правом верхнем углу среза.
Также, чтобы скрыть срезы при печати документа нажимаем правой клавишей мыши по срезу и в разделе Размер и свойства - «Свойства» снимаем галочку с «Выводить объект на печать».
Либо, выделив область таблицы, в параметрах печати можно выбрать «Напечатать таблицу» или только «Выделенный фрагмент».
Примечательно то, что при добавлении новых данных в таблицу, в срезах автоматически будут появляться новые значения.
Показать полностью
5