Сообщество - MS, Libreoffice & Google docs

MS, Libreoffice & Google docs

138 постов 7 488 подписчиков
323

Диаграмма «план-факт»

Диаграмма «план-факт»


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


Итак, рассмотрим на примере небольшой и скучной таблицы создание диаграммы «план-факт», в которой визуально будет отображено выполнен план или нет.

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

Выделяем таблицу без заголовка и на вкладке «Вставка» - «Диаграммы» выбираем «График с маркерами»:

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

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

Так как при просмотре диаграммы визуально идёт сравнение данных, добавим для наглядности «Полосы повышения и понижения» во вкладке «Добавить элемент диаграммы»

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

В результате получим диаграмму, которую можно подкорректировать на свой вкус в меню «Формат области диаграммы»:

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

Второй способ, это применение диаграммы с областями.


Для этого к имеющейся таблице добавим столбец «Разность», в который вписав формулу =C4-B4 получим разницу между фактом и планом.

Далее, удерживая Ctrl выделим содержимое столбцов A, B и D, на вкладке «Вставка» выбираем диаграмму «С областями и накоплением»:

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

Теперь выделяем столбцы «План» и «Факт», копируем их и вставляем в диаграмму, в результате получаем своеобразные горы:

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

Нам нужны лишь две верхние полосы. Выделяем первую полосу и нажатием правой клавиши в контекстном меню задаём зелёный цвет, вторую окрашиваем в красный.

Далее также нажатием правой клавиши мыши по диаграмме выбираем «Изменение типа диаграммы», где в открывшимся окошке для «плана и факта» выбираем «График с маркерами». Нажимаем ОК.

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

Затем выделяем нижнюю полосу и в контекстном меню выбираем «Нет заливки». В итоге получаем такую диаграмму:

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

Теперь можно навести красоту окрасив её в подходящие цвета, удалить в легенде ненужные пояснения, а также задать название нажав на заголовок и в строке состояния ввести формулу =Лист2!$A$1, в результате не придётся заново писать название диаграммы.

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост
Показать полностью 7
39

Калькулятор неопределенности в Эксель

Доброго дня всем, пишу первый раз, строго не судите.

Как то раз, подруга попросила сделать ей нормальный калькулятор для расчета неопределенности в Эксель. За неимением оного, калькулятор я сделал в опен офисе, но в экселе он тоже работает. О том, что это за штука и для чего она, я писать не буду, кто в теме, тот поймет. Может кому интересно будет.

В книге 3 листа:

1) сам калькулятор;

2) список измерительных приборов;

3) список определяемых параметров.

Калькулятор неопределенности в Эксель Excel, Калькулятор

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

В данном файле макросы не использовались. Скачать с яндекс диска https://yadi.sk/i/eTvuGZPUUAJEoQ

461

Скрытие и отображение ненужных строк и столбцов

Вот и наступил декабрь, у кого-то началась подготовка к празднованию Нового года, а кто-то, сидя в офисе, готовится к сдаче годового отчёта.


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


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

Скрытие и отображение ненужных строк и столбцов Excel, Таблица, Лайфхак, Полезное, На заметку, Гифка

Пример принципа работы группировки таблицы в гифке:

Скрытие и отображение ненужных строк и столбцов Excel, Таблица, Лайфхак, Полезное, На заметку, Гифка

Чтобы создать группировку таблицы выделяем нужное количество строк или столбцов, а затем выбираем на вкладке Данные – Группировать (Data – Group), либо нажатием сочетания клавиш Alt+Shift+стрелка вправо, а для разгруппировки Alt+Shift+стрелка влево, соответственно. В результате они будут сгруппированы.

Скрытие и отображение ненужных строк и столбцов Excel, Таблица, Лайфхак, Полезное, На заметку, Гифка

В итоге у нас получится вот такая таблица

Скрытие и отображение ненужных строк и столбцов Excel, Таблица, Лайфхак, Полезное, На заметку, Гифка

Нажатием на «+/-» или цифры можно сворачивать и разворачивать ячейки и столбцы. Группы можно делать вложенными одна в другую (до 8 уровней вложенности).

Скрытие и отображение ненужных строк и столбцов Excel, Таблица, Лайфхак, Полезное, На заметку, Гифка

Также, если в таблице имеются итоговые строки или столбцы с функцией суммирования соседних ячеек, то есть шанс (не 100%-ный), что Excel сам создаст все нужные группировки в таблице одним движением – с помощью команды Данные – Группировать – Создать структуру (Data – Group – Create Outline). Однако, данная функция работает весьма непредсказуемо на сложных таблицах и порой выдаёт полный бред, но проверить можно.

Показать полностью 1
9

Как в Excel сделать соответствие ячейки значению

UPD: Вопрос закрыт #comment_161278635


Есть два столбика, А И Б, в которых указана высота и число. Как сделать так что если при получении расчетов получалось число высоты то в ячейке выбивало значение число которое соответствует этой высоте. То есть если G3=A3 то выбивало бы значение В3. То есть то которое соответствует этой высоте.

598

Использование срезов для поиска и фильтрации

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

Использование срезов для поиска и фильтрации Excel, Таблица, Лайфхак, Полезное, Длиннопост, Гифка

Для этого, уже имеющуюся таблицу с данными, необходимо отформатировать как таблицу «Главная» - «Форматировать как таблицу» (Home – Format as Table), где выбираем любой дизайн. В моей таблице 3000 строк, ссылку на файл оставлю в комментариях.


Теперь, поставив курсор на ячейку в шапке, переходим на появившуюся динамическую вкладку Работа с таблицами: Конструктор (Table Tools: Design) и нажимаем кнопку Вставить срез (Insert Slicer). В открывшемся окне отмечаем флажками названия столбцов, по которым будем фильтровать.

Использование срезов для поиска и фильтрации Excel, Таблица, Лайфхак, Полезное, Длиннопост, Гифка

Появившиеся срезы размещаем над таблицей в удобном для вас порядке.

Если срез содержит очень много элементов (кнопок), то их можно расположить в несколько столбцов, как на срезах «Месяц» и «Бригадир». Для этого выделите срез и увеличьте для него количество столбцов на вкладке Инструменты для среза: Параметры (Slicer Tools: Options). Здесь же можно выбрать Стили срезов.

Использование срезов для поиска и фильтрации Excel, Таблица, Лайфхак, Полезное, Длиннопост, Гифка

Дополнительные параметры выделенного среза можно настроить с помощью кнопки Настройка среза (Slicer Settings) на той же вкладке:

Использование срезов для поиска и фильтрации Excel, Таблица, Лайфхак, Полезное, Длиннопост, Гифка

Удерживая клавиши Ctrl или Shift можно выделять сразу несколько элементов среза. Для сброса фильтра нажимаем кнопку Удалить фильтр (Clear Filter) в правом верхнем углу среза.


Также, чтобы скрыть срезы при печати документа нажимаем правой клавишей мыши по срезу и в разделе Размер и свойства - «Свойства» снимаем галочку с «Выводить объект на печать».

Использование срезов для поиска и фильтрации Excel, Таблица, Лайфхак, Полезное, Длиннопост, Гифка

Либо, выделив область таблицы, в параметрах печати можно выбрать «Напечатать таблицу» или только «Выделенный фрагмент».

Использование срезов для поиска и фильтрации Excel, Таблица, Лайфхак, Полезное, Длиннопост, Гифка

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

Показать полностью 5
73

Лайфхак для экселя

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

10

Отсутствует работа формул и функций в Excel

Прошу помощи сообщества Excel.

Суть вопроса.

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

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

Могу выслать образец проблемного файла, если напишите почту в комментариях.

Заранее всем спб за помощь.

ПЫСЫ : образец проблемного файла можно скачать на  https://dropmefiles.com/IAFQe

369

Поиск данных в нескольких таблицах

Допустим, что у нас имеется четыре одинаковых по конструкции таблицы с данными по заказам товаров в трех странах:

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

Рассмотрим значение используемых формул по отдельности.

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

=ПОИСКПОЗ(W3;Q4:Q8;0)

=MATCH(W3;Q4:Q8;0)


=ПОИСКПОЗ(W4;R3:T3;0)

=MATCH(W4;R3:T3;0);W5)

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

Дальше используем функцию ИНДЕКС, чтобы извлечь данные из набора нескольких таблиц

=ИНДЕКС((C4:E8;H4:J8;M4:O8;R4:T8);X3;X4;W5)

=INDEX((C4:E8;H4:J8;M4:O8;R4:T8);X3;X4;W5)

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

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

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


=ИНДЕКС((C4:E8;H4:J8;M4:O8;R4:T8);

ПОИСКПОЗ(W3;Q4:Q8;0);

ПОИСКПОЗ(W4;R3:T3;0);W5)


=INDEX((C4:E8;H4:J8;M4:O8;R4:T8);

MATCH(W3;Q4:Q8;0);

MATCH(W4;R3:T3;0);W5)


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

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

Сперва на вкладке «Формулы» - «Диспетчер имен» создадим именованные диапазоны, которые на них указывают:

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

Далее используем формулу для поиска номера строки товара:

=ПОИСКПОЗ(W4;ИНДЕКС(ДВССЫЛ(W3);0;1);0)

=MATCH(W4;INDEX(INDIRECT(W3);0;1);0)

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

Для тех, кто предпочитает знать как всё устроено, разберём её подробно))


Во-первых, функция ДВССЫЛ(W3) в данном случае представляет собой ссылку на именованный диапазон 4-го квартала. Прямую ссылку на ячейку с именем W3 использовать нельзя, т.к. Excel будет воспринимать ее как текст. Чтобы превратить текст «Квартал4» в живую ссылку на именованный диапазон «Квартал4», и нужна функция ДВССЫЛ (INDIRECT).


Во-вторых, фрагмент: ИНДЕКС(ДВССЫЛ(W3);0;1)

… представляет собой ссылку на первый столбец именованного диапазона «Квартал4», т.е. на Q3:Q10.


Как это получилось?

Классический вариант использования функции ИНДЕКС на одной двумерной таблице, напомним, предполагает три аргумента: =ИНДЕКС(диапазон; номер_строки; номер_столбца)

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

Хитрость в том, что если номер строки равен 0, то ИНДЕКС выдает уже не содержимое ячейки, а ссылку на весь столбец с указанным номером, т.е. на первый столбец именованного диапазона заданного ДВССЫЛ(W3), т.е. на ячейки Q3:Q10.

Ну а затем функция ПОИСКПОЗ (MATCH) ищет в этом диапазоне требуемый товар (Пиво) и возвращает его позицию (4 строка, т.к. пустая Q3 тоже считается).


Аналогично можно найти номер столбца с нужной страной:

=ПОИСКПОЗ(W5;ИНДЕКС(ДВССЫЛ(W3);1;0);0)

=MATCH(W5;INDEX(INDIRECT(W3);1;0);0)

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

Только в этом случае нулю равен не номер строки, а номер столбца, чтобы получить ссылку на первую строку именованного диапазона «Квартал4», где затем функция ПОИСКПОЗ будет искать «KZ».


И, последним останется вытащить количество заказов функцией ИНДЕКС:

=ИНДЕКС(ДВССЫЛ(W3);X4;X5)

=INDEX(INDIRECT(W3);X4;X5)

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

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


P.S. Ссылка на файл примера в комментариях.


P.P.S. Уважаемые подписчики, Вас уже больше 7000 человек, это приятно радует и вдохновляет на создание новых постов)) В комментариях Вы писали, что я один из немногих, на кого Вы подписаны, кто-то впервые подписался на меня, а кто-то специально зарегистрировался, чтобы подписаться)) Это очень приятно и я не хочу Вас разочаровывать.

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

Показать полностью 5
1681

Суммирование в Excel через одну, две, три ячейки

Всем известен способ подсчёта данных в Excel путём элементарного сложения ячеек =C2+C4+C6, либо с помощью функции =СУММ(C2;C4;C6), но если таблица большая, то такой способ выведет из себя даже самого терпеливого. Поэтому предлагаю рассмотреть способ суммирования по условию: =СУММЕСЛИ(B2:B15;"Доход";C2:C15)

=СРЗНАЧЕСЛИ(B2:B15;"План";C2:C15)

Суммирование в Excel через одну, две, три ячейки Excel, Таблица, Сумма, Лайфхак, Полезное, Длиннопост

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

=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;

B2:B15))

=МИН(ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;

B2:B15))

=МАКС(ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=1;

B2:B15))

Суммирование в Excel через одну, две, три ячейки Excel, Таблица, Сумма, Лайфхак, Полезное, Длиннопост

Если же нужно суммировать каждую третью, четвертую и т.д. ячейки, то изменяется только делитель номера строки:

=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(C2:C17);4)=1;

C2:C17))


В качестве второго варианта можно использовать функцию СУММПРОИЗВ, которая вводится как обычная формула (без Ctrl+Shift+Enter), но работает также:

=СУММПРОИЗВ(--(ОСТАТ(СТРОКА(C2:C17);4)=1);C2:C17)

Суммирование в Excel через одну, две, три ячейки Excel, Таблица, Сумма, Лайфхак, Полезное, Длиннопост

Для подсчёта суммы, находящейся в столбцах, вместо СТРОКА используем СТОЛБЕЦ, затем протягиваем формулу вниз:

=СУММПРОИЗВ(--(ОСТАТ(СТОЛБЕЦ(B3:F3);2)=0);B3:F3)

Суммирование в Excel через одну, две, три ячейки Excel, Таблица, Сумма, Лайфхак, Полезное, Длиннопост

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

Показать полностью 2
689

Сумма ячеек по цвету, шрифту, формату и т.д

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

1. Сохраняем документ с поддержкой макросов, хотя его там и не будет, но в данном случае так надо.

2. Нажатием Ctrl+F3 или на вкладке «Формулы» выбираем «Диспетчер имён», где в строке «Имя» вводим название формулы ЦветЯчейки, а в поле «Диапазон» саму формулу: =ПОЛУЧИТЬ.ЯЧЕЙКУ(63;ДВССЫЛ("RC[-1]";0))

=GET.CELL(63;INDIRECT("RC[-1]";0))

Сумма ячеек по цвету, шрифту, формату и т.д Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Я не буду грузить читателя разъяснением значений этой формулы, главное, чтобы всё работало, не так ли?)) Отмечу лишь, что 63 это код цвета заливки, заменяя который, можно подсчитывать ячейки по формату, шрифту, выделению курсивом и т.п.

Вот таблица с кодами и их значениями:

Сумма ячеек по цвету, шрифту, формату и т.д Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

3. В соседнюю от цветной ячейки вводим «равно» и имя созданной формулы ЦветЯчейки, затем протягиваем её вниз. В результате отображаются коды цветов, которые будут необходимы нам для подсчёта.

Сумма ячеек по цвету, шрифту, формату и т.д Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

4. Теперь с помощью формулы =СУММЕСЛИ(B2:B12;10;A2:A12) можно посчитать сумму цветных ячеек, где 10 это код цвета, для жёлтых - 6:

=SUMIF(B2:B12;10;A2:A12)

Сумма ячеек по цвету, шрифту, формату и т.д Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Созданная формула =ЦветЯчейки определяет 56 цветов. На некоторые цветовые гаммы он реагирует некорректно, так что любителям 50 оттенков серого придётся воспользоваться хардкорным чёрным или серым))

Вот пример палитры «твёрдых» цветов:

Сумма ячеек по цвету, шрифту, формату и т.д Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Следует учесть, что Excel при изменении цвета ячейки автоматически не совершит пересчёт данных, для этого необходимо заново ввести формулу, либо нажать Ctlr+Alt+F9, что гораздо проще и быстрее.


Также на этот случай есть макрос,


1. Нажимаем Alt+F11 и в открывшемся окне вводим следующий текст


Public Function SumByColor(DataRange As Range, ColorSample As Range) As Double

Dim Sum As Double

Application.Volatile True


For Each cell In DataRange

If cell.Interior.Color = ColorSample.Interior.Color Then

Sum = Sum + cell.Value

End If

Next cell

SumByColor = Sum

End Function


2. В разделе «Формулы» - «Вставить функцию» выбираем категорию «Определенные пользователем», где указываем нашу функцию, которой задаём диапазон подсчёта и образец цвета:

Сумма ячеек по цвету, шрифту, формату и т.д Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

И получаем результат:

Сумма ячеек по цвету, шрифту, формату и т.д Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Чтобы макрос учитывал не цвет заливки фона, а цвет шрифта ячейки, в шестой строке заменяем свойство Interior на Font в обеих частях выражения.


Для подсчёта количества цветных ячеек, а не суммы, заменяем в седьмой строке Sum = Sum + cell.Value на Sum = Sum + 1


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


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

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


Следует учесть, что функция перебирает все, в том числе и пустые ячейки, в DataRange, поэтому задавайте в качестве первого аргумента только диапазон со значениями, а не весь столбец, иначе Excel «зависнет» надолго.


P.S. Уважаемые подписчики, посты про Excel будут выходить по понедельникам
Показать полностью 5
Мои подписки
Подписывайтесь на интересные вам теги, сообщества,
пользователей — и читайте персональное «Горячее».
Чтобы добавить подписку, нужно авторизоваться.
Отличная работа, все прочитано!