Tornado Chart Excel Template – Free Download – How to Create
But unless you use our Chart Creator Add-in that allows you to build advanced dynamic Excel charts—like tornado charts—in a single click, you will need to create this chart manually.
In today’s ultra-newbie-friendly tutorial, you will learn how to design a simple tornado chart from scratch, even if you have never used Excel for data visualization before.
Without beating around the bush, let’s roll up our sleeves and get down to work.
Getting started
Bill Gates once famously said, “Content is king.” In Excel, data runs the show.
Which brings us to the data for your tornado chart.
For illustration purposes, we are going to compare gross online and retail sales of each tea blend for a fictitious tea wholesale company. A tornado chart will help us neatly display our comparative analysis to unearth hidden opportunities for expanding our tea empire—this time around, however, in a peaceful way.
Here is how you need to prepare your dataset:
A quick rundown on each element:
- Column A: This column determines how many bars your tornado chart will have.
- Column B and C: These are the two variables used for comparison.
Step #1: Sort the rows of the table by column B in ascending order.
First things first: sort the rows by column B, listing the values from smallest to largest. That way, the largest bar will be at the top of the chart while the smallest will be placed at the bottom.
- Highlight all the chart data.
- Click the Data
- In the Sort & Filter group, select the “Sort”
- In each dropdown menu, sort by the following:
- For “Column,” select “Online” (Column B).
- For “Sort On,” select “Values” / “Cell Values.”
- For “Order,” select “Smallest to Largest.”
Step #2: Create a clustered bar chart.
Now, you need to put together a clustered bar chart that will serve as the backbone for the tornado chart.
- Select your chart data.
- Go to the Insert
- Click the “Insert Column or Bar Chart” icon.
- Choose “Clustered Bar.”
Once the clustered bar chart appears, you can remove the chart legend (typically at the bottom of the chart). Right-click the legend and select “Delete.”
Step #3: Add a secondary axis.
Adding a secondary axis will allow us to reposition the bars, molding the chart into a tornado shape.
First, right-click on any column B chart bar (any of the blue bars) and choose “Format Data Series.”
In the task pane that appears, make sure you are in the Series Options tab. Under “Plot Series On,” click the “Secondary Axis” radio button.
Step #5: Change the secondary axis scale.
Once there, modify the primary and secondary axis scales for the metamorphosis to take place. Let’s dive into the juicy stuff, dealing with the secondary axis first.
Select the secondary axis (the set of numbers at the top of the chart) and right-click on it. Then choose “Format Axis.”
In the Axis Options tab, set the Minimum Bounds value to -500 and the Maximum Bounds value to 500.
An important note: make sure you check the “Values in reverse order” box.
Easy Excel Automation
Add Excel automation to your workbook with just a few clicks.
Step #6: Change the primary axis scale.
Rinse and repeat: apply the same process to the primary axis (the set of numbers at the bottom). Set the exact same minimum (-500) and maximum (500) values for the primary axis—but leave the “Values in reverse order” box unchecked.
At this point, you might be thinking to yourself, “Wait a minute, how do I know what value to use to set the right range if my data differs between the two groups?” Don’t panic. Here’s a good rule of thumb: pick the largest value in your data chart and round it up.
In this case, the largest value in our list is 500 (with in-store Earl Grey). Therefore, that is the number we used to determine the range of our minimum and maximum values.
Step #7: Remove the secondary axis and change the primary axis scale number formatting.
It’s time to wave goodbye to the secondary axis as we no longer need it. Right-click on the secondary axis and select “Delete” to remove it.
Now, you may have noticed that some of the numbers on the primary axis scale are negative. Fortunately, it doesn’t take a rocket scientist to solve the issue:
- Right-click on the primary axis and choose “Format Axis.”
- Click the “Axis Options” icon.
- Open up the “Number” dropdown.
- Select “Custom” in the “Category” dropdown menu.
- Pick “###0;###0” from the “Type”
- Enter “###0;###0” into the “Format Code” field.
- Close the pane.
Step #8: Move the category axis labels to the left.
Right-click on the vertical category axis labels (in our case, the product names) and choose “Format Axis.”
Next, in the “Format Axis” pane, go to the “Axis Options” tab, move down to the “Labels” section, and set the “Label Position” to “Low.” You can also make the labels bold (from the Home tab at the top of the Excel window) so they stand out more.
Step #9: Add the data labels.
To make our tornado chart more informative, add labels representing your actual data values.
Right-click on any blue bar on the chart and choose “Add Data Labels.”
Do the same for the other half of the tornado chart.
Step #10: Move the data labels to the center.
Polishing up the final details, you can improve what you already have even more by moving the labels to the center of the chart. Here is how you do it.
Right-click the label and click “Format Data Labels.”
In the “Format Data Labels” pane, click the “Label Options” icon. Then set the “Label Position” to “Inside Base.”
Once there, color the text white and make it bold.
Repeat for the other half of the tornado chart.
Step #11: Resize the chart bars.
Just one last thing and we are done with the tornado chart for good. Those bars look skinny, so let’s pump them up a bit to make the chart more pleasing to the eye.
Right-click on any blue bar and select “Format Data Series.”
In the “Series Options” tab, set the “Gap Width” value to 10%. Do the same thing to the other side of the chart. As a final adjustment, update the chart title.
Ta-da! You are all set now. You have just added another tool to your data visualization belt.
How to Create a Tornado Chart in Excel
An Excel Tornado Chart is useful for those who want to analyze their data for better decision-making. The best use of it is for sensitivity analysis but you can use it for comparison purposes. That’s why it is a part of our advanced charts list on Excel Champs.
What is an Excel Tornado Chart
The Excel Tornado Chart is like a two-sided bar chart (looks like a tornado) where you have two data bars that are opposite to each other and make it easy to compare both of them. As I said, it’s a useful tool for sensitivity analysis, but you can use it where you need to compare values.
It’s a special type of bar chart in which data is sorted vertically from highest to lowest. With this type of shape, it looks like a tornado.
How to Create a Tornado Chart in Excel
In Excel, there is no default option to create a tornado chart but you can use the default bar chart and customize it. Today, in this post, we will learn to create it.
Make sure to download this sample file from here to follow along. To create a tornado chart in Excel you need to follow the below steps:
- First of all, you need to convert data of Store-1 into the negative value. This will help you to show data bars in different directions. For this, simply multiply it with -1 (check out this smart paste special trick, I can bet you’ll love it).
- After that, insert a bar chart using this data. Go to Insert Tab ➜ Charts ➜ Bar Chart and with this, you’ll get a bar chart like below where you have two sides (one is side is for positive values and another is for negative).
- From here, select the axis label and open formatting options and in the formatting options, go to axis options ➜ Labels ➜ Label Position. Change label position to “Low”.
- Next, you need to change the axis position in reverse order. This will adjust bars from both of the sides and for this, go to Axis options ➜ Axis position ➜ tick mark “Category in reverse order”.
- Now you need to change the series gap and gap width. This will help to streamline data bars with each other and for this go to series options -> Change series overlap to 100% and gap width to 10%.
- And you need to change the number formatting of the horizontal axis. And for this, go to the Axis Options ➜ Number ➜ select custom ➜ paste following format and click add.
- In the end, just like the above step, you need to change the format for data labels for Store-1 so that it doesn’t show the negative signs and for this go-to label options ➜ Number ➜ select custom ➜ paste following format and click add.
Congratulations, now you have your first tornado chart in your worksheet, just like below.
dynamic chart range for your chart so that you don’t need to update it again and again when you need to update the data.
Create a Tornado Chart using Conditional Formatting
Unlike the way we have used the above method, you can use conditional formatting as well. To create a tornado chart with conditional formatting:
- You just need to plot your data as I have in the below snapshot. As you can see, you have data for both of the stores (store 1 and store 2) in the table.
- So first of all, align store 1 column to right and store 2 to left.
- The next thing is to create apply conditional formatting. You have to apply it to both of the columns one by one.
- From here, select the store 1 one column and go to the Home Tab ➜ Styles ➜ Conditional Formatting ➜ Data Bars ➜ More Rules.
- In the “More Rules” dialog box, you need to do define below things to create data bars:
- Color: Whatever color you want.
- Border: Solid (if you want)
- Direction: Right to Left.
- Color: Whatever color you want
- Border: Solid (if you want)
- Direction: Left to Right.
Use REPT Function to create an In-Cell Tornado Chart in Excel
I love to do things in a different way or try new unconventional ways to do old things. At this point, you know how to create a tornado chart with a bar chart and with conditional formatting as well.
But, there is one more thing which we can try, that’s the REPT function (If you want to learn more about it, check from this function page). To create a TORNADO chart with REPT function you can follow the below steps:
- First of all, you need to set up your data just like we have in the below snapshot. Here you have a column with product names and two columns for each store (one is for values and second for values).
- From here we need to insert REPT function in the data bar column of the store 1 and for this insert the below formula in the first cell and drag it down to the last cell.=REPT(“|”,D3/10)
- After that select the entire column and change the following things:
- The font to “PlayBill” (with this font you can have a look of data bar).
- Change the width of the column equivalent to the largest data bar or more.
- Change font color to orange.
- Change text alignment to right to left.
- Next, you need to follow the same method to add data bars to the store2 column and change the following things:
- The font to “PlayBill” (with this font you can have a look of data bar).
- Change the width of the column equivalent to the largest data bar or more.
- Add font color to
- Change text alignment to the left to right.
Sample File
Conclusion
Some people call it tornado diagrams, a useful tool for decision-making by comparison. You can compare two different items or a single item for the different periods.
So that’s the entire story about tornado charts and YES, you just need 7 STEPS to create it. One thing I just forget to tell you is that you can also use conditional formatting or a REPT function to create an in-cell tornado chart.
I hope you found it useful and it will help you take your skill to next level.
Now tell me one thing. Have you ever used a tornado chart? Please share with me in the comment section, I’d love to hear from you. And please don’t forget to share this tip with your friends. I’m sure they will appreciate it.
Диаграмма «Торнадо»
Диаграмму «Торнадо» можно применять для демонстрации количественных данных городского и сельского населения, прибывших и убывших, продаж товаров фирмы и её конкурента, взрослых и детей, мужчин и женщин и т.д.
Рассмотрим составление диаграммы на примере таблицы мужчин и женщин:
По сути, всё кажется простым, но есть пара нюансов, которые нужно учесть при составлении диаграммы. Итак, выделив нашу таблицу, на вкладке «Вставка» выбираем диаграмму «Объёмная линейчатая с накоплением».
Как видим, все данные слились в одну строку, что не даёт полного представления о картине и ещё больше запутывает:
Чтобы этого избежать, мы сделаем цифры в столбце «Мужчины» со знаком минус. Для этого вводим в любой ячейке «-1», затем копируем её и выделив столбец «Мужчины», нажав на правую клавишу мыши, вставляем с помощью специальной вставки. В открывшемся окне выбираем «значения» и «умножить», в результате все цифры станут со знаком минус.
Таблица сразу примет нужный нам вид:
Остаётся один момент, который эстетично портит вид диаграммы – это знак минус впереди цифр:
Чтобы убрать минусы, выделяем столбец «Мужчины», открываем формат ячеек, выбираем «Все форматы» и указываем маску #;#;0
В результате у нас получится довольно симпатичная диаграмма, которую можно разукрасить в любые цвета:
689 постов 15K подписчиков
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.
Не считаю такой вид полезным по одной причине:
Первая указанная вами гистограмма с накоплением дает представление сразу о трех показателях:
и самое главное:
И ничего она не запутывает — все понятно и наглядно. В вашем же варианте в угоду красоте теряется один из важных показателей
Диаграмма «спагетти» в Excel
Ребятушки, подскажите, можно ли сделать в эксель диаграмму спагетти, типа такой, которая на рисунке?
Нужно отобразить передвижение из одного кабинета в другой
5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1
Друзья, всем привет.
Сегодня хотел бы поговорить с вами о том, чего в Excel делать не нужно, чтобы в будущем избавить себя от головной боли и перманентных хлопков в нижней части тела. Планировал сначала про 10 вещей написать, но при подготовке материала понял, что в рамках одной статьи слишком уж много получится. Поэтому решил разбить на 2 части.
Важное примечание!
Сразу оговорюсь, что речь здесь скорее пойдёт не про то, как исправлять уже кривые данные, а про то, как правильно первоначально их оформлять. Так что статья больше ориентирована не на тех, кто работает с готовыми данными, а собирает все свои таблицы «с нуля».
1 — Объединение ячеек.
Один из самых страшных грехов при создании таблиц. Объединение ячеек влечёт за собой трудности с сортировкой, фильтрацией, созданием сводных, форматированием, протягиванием формул и т.д. Вот пример:
Ячейки в заголовках, где написаны месяцы, объединены. Если мне нужно будет просуммировать продажи за январь-февраль, то при переходе с 9 на 10 строку диапазон будет расширен до неприличного и ненужного мне. Придётся либо вручную его менять, либо через точку с запятой указывать несколько отдельных диапазонов.
Как сделать лучше.
Если уж так хочется, чтобы название месяца было по центру, то поступаем следующим образом.
Ничего не объединяя, пишем название месяца в первую ячейку (В2, если речь про ЯНВАРЬ), далее выделяем ячейки, в которых январь должен быть виден (В2:F2) — правая кнопка мыши — Формат ячеек (или CTRL + 1 с клавиатуры):
Далее вкладка Выравнивание — по горизонтали — по центру выделения:
К остальным заголовкам можно уже применить «Формат по образцу» (кстати, для многократного применения формата нужно нажать на иконку два раза левой кнопкой мыши), чтобы не проделывать все действия заново. Визуально получим то же самое, но без богомерзкого объединения ячеек.
2 — «Правильная» таблица
Этот пункт, безусловно, спорный, но я его всё же включил. И, в первую очередь, совет касается тех таблиц, но основе которых вы в дальнейшем планируете строить сводные таблицы. Сделали мы вот такую таблицу по месяцам за несколько лет:
Красиво, хорошо, душа радуется. Но грусть нас постигнет, если мы захотим потом построить сводную таблицу на основе этих данных. Нет, я не говорю, что это невозможно. Построим. И даже худо-бедно сможем годы просуммировать или месяцы. Но если бы таблица содержала все 12 месяцев или период не в 3 года, а лет в 5-6 хотя бы, то всё было бы куда хуже.
Как сделать лучше.
Всё просто — не делайте трёхмерные таблицы. Плоская таблица — вот то, что нам нужно. То есть выглядеть та же самая таблица должна вот так:
Теперь и сводную строить сплошное удовольствие, и сортировать/фильтровать данные куда проще. Про то, как трёхмерные таблицы переделывать в плоские не вручную, здесь речь не пойдёт.
3 — «Правильные» даты
Сразу заявлю, что я понимаю боль тех, кто сталкивается с уже волшебно введёнными датами. Но речь про то, как делать это правильно изначально. С датами в Excel вообще всё весело. Я хочу рассказать про самый, с моей точки зрения, чудесный способ ввод дат. Делаем мы таблицу, дошло дело до дат. И мы, как ответственные, трудолюбивые работники выбираем ячейку, и, как мама учила, начинаем вводить «29 марта 2023 г.». Целый день так работали в поте лица, устали. Довольные проделанной работой и гордые за себя. Но если вдруг нас просят отфильтровать данные только по одному конкретному месяцу или посчитать разницу между двумя датами, то мы впадаем в депрессию, потому что «тупой иксель» не фильтрует, не считает:
Ну не умеет он так даты воспринимать. Все мы неидеальны.
Как сделать лучше.
Вводить даты, используя православные разделители, которые Excel понимает. Их три:
«-» — дефис/тире/минус (кому как удобнее)
А потом уже через формат ячеек установить то, что тебе нужно.
Про то, как исправлять уже сотворённое, думаю, отдельную статью напишу. Хотя в комментариях к предыдущим статьям разные способы мелькали. Соберу их в одном месте.
4 — Цветовое оформление таблицы.
Есть у нас унылые данные, никак не оформленные, а душа требует праздника и цвета. Начинаем аккуратно выделять столбцы и строки, усердно закрашиваем. Кто-то так вообще не церемонится, и выделяет сразу весь столбец и уверенно нажимает на ведро с краской:
А что? А вдруг новые данные появятся, я вот сразу этот момент продумал. Я — молодец. Потомки спасибо скажут. Нет, не скажут. Во-первых, это некрасиво. Во-вторых, это лишняя нагрузка на сам Excel. В-третьих, есть способ куда удобнее и практичнее.
Как сделать лучше.
Форматировать данные как таблицу.
Встаём в любую ячейку таблицы, Главная — Стили — Форматировать как таблицу. Выбираете цвет своего настроения (если ничего не хотите, то потом можно поменять или выбрать Создать свой стиль таблицы), проверяем диапазон и галочку «Таблица с заголовками» — Ок. Всего 4 (. ) нажатия на кнопку мыши и готово. В этих наших интернетах это называется «умная» таблица. Инструмент обсуждаемый. Кто-то их боготворит, кто-то проклинает. Про всё здесь не расскажешь (наверное, отдельную статью писать придётся). Затрону только пару вещей. «Умная» таблица резиновая. То есть все столбцы и строки, которые мы будем добавлять вручную или копированием и вставкой, сразу будут отформатированы как и всё остальное в столбцах (включая формулы и форматы). Выделить столбец можно либо встав в любую ячейку столбца и нажав CTRL + пробел, либо наводим курсор на верхнюю часть ячейки с заголовком, ловим момент, когда он превратится в чёрную (чёрные стрелки важны) стрелку вниз, и нажимаем левую кнопку мыши.
5 — Ручное закрашивание данных
Нужно нам красивый табель сделать. Есть сотрудники, есть дни недели. Рабочие дни усердно и кропотливо закрашиваем через ведро с краской. Получается вот так:
Вроде бы, и хорошо. А как теперь посчитать, сколько человеки наши в неделю работали? Встроенные функции по цветам считать не умеют. Можно, конечно, пользовательскую функцию написать на VBA, но.
Как сделать лучше.
Условное форматирование плюс немного магии пользовательских форматов. В ячейки, где человек работал, ставим «1». Выделяем весь диапазон, где хотим что-то закрашивать. Главная — Стили — Условное форматирование — Правила выделения ячеек — Равно. Равно 1, в окошке справа выбираем пользовательский формат. На вкладке «Заливка» выбираем нужные нам цвета. А вот самое интересное происходит на вкладке «Число». Выбираем «(все форматы)», в поле «Тип» прописываем следующий формат «;;;» (без кавычек три точки с запятыми). Если кратко, то каждой точкой с запятой мы задаём формат для положительных, отрицательных, нулевых и текстовых значений. А так как ничего конкретного не прописали, Excel никак данные отображать и не будет. То есть физически в ячейке будет «1», но визуально ячейка будет пустой. Мало того, что мы теперь можем подсчитать количество рабочих дней с помощью СУММ или СЧЁТ, так ещё и при внесении изменений всё у нас будет меняться. Кстати, если делать это с «умной» таблицей, то при добавлении новых сотрудников созданное нами правило будет работать и на них.
На этом, пожалуй, первую часть закончу. Как всегда, огромное спасибо всем, кто осилил данный материал, потратив своё драгоценное время и внимание. Надеюсь, было полезно. Делитесь своими соображениями по поводу того, чего делать НЕ НУЖНО, работая с данными в Excel. Совместными усилиями будем искоренять нечестивые данные.
Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции
Друзья, всем привет. В прошлой статье я рассказывал про мгновенное заполнение в Excel. Вещь интересная, но, как некоторые пользователи отмечали в комментариях (и вполне обоснованно), мгновенное заполнение не всегда подходит для решения тех или иных задач. В первую очередь, это касается данных, которые в таблице часто меняются. Мгновенное заполнение не реагирует на изменение данных. В этом случае лучше использовать функции. Да, это дольше, да, это будет выглядеть страшно (далее поймёте, почему), но зато один раз сделал и потом наслаждаешься плодами своих трудов. В качестве примера я решил взять тестовое задание, которое давно проходил при приёме на работу в одну довольно известную компанию.
Важное уточнение! В Excel можно решать очень сложные задачи. Но должна быть хоть какая-то логика. Чем сложнее логика, тем сложнее будут сочетания функций, набор инструментов и т.д. Потратишь много времени, но решишь. А вот если логики нет, то не ждите чуда. Чудите сами, как говорится.
Что же, давайте приступим.
Есть столбец с текстом:
Нам необходимо в отдельные столбцы разнести код и наименование.
Первое, на что обращаем внимание, есть ли здесь закономерность? С кодом всё понятно. Нужно извлечь текст до первого пробела. Отлично. Находим порядковый номер первого пробела с помощью функции НАЙТИ (FIND):
Далее всё просто. С помощью функции ЛЕВСИМВ (LEFT) извлекаем всё, что до пробела, отнимая от порядкового номера пробела единицу:
Я показал пошаговое решение. Но это можно всё сделать и в одной формуле. Вместо D2 вставляем НАЙТИ (FIND):
Перейдём к более интересному — наименование. И снова начинаем с логики. Нужно извлечь текст между первым и последним пробелом. В Excel нет функции, которая по щучьему велению, по нашему хотению извлекает текст между первым и последним символом (например, пробелами). Придётся хитрить. Сначала пошагово, потом впихнём всё в одну ячейку (и оторвёмся по полной).
Надо подсчитать, а сколько в принципе у нас пробелов в тексте. Сделать это можно вот как:
ДЛСТР (LEN)(А2) (как вам название?) — подсчитывает общее количество символов в ячейке. ДЛСТР(ПОДСТАВИТЬ(A2;» «;»»)) — подсчитывает количество символов без пробелов. От них мы избавились благодаря функции ПОДСТАВИТЬ (SUBSTITUTE), которая заменила пробелы на пусто.
Теперь очень мудрёный шаг. Нам надо заменить последний пробел на какой-нибудь другой произвольный символ:
ПОДСТАВИТЬ (SUBSTITUTE)(A2;» «;»*»;E2) как раз это и делает. Даём ей исходный текст, что нужно найти, на что заменить, и какой символ по порядку (именно для этого нам нужно было узнать, сколько всего пробелов).
Зачем нам это? Немного терпения, друзья, сейчас всё будет.
Логика решения меняется. Теперь нам надо извлечь текст между первым пробелом и звёздочкой. А вот это реализовать уже можно. Находим порядковый номер звёздочки:
ПСТР (MID) — как много в этом слове. А функция, вообще-то, полезная. Указываем ей ячейку, порядковый номер, с которого хотим начать извлекать символы, и само количество символов.
А2 — понятно, ячейка с текстом.
D2+1 — начиная с какого символа будем извлекать (первый пробел + 1)
G2-D2-1 — количество извлекаемых символов. Порядковый номер звёздочки — порядковый номер первого пробела — 1 (чтобы исключить лишний символ в конце).
А теперь комбо! Всё в одной ячейке:
Говорил же, что будет страшно, а вы не верили.
Вот и решение задачи. Вообще, моё мнение, такие штуки в любом случае нужно сначала решать пошагово, а потом только впихивать всё в одну ячейку. Но тут уж каждый сам для себя решает.
В файле, который идёт к этой статье, есть ещё один готовый пример. Это тоже вполне себе реальная задача, которую слушатели предложили решить во время корпоративного обучения. Исходный текст нужно было переделать в то, что указано в «Должно быть»:
Здесь уже расписывать не буду, а то ещё одна «простыня» получится. Кому интересно, посмотрите. Там, правда, уже не так всё подробно по шагам расписано. Но я в вас верю!
Можно и ещё более сложные конструкции использовать. В комментариях к прошлой статье были варианты с обработкой пустых ячеек. Про всё не расскажешь. Если честно, я перед собой такую цель и не ставлю. Главное, что я хотел вам показать — это то, что прежде, чем кидаться искать функции, посмотрите, а есть ли вообще логика в том, что вы хотите? Если есть, тогда и решение найдётся.
Как всегда, огромное всем спасибо, кто потратил своё драгоценное время и внимание на чтение данного материала. Надеюсь, что-то вам обязательно пригодится. Пишите комментарии, делитесь своими мыслями, решениями и вопросами. Да пребудет с нами сила ИКСэль.
П.С. недавно проводил мастер-класс по разным штукам в Excel, про которые здесь почти не писал. Например, рассказал про то, что такое расширенный фильтр.
Мгновенное заполнение в Excel — магия в чистом виде
Друзья, всем привет. Сегодня хочу рассказать вам про мгновенное заполнение в Excel.
Ссылка на файл, чтобы можно было потренироваться — https://disk.yandex.ru/i/HyW0N215F6CuUg
Возможно, многие с ним знакомы заочно. Наверняка же замечали, что когда вручную заполняешь какие-то значения в ячейках, то с переходом к следующей ячейке при вводе символов Excel порой выдаёт вот такой список:
Так вот это и есть мгновенное заполнение во всей своей красе. Да, иногда это раздражает, потому что тебе это не нужно. Но в большинстве случае польза мгновенного заполнения огромна.
Извлечение данных
Предположим, у нас есть вот такой столбец с текстом:
Нам нужно извлечь отдельно номер договора и дату. Это можно сделать с помощью инструмента «Текст по столбцам». Правда, потом придётся от символа «№» ещё избавляться. А вот мгновенное заполнение справится с этим намного быстрее. Просто вводим справа от текста в первую ячейку номер договора (1), нажимаем Enter. Далее возможны два варианта.
Вариант 1. Вручную вводим в ячейку первую цифру второго договора (2). Excel предлагает свои варианты, жмём Enter — PROFIT!
Вариант 2. После того, как перешли ко второй ячейке, сразу нажимаем сочетание Ctrl + E (Е английская, конечно). Именно это сочетание отвечает за запуск мгновенного заполнения. Аналогично с датами. Вводим в ячейку С2 дату первого договора — Enter — Ctrl + E — наслаждаемся результатом.
ОЧЕНЬ ВАЖНАЯ ЧАСТЬ СТАТЬИ.
Так как же это работает? Всё довольно просто. В первой ячейке мы задаём образец, чего хотим получить, далее Excel распознаёт нашу логику и заполняет остальные ячейки по образу и подобию.
Ух ты! И так будет работать всегда?! Строго говоря — нет. Иногда, Excel не может с одной ячейки распознать логику. В этом случае нужно вручную заполнить не одну, а две, три, четыре (если случай совсем запущенный) ячейки. И только после этого нажимать Ctrl + E. Чем больше ячеек заполняешь, тем выше вероятность того, что твоя логика будет верно распознана могучим интеллектом Excel. Порой мгновенное заполнение не справляется с поставленной задачей:
Даты в первом столбце указаны в формате ГГГГ-ММ-ДД. При попытке привести их в формат ДД-ММ-ГГГГ получается вот такая «красота». Поэтому не поленитесь после того, как все ячейки будут заполнены, пробежаться по ним, а тот ли в них результат, который ты ожидал увидеть.
Образцы вводите в соседнем столбце от источника (можно справа или слева). Не «убегайте» далеко от данных, результат может быть непредсказуемым или вообще ничего не будет.
Ещё одно важное дополнение: мгновенное заполнение работает в версиях Excel 2013 и выше.
Теперь, когда с пояснениями закончено, давайте посмотрим, на что ещё способен этот удивительный инструмент.
Извлечение только чисел из столбца
Если нам из «красивого» столбца, в котором есть значения вроде «123руб», «55 рублей» и так далее, нужно извлечь только цифры, то вы уже знаете, что нам поможет:
В данном конкретном случае я прописал вручную две первых ячейки, иначе Excel не понимал, что нужны только числа.
Работа с текстом
В столбце указаны Имя и Фамилия. Нам нужно получить результат в виде «Имя Ф.» В первой ячейке вводим образец — Enter — Ctrl + E:
Кстати, если попробовать получить Фамилия И., то будьте внимательны. Если прописать два примера, потом начать вводить третий, то появляется довольно забавный список:
Но если не начинать вводить в третью ячейку текст, а сразу нажать на Ctrl + E, то всё будет нормально. Раз на раз не приходится. Временами мгновенное заполнение ведёт себя очень странно.
Извлечение части сплошного текста
Необходимо разбить слипшийся текст на части. Вводим в первых двух ячейках образец — Ctrl + E:
С номером поступаем аналогично.
Сбор текста
В отдельных столбцах есть различная информация, которую необходимо собрать в одно предложение. Обратите внимание, что порядок столбцов для мгновенного заполнения роли не играет. Прописываем предложение в первой ячейке — Enter — Ctrl + E:
На этом статью я хотел бы завершить. Уверен, я перечислил далеко не все чудесные возможности мгновенного заполнения. Буду вам благодарен, если в комментариях поделитесь своими способами применения этой чудесной штуки.
В качестве небольшой рекламы позвольте оставить здесь ссылку на мастер-класс, который я буду проводить 9 марта. Кто хочет узнать ещё несколько полезных приёмов при работе в Excel (там почти не будет того, о чём я писал здесь), а ещё хочет услышать чуть больше про то, где я работаю, записывайтесь — Полезные приемы при работе в Excel. Часть 2 (specialist.ru)
На этом всё. Как обычно, спасибо огромное всем, кто потратил своё драгоценное время и осилил данное полотно. Надеюсь, было полезно. Видео по данной статье обязательно появится на моём канале — (36) Андрей Митрохин — YouTube
Таблица подсчета розеток/выключателей/рамок.
Когда я работал в магазине электротоваров регулярно приходилось считать ЭУИ и рамки к ним по зарисовкам заказчиков или их работников, тогда я это делал на бумаге и неплохо набил на этом руку. Но у некоторых продавцов консультантов это выходит не слишком быстро и качественно. Для автоматизации процесса я решил создать таблицу в google, а затем перенес ее в Excel (последний мне нравится больше). Таблицей я намерен поделиться ссылки будут ниже, а пока краткое описание:
Это страница «Сводка» первоначально ее надо заполнить под себя и сохранить как шаблон:
— Наименования всех типов ЭУИ какие у вас могут быть (если не достаточно того, что ввел я)
— Цвета механизмов (или например серия + цвет, как удобнее будет)
— Цвета рамок (аналогично механизмам)
Потом переходим на страницу «Ввод данных»
При добавлении новой строки указываете комнату
цвет механизма, цвет рамки и наполнение постов выбираете из выпадающего списка (подтянутся варианты со страницы «Сводка»), когда вы выбираете механизм для поста — ячейка окрашивается, считая количество постов в рамкеПосле заполнения страницы «Ввод данных», возвращаемся на «Сводка»
При выборе нужного цвета в крайней правой таблице («Текущий цвет») в списке ЭУИ и рамок останется только то количество, которое соответствует выбранным цветам.
Кроме того, общее количество механизмов и постов в рамках и количество установочных коробокЗЫ Отдельное спасибо @XaXa3Pa3a
Полезные трюки при работе в Excel
Всем привет. Это моя первая статья на Пикабу, поэтому позвольте сначала представиться. Я являюсь преподавателем Microsoft Excel. Теперь, когда с формальностями покончено, можно перейти к основному.
Сомнения перед написанием
Я довольно часто читаю разный тематический материал на Пикабу, и меня восхищают большинство авторов и статей. Статьи восхищают, в первую очередь, своей интересностью (есть такое слово вообще?) и полезностью. Именно поэтому у меня были большие сомнения, а стоит ли вообще лезть со своими очередными «простыми, но полезными штуками при работе в Excel». Да и кому вообще ты со своим Excel нужен?! Тем более, что беглый поиск по сайту не выдал ни одной подобной статьи. И та часть меня, которая отвечает за неуверенность, сразу подметила, что раз нет, значит, оно никому не нужно. А может, просто плохо искал. И да, я отдаю себе отчёт в том, что подобного материала довольно много на просторах интернета. И всё-таки, принцип «лучше сделать и жалеть, чем не сделать вовсе» возобладал.
Почему я посчитал, что это будет полезно
Занимаясь преподаванием этой замечательной программы (а я и правда считаю её чудесной и, можно сказать, влюблён в неё), я довольно часто подмечал, что именно мелочи оказывают самое большое впечатление на слушателей. Рассказываешь про сочетание функций ИНДЕКС(ПОИСКПОЗ), какое оно крутое, позволяет двумерный поиск по таблице осуществлять и много чего ещё делать, все сидят, понимающе кивают. Потом в процессе показываешь какую-нибудь мелочь, вроде той, что листы можно копировать, зажав Ctrl и мышкой перетащив лист чуть правее/левее, аудитория сразу оживает: «Ну всё, не зря время потратили». Именно про такие вот простые приёмы я и хотел бы вам рассказать (про первый так уже рассказал).
Небольшое пояснение
Путь до той или иной команды обычно описывается следующим образом: название вкладки — потом группа команд — сама команда:
Если у вас ноутбук, то функциональные клавиши могут работать только при одновременном нажатии на кнопку Fn+F1-12 (есть такие ноутбуки, в которых и этот способ не работает, тут надо уже по модели ноута смотреть).
Вообще, почти каждая функциональная клавиша отвечает за какое-то действие. Но я остановлюсь на одной, а именно — F4. И нет, речь пойдёт не про то, что этой кнопкой в Excel мы можем менять тип ссылки для ячейки.
F4 — повтор последнего выполненного пользователем действия (если нажимать её не тогда, когда курсор находится в строке формул)
Например, вам нужно для нескольких несмежных столбцов установить определённую ширину. Вместо того, чтобы каждый раз выбирать столбец, потом переходить на вкладку Главная — Ячейки — Формат — Ширина столбца. Можно один раз проделать эту операцию, потом просто выделить следующий столбец и нажать F4. И такой фокус можно проделывать со многими операциями, будь то закраска ячеек, строк, столбцов, части графика на диаграмме или банальная вставка столбцов (да, столбец можно вставлять сочетанием Ctrl + «+», но ведь это две кнопки, а F4 — одна).
Представления
Представления, с моей точки зрения, являются одним из самых недооценённых инструментов в Excel. Предположим, у вас есть таблица, в которой вы часто фильтруете несколько столбцов по разным критериям: отдел, пол и город.
И вот вы каждый раз раскрываете фильтр, устанавливаете нужные критерии, просматриваете данные, потом раскрываете фильтр, следующий критерий, потом фильтр. Думаю, суть вы уловили. «Но всё меняется, когда приходят они — представления!» © Установив нужные критерии, переходим на вкладку Вид — Режимы просмотра книги — нажимаем Представления:
Далее всё интуитивно (куда же без интуиции в этой прекрасной программе) понятно. Жмёшь «Добавить», обзываешь представление так, как тебе угодно — Ок. Здесь же, в окне добавления представления, мы можем узнать, а что, собственно, Excel сохраняет. А сохраняет он параметры печати, результаты фильтрации, скрытые строки и столбцы. Создав под каждый набор фильтров, строк и столбцов представление, потом лёгким и непринуждённым нажатием на эту команду ты будешь менять свою таблицу в мгновение ока. Это не совсем удобно? Что же, согласен. Давайте сделаем ещё удобнее и добавим представления на панель быстрого доступа. Для этого раскроем настройку панели быстрого доступа — Другие команды:
В открывшемся окне в поле «Выбрать команды из:» выбираем «Все команды». Потом находим «Представления» — Добавить:
Кстати, так можно добавить на панель быстрого абсолютно любую команду.
Теперь у нас появился выпадающий список со всеми нашими сохранёнными представлениями. Через это же окно можно и новые представления создавать. Просто пишешь в нём название, нажимаешь Enter — готово.
ПРЕДУПРЕЖДЕНИЕ!
Представления не работают в книгах, в которых есть «умные» таблицы (таблицы, которые мы создаём через вкладку Главная — Стили — Форматировать как таблицу).
После создания представления не нужно перемещать столбцы/менять их местами, иначе представление прекратит работать.
Два окна одной книги.
Прежде, чем кидать в меня различные предметы с криками «мало того, что про какой-то Excel пишет, так сейчас ещё будет рассказывать, как в двух окнах работать, смерд?!» позвольте пояснить. Речь пойдёт о том, как работать в двух окнах с ОДНОЙ книгой. Давайте смоделируем ситуацию. Есть у тебя два монитора (если ещё нет, обязательно заводи второй, пускай небольшой, но чтобы был), один файл Excel с несколькими листами внутри. Тебе нужно из одной таблицы перенести данные в другую (сравнить их, связать формулами и так далее). Что ты делаешь? Правильно, бесконечно долго и уныло переключаешься между листами. Второй монитор тем временем грустно за этим наблюдает. Но можно сделать этот процесс более удобным и быстрым. Прошу любить и жаловать, вкладка Вид — Окно — Новое окно:
Нажав на эту команду, мы получим ту же самую книгу, но в другом рабочем окне. Название файла будет немного изменено на «Мой файл:1» и «Мой файл:2». А дальше уже дело за тобой. Располагай окна так, как тебе удобно (на одном мониторе, на разных), копируй данные, создавай связи, формулы — в общем, работай. Но делать ты это уже будешь быстрее и удобнее. Все изменения, которые мы вносим в любое из клонированных окон, появляются сразу во всех связанных окнах. Главное, не забыть нажать «Сохранить» хоть в каком-нибудь окне.
Специальная вставка (пропускать пустые ячейки)
Вообще, про специальную вставку в Excel можно написать отдельную статью, наверное. Инструмент во многих случаях просто незаменимый. Но в рамках данной статьи я расскажу только про одну возможность. Представим, что есть две таблицы:
Нужно перенести данные из крайнего правого столбца второй таблицы (столбец Р) в крайний столбец первой таблицы (столбец F) таким образом, чтобы существующие номера остались. Обычным копированием-вставкой сделать это не получится, так как в столбце Р есть пустые ячейки, которые заменят собой существующие номера в столбце F. И тут на сцену выходит специальная вставка. Выделяем диапазон из столбца Р, копируем. Далее выбираем ячейку, начиная с которой нужно вставить данные (в нашем случае это F2), и либо щёлкаем правую кнопку мыши — в контекстном меню ищем «Специальная вставка», либо нажимаем сочетание клавиш Ctrl+Alt+V. Попадаем в такое окно:
Ставим галочку рядом с «пропускать пустые ячейки» — Ок. Профит!
Хочу отметить, что большинство приёмов, которые я здесь описал, не начнут прям с ходу экономить вам часы рабочего времени. Но если постепенно приучить себя их использовать, вспоминать о них, то скорость работы будет неуклонно возрастать. На этом, пожалуй, всё. Спасибо всем, кто уделил своё внимание и драгоценное время чтению поста. Надеюсь, что кому-то это было полезно. Вообще, если хотя бы одному человеку данный материал поможет в работе, я уже буду считать это успехом.
P.S. Если статья покажется интересной и полезной, то на примете есть ещё несколько приёмов, про которые могу рассказать.
Друзья, создал на Ютубе свой канал. Пока только видео с первой статьёй. В ближайшие дни опубликую вторую часть. Полезные трюки и приёмы при работе в Microsoft Excel — YouTube
Сравниваем продажи
Буэнос диас, мучачос!
Предлагаю снова погрузиться в «сладостный, волшебный, поэтический мир сводок, цифр, отчетов, планов и смет». И диаграмм.
Сегодня сравним продажи чего-нибудь, и слепим из них вот такую визуализацию:
Нам понадобится таблица следующего вида:
Есть три вспомогательных столбца F, G, H.
В графе «Подсветка» (F) — формула «=ЕСЛИ(E4-D4>0;E4;0)» — чтобы потом выделить на диаграмме столбцы, превышающие уровень прошлого года. В графе «Ось» (G) тупо нолики, потом поймете, зачем. А в графе «Подпись» (H) повторяем название категорий товаров, чтобы добавить их на диаграмму, причем располагаем их по середине наших наборов данных (конечно, если в данных по месяцам будет четное количество строк, то по середине не получится, и на диаграмме подписи будут чуть смещены)Выделяем таблицу как показано на скрине:
В меню «Вставка» -> «Гистограммы» -> «Гистограмма с группировкой»:
Для названия диаграммы сделаем ссылку на ячейку, в которой содержится заголовок таблицы.
1. выделяем на диаграмме название
2. в строке формул ставим знак =
3. Жмякаем ЛКМ ячейку B1, затем Enter.
Удаляем в легенде наши вспомогательные значения — подстветку и ось. Сначала ЛКМ выделим всю легенду, затем ЛКМ еще раз щелкаем по нужному элементу -> Delete:
Легенду через формат располагаем сверху, а в левый угол перетягиваем мышкой. (Ну или выберите какой вам нравится вариант расположения):
Убираем оси. И получаем вот такую лысую фигню*:
(*на скринах выше видно, что подписи категорий товаров легли кривенько, но если вас такой вариант устраивает, то можно пропустить дальнейший извращенный процесс добавления подписей через танцы с бубном)
Выделяем любой ряд данных и через меню Работа с диаграммими -> Конструктор -> Изменить тип диаграммы, и поменяем:
— для ряда «2021» на «график с маркерами»
— для ряда «Ось» на «точечную с прямыми отрезками»
После этих манипуляций появятся всякие лишние оси — их удаляем:
Пока столбики стоят по отдельности — настраиваем их цвета на свой вкус. Серый ряд («Подсветка») делаем, например, темнее или ярче:
После того, как с расцветкой определились — делаем перекрытие рядов, и минимальный боковой зазор:
Также, через формат меняем вид графика, который показывает продажи за 2021 год, делаем пунктирную линию, маркер с белой заливкой, и т.д. и т.п.
Теперь, выделяем желтую линию, ПКМ -> добавить подписи данных:
После добавления подписей, нам нужно выделить сами подписи, в нашем случае нолики:
И в формате подписей данных ставим галку на «значения из ячеек» -> «выбрать диапазон» и мышкой выделяем диапазон $H$3:$H$19:
Добавленные таким образом подписи тоже лягут криво, поэтому в формате поставим флажок «Снизу»:
Чтобы подписи не наползали на диаграмму, нужно выделить ЛКМ область построения, и потянуть за нижний маркер чуть вверх:
Теперь спрячем наш нулевой ряд, выделив его ЛКМ и поставив флажок «Нет линий»:
Ну и последние штрихи, добавляем подписи данных, располагаем их как надо, ну и т.п., чтоб получилось вот так:
А как сделать оформление как на первом скрине — вы знаете из прошлого урока Вторичная круговая диаграмма + полезный фокус
Вторичная круговая диаграмма + полезный фокус
Хэллоу, экселеводы и экселеведы!
Сегодня нарисуем круговую диаграмму:
Чтобы построить вторичную круговую диаграмму, нужно пройти 9 кругов ада.
Таблицу данных формируем следующим образом:
*все имена и события вымышлены, любые совпадения с реальными людьми и/или событиями чистая случайность
Графа «Доля» для построения диаграммы не нужна, в таблице она просто так, чтобы было.
В таблице есть некие категории (это могут быть категории товаров, должников, видов услуг и тому подобное). В моем примере данные отсортированы по убыванию — так как для маленького круга мы берем категорию жанров, по которым собирается менее 500 зрителей (либо, у вас это могут быть выделенные по любым иным признакам категории товаров/услуг/чего угодно — главное, чтобы те строки, которые вы хотите выделить в правый круг шли подряд ниже остальных категорий, которые будут располагаться в левом круге)Затем, изуродуем нашу таблицу, добавив в нее строку, которая будет отделять данные для наших кругов:
*в предыдущих уроках были высказаны претензии по поводу того, что нельзя так обращаться с таблицами, с чем я категорически согласна. Но в этих постах нет цели научить вас культуре обращения с данными, я показываю пример таблиц именно для построения тех или иных диаграмм, а не для какой-то иной работы с ними.
Выделяем таблицу без итоговой строчки и «Доли»:
В меню «Вставка» выбираем вторичную круговую диаграмму:
Как всегда, материм эксель за стрёмные цвета и каличный вид диаграммы, смиряемся и пашем дальше. Легенду надо выделить и нажать Delete (либо через «+» в котором скрываются/отображаются элементы диаграммы):
Дальше, мы видим, что какая-то чехарда с рядами данных в кругах — это поправимо. Нужно выделить ряды данных и в правой части экрана, где форматирование диаграмм — поставить необходимое нам количество значений во второй области построения:
Это количество (6) мы взяли, как говорится, не с потолка, а из нашей таблицы — под выделенной подкатегорией «Другие» находится 6 жанров фильмов, зрителей у которых меньше 500 человек.
Добавляем подписи данных — выделив ряды данных, ПКМ -> Добавить подписи данных:
Появится полная фигня, которую нам опять переделывать:
Выделим подписи данных ЛКМ, в форматировании диаграммы для подписей выбираем необходимые параметры:
Те фильмы, которые попали в правый круг по умолчанию, на диаграмме обзываются «Другой». Можно перебить название вручную. Или выделить только эту подпись, и в строке формул поставить » story-block story-block_type_image»>
Тут пришлось изголяться, если мы ссылаемся на ячейку с названием подкатегории, то у нас похерится процентное значение. Поэтому в ячейку с названием я зашила формулу, которая сцепляет текст с суммой процентов.
И еще — проценты в таблице и на диаграмме могут отличаться на 1 п. так как, я х.з вообще по какой логике идет округление на диаграмме((.Ну и последнее, настраиваем «красивости» — цвета, положение подписей, можно сделать круги разрезанными, и т.д. и т.п. на свой вкус и цвет. Я подробно эту муть расписывать не буду, там уже все просто и понятно.
А теперь фокус
Чтобы мы могли перенести диаграмму на чистый лист (ну допустим, мы хотим сделать отдельный лист с инфографиками, без всяких таблиц и прочей лабуды) на панели быстрого доступа нажмите на малюсенькую стрелочку («Настроить панель быстрого доступа») -> Другие команды
Выберите «Все команды» и в списке по алфавиту найдите инструмент «Камера» -> Добавить -> ОК
А теперь, располагаем нашу диаграмму таким образом, чтобы быть уверенными, что ни ее размер, ни ее положение мы больше менять не будем. Выделяем ячейки, на которых «лежит» диаграмма и нажимаем значок «Камера» на панели быстрого доступа:
Появится пунктирная рамка, как при копировании:
В любом пустом месте этого же листа сделайте движение зажав ЛКМ, как будто выделяете диапазон (размах не важен), и наша диаграмма волшебным образом появится на экране, но уже в виде рисунка:
Фокус в том, что этот рисунок можно перенести на отдельный лист, а когда мы будем менять формат диаграммы, все изменения отобразятся и на её «фотографии». Почему нельзя диаграмму передвигать, или менять размер — это всё тоже отобразится на рисунке. А ещё, для рисунка можно настроить всякие красивости, например тени или объем, отражение, поворот и много еще чего:
Диаграмма "План-Факт"
Здрасть!
Сегодня мы нарисуем диаграмму «План-факт» вот такого вида:
Для этого нам понадобится таблица:
В графе «Дельта» простая формула «Факт» минус «План». Для наглядности задаём формат, благодаря которому отрицательная разница светится красным цветом, а положительная — синим. Выделяем диапазон данных, ПКМ -> Формат ячеек -> выбираем (все форматы). А в строке Тип: набираем (кого я обманываю — копируем) это: [Синий]+0%;[Красный]-0%
А дальше пойдут танцы с бубном. Первым делом, зажав клавишу Ctrl выделим столбцы «Месяц», «План» и «Дельта». В меню «Вставка» ищем кнопку «Вставить диаграмму с областями» и жмякаем центральную диаграмму в верхнем ряду («С областями и накоплением»):
Результатом этих действий будет вот такая херобора:
Но нам этого мало. Поэтому выделим теперь столбцы «Факт» и «План», жмякнем ПКМ -> копировать (или Ctrl+C):
А теперь выделим область диаграммы, нажмем Ctrl+V и получим вот такое:
Затем выделим любой ряд данных на диаграмме, жмякнем ПКМ -> изменить тип диаграммы для ряда:
В окне «Изменение типа диаграммы» поменяем последние два ряда на «График с маркерами»
Получится вот так:
Растянем диаграмму вширь, удалим линии сетки, и прочие лишние рамки (линии сетки можно выделить ЛКМ и нажать Delete, только аккуратно, не грохните всю диаграмму):
Убираем линии на горизонтальной оси:
Нижний ряд данных (синий) выделим ЛКМ и сделаем его без заливки:
Название я тоже удаляю (я подписываю диаграммы отдельным заголовком непосредственно в презентации)
Теперь нужно навести порядок в легенде. Нажимаем на нее ЛКМ один раз — и выделяется вся легенда:
А второй раз жмякаем крайний левый «План» — должен быть выделен только этот элемент легенды:
— и нажимаем Delete. Таким же образом поступаем с «Дельта». Затем легенду перенесем наверх:
Вертикальную ось значений выделяем, и меняем начальное значение, например на 0,5 (так как в таблице все значения план-факт больше 50%) — чтобы немного отцентровать график:
Затем вертикальную ось удаляем, она нам больше не нужна. Теперь поочередно выделяем ЛКМ ряды данных и добавляем подписи:
Когда подписи добавлены, мы меняем их положение на «Сверху» или «Снизу» — в зависимости от того, каких значений больше — тех что выше плана или ниже (чтобы меньше перетаскивать вручную), заодно снимаем галку с «линии выноски»
Ряды данных перекрашиваем как нам нравится — не забываем про маркер, у него есть параметры заливка и граница (границу делаем без линий):
Ряд «Дельта» — он посередине между линиями, оранжевый — делаем с узорной заливкой, заштрихованным:
К нему также добавляем подписи данных, и растаскиваем вручную — положительные значения наверх, отрицательные вниз. В формате подписей данных должна по умолчанию стоять галка «Связь с источником» — тогда все наши подписи будут такого же формата, как мы задали в таблице.
Если вы дочитали до конца, то вы супер молодцы, потому что я уже чёт заколебалась, фух.
За это вот вам файл с примером прошлого урока https://disk.yandex.ru/i/1cCD_hA9xC9Ukw
и файл данного урока https://disk.yandex.ru/i/0Qi8vmRVpLNmwAИнфографик "Рейтинг"
Привет, отчаянные и малочисленные, преданные фанаты Excel!
Сегодня изобразим инфографик, показывающий рейтинг сферических руководителей в вакууме (Название мне придумывать лень):
Для этого нам нужна таблица вот такого вида:
Предположим, норматив установлен один на квартал. Пустая строка между месяцами позволяет нам на гистограмме также визуально отделить их
Итак, для построения гистограммы выделим таблицу (без столбца «Ранг»):
В меню «Вставка» ищем значок «Вставить линейчатую диаграмму». И жмякаем «Другие линейчатые диаграммы»:
В окне «Вставка диаграммы» нужно выбрать «Линейчатая с группировкой» -> ОК:
Результатом этих действий будет вот такое уродливое нечто:
Мы должны безжалостно удалить всё лишнее – линии сетки, ось значений, всякие там рамки и т.п. Для этого, выделим ЛКМ эти элементы по очереди и жмякнем «Delete».
А лишнее обрамление и рамки убираем в конструкторе диаграмм. Например, выделили вертикальную ось категорий -> ставим флажок в разделе «Заливка и границы» «нет линий»:
То же самое проделываем для всей диаграммы, чтобы убрать ее рамку:
Обратите внимание — линейчатая гистограмма по умолчанию переворачивает вертикальную ось категорий (то есть наших несчастных руководителей) с ног на голову. Чтобы было красиво и логично, в настройках диаграммы поставим обратный порядок категорий. Для этого выделяем вертикальную ось категорий и в параметрах оси ставим галку «Обратный порядок категорий»:
Теперь причёсываем ряды данных. Выделяем сначала ряд «Факт» и делаем его темно-синим, или каким угодно, какой вам нравится:
Затем, выделим ряд «Норматив» и делаем без заливки, а линии светло-синими:
Теперь, выделим любой ряд данных и настроим перекрытие рядов = 100 %, а боковой зазор, ну например 20%:
Шрифт тоже красим в темно-синий. А легенду можно переместить наверх, или оставьте как есть, и так сойдет:
И последнее, выделим ряд данных «Факт» и ПКМ добавим подписи данных. Расположить их лучше по центру, и перекрасить в белый:
Если б мишки были пчёлами… условия в Excel
Решил попробовать формат коротких постов по Excel
Дети, сегодня я расскажу вам о такой удобной функции в Excel как ЕСЛИ.
В общем виде выглядит так:
ЕСЛИ (условие; результат если условие верное; результат если условие не верное)
Я буду писать формулу, а на картинке будет результат этой формулы в 5 столбце
При множественных условиях иногда удобно применять функции И, и ИЛИ, которые позволяют соединять несколько условий
=ЕСЛИ(И(A2=»овощи»;D2>50);»дорогой овощ»;»либо не дорогой, либо не овощ»)
Не забываем, что можно комбинировать несколько ЕСЛИ Главное не запутаться в скобках и точках с запятыми.
=ЕСЛИ(A2=»фрукты»;ЕСЛИ(D2>100;»дорогой фрукт»;»не дорогой»);»не фрукт»)
Как видим результат получается довольно гибкий.
Также напомню, что есть операторы сравнения <,>,<=,>=,=,<>.А на этом сегодня все, пойду дальше писать про макросы…
Ответ на пост «Помощь в формировании Таблицы на основе 2 таблиц»
Если по-простому, формулами, то так:
Таблицу 3 переделываем в другой вид:
Это будет состав детали, даты будем добавлять вправо.
(Таблица соответствия количества материала количеству детали)
Таблица 2 тогда будет выглядеть так:
(тупо суммируем кол-во материалов на дату)
1. Важно чтобы во всех 3-х таблицах даты были синхронизированы, чтобы можно было протягивать формулы не опасаясь что-то поломать.
2. Важно следить чтобы в составе (таблица 3) не было дубликатов пар — [деталь — материал] (желтые столбцы), иначе объем задваиваться будет.
Сумма итогов, чтобы её не переписывать каждый раз (в ячейке H2) — может выглядеть так:
Ответ на пост «Excel, делим покупки на несколько человек»
И так господа, перед Вами методичка по распиливанию затрат после попоек.
Разработана она мною, но на копирайт не претендую.
Ситуация проста: Пятница, Вы собрались пошуметь. Заказаны «стартовые» места, собрана компания друзей, сняты ограничения с карточек, побрились, подмылись, вызвали такси и поехали!
Сразу рекомендация: договоритесь как будете делить счёт. Вполне возможно, что в вашей компании тот, кому завтра платить за ипотеку. И он вообще в принципе не собирается много тратить.
Помните, между друзьями не должно быть обид или споров по денежным вопросам. Если у вас в компании есть друг, не желающий сопровождать общий счёт, объясните ему, что он сам платит за себя и главное платит СРАЗУ! Принесли счёт в ресторане, он сам запоминает и считает сумму напитого и наеденного и СРАЗУ кидает на стол деньги, а ещё фиксит это, например в общем чате. Кстати, общий чат — это тема. История с карточками — это хорошо, но налом платить в некоторых местах безопасней. Кидайте сразу в общий чат, кто, сколько и главное, за что заплатил. Поверьте, с утра вам будет не до воспоминаний, да и не всё вспомнить. Не забывайте о конспирации! Нельзя писать «3000 Диме на приват». Продумайте заранее все фразы и договоритесь об обозначениях.
Вы проснулись в субботу, в обед с привкусом тухлых кошачьих носков во рту, тремя размытыми печатями, двумя клубными браслетами и одной татуировкой с рожей чихуахуа на правом бедре. На карте минус месячный доход. Но вы всё предусмотрели, поэтому гоу в чат и историю операций с карты в приложении банка.
Помните! Очень большая вероятность, что вы вчера платили за всех и вам все должны (что не факт). В этом случае не один из ваших друзей чисто морально не хочет к похмелью ещё добавлять чувство долга! Поэтому от того, как быстро вернёте деньги зависит только от вас. Теперь вы коллектор и начинаете обзвон друзей.
1. Кидаем в чат позывной. Кто, за что, вчера платил.
2. Открываем Excell и создаём следующую таблицу:
И так: Перед вами таблица со следующими параметрами:
Столбец «Событие» — кратко фиксим события оплаты;
Столбцы «Участники» — все, кто присутствовал на пьянке;
Столбец «Сумма» — Общая сумма к разделу;
Столбец «N» — кол-во человек участвующих в разделе;
Столбец «Проверка» — собственно сумма всех сумм в столбцах B-E = 0.
Если пьянка длилась несколько дней (например Нашествие), то слева ещё вставляется таблица «Дата».
Допустим начали сначала Вася, Петя, Игорь. Зашли в Кафе, пожрали, да выпили на 9500 рубликов. Платил Петя.
Тогда для Пети пишется формула:
Для всех, кто учувствовал пишется формула:
В результате получаем:
Все кто с минусом — те в долгах;
Кто с плюсом -тот кредитор.
Формулы с $ помогают просто копипастить ячейки, и заполняя только столбцы Сумма и N, долбить долг дальше по событиям:
Как мы видим, в пабе к нам пришёл ещё Ваня.
Дальше из караоке идём в клуб. Ваня цепляет тёлку, Вася уезжает домой.
Для этого вводим ещё один столбец а с Васей прощаемся. В клубе мы оставляем 15 к.
Ваня с «Ваней — 2» уезжают, а Петя и Игорь идут:
После стрипухи все возвращаются в кафе на завтрак, при этом по дороге подбирая уже сбежавшего под утро Ваню. В кафе решают платить по честному! Поэтому каждый платит за то, сто съел (Платит Ваня):
Диаграмма-торнадо в Microsoft Excel
Такой тип диаграмм удобно использовать для наглядного сравнение двух наборов данных, например:
- мы и конкуренты по основным направлениям (товарам, услугам)
- количество мужчин и женщин разного возраста среди покупателей или зрителей
- этот год и прошлый год по основным KPI и т.д.
Строить такую штуку не очень сложно, но пара небольших засад на пути поджидает — давайте с ними разберемся.
За основу возьмем обычную линейчатую гистограмму с накоплением (Stacked Bar Chart) :
Чтобы столбцы расходились в разные стороны, а не плюсовались друг на друга, используем простой трюк — добавим перед числами одного из наборов знак минус. Тогда отрицательные и положительные значения пойдут в противоположных направлениях от оси Y. Проще всего это сделать с помощью специальной вставки, а именно:
- ввести минус 1 в любую пустую ячейку и скопировать ее
- выделить числа в данных по одному из наборов (например, население по городу в диапазоне B5:B19)
- нажать Ctrl+Alt+V, чтобы вызвать окно специальной вставки
- выбрать опции Значения (Values) и Умножить (Multiply) и нажать ОК.
Теперь можно выделить всю нашу таблицу с данными (A4:C19) и строить линейчатую диаграмму с накоплением на вкладке Вставка (Insert) . Результат будет уже похож на то, что хотелось:
Для полноты картины лучше перенести вертикальную ось Y вправо до упора, чтобы она не мешалась посреди диаграммы. Для этого нужно щелкнуть правой кнопкой мыши по горизонтальной оси, выбрать команду Формат оси (Format Axis) и затем в группе Вертикальная ось пересекает вариант Максимальное значение оси (Maximum Value) .
Также может возникнуть желание спрятать на диаграмме минусы перед числами. Проще всего это сделать, если применить к ячейкам с исходными данными хитрый пользовательский формат, который не отображает знак «минус». Для этого нужно выделить исходные ячейки (B5:B19), открыть окно Формат ячейки и, выбрав вариант Все форматы (Custom) , ввести в поле Тип следующую маску: