​​Всем отличного воскресенья!

Сегодня короткий пост о такой простой текстовой функции, как ПОВТОР. Она повторяет написание текста заданное число раз. Без разделителя.

Синтаксис прост:

= ПОВТОР (текст; число повторений)

▫️Текст - указали ячейку или текст, который нужно размножить.
▫️Число повторений - указали кол-во повторов текста.

Конструкция = ПОВТОР ("text"; 2) выдаст результат texttext.

О самообразовании

Не можете выучить пару формул? Английский язык или программирование? Или может забываете что есть выручка, а что прибыль?

С нами такое часто бывает) Вроде учишь что-то новое, тратишь много сил, а по факту ничего остается в памяти. Даже своим друзьям не рассказать о новых знаниях.

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

https://t.me/hoolinomics/1071

Суть метода: объснять себе сложные термины и определения так, будто хотите рассказать об этом пятикласснику.

По-моему это великолепно.

​​Склеивание нескольких фрагментов в один

Как склеить фразы из нескольких ячеек таблицы?
Можно взять функцию СЦЕПИТЬ или оператор склеивания - амперсанд (&).
Об этом я уже писал ранее:

Первый пост
Второй пост

Но указанные способы работают с отдельными ячейками. Как сцепить целый диапазон ячеек?
В арсенале Excel есть функция СЦЕП.
Она то и сцепит все строчки в рамках одного диапазона.

= СЦЕП (диапазон)

Видео↓

​​Удаляем лишнее в тексте

Салют!
Выгрузив таблицу из какой-нибудь ERP или скачав ее с сайта, часто в нее залетают лишние символы или пробелы.

Как быстро почистить таблицу от лишних символов?

1. Старейший способ: найти и заменить через Ctrl + H
В открывшемся окне, вставляете нежелательный символ в первую строку, во вторую на что вы его хотите поменять.
Если хотите удалить, то оставляете пустой.

2. Если в тексте больше одного пробела между словами, то в таком случае воспользуйтесь СЖПРОБЕЛЫ.
Эта функция удаляет все лишние пробелы между словами. Оставляет по одному.

= СЖПРОБЕЛЫ (текст)

Работа функции на видео ниже.

3. Кроме пробелов бывают непечатаемые символы. Вроде выглядит как пробел, но на самом деле нет. И функция выше уже не сработает.
Здесь можете использовать функцию ПЕЧСИМВ.

= ПЕЧСИМВ (текст)

See you later!

Салют, друзья!

В течение 2 недель мне пришлось немного попутешествовать по нашей необъятной стране. И ровно 2 недели не было новых материалов на канале, за что я дико извиняюсь)
Я вернулся, работаем дальше.

По итогу голосования, следующей темой вы выбрали Сводные таблицы. Это здорово, следующий пост уже в среду будет по новой теме.
Как пройдем сводные, приступим к второй и третьей теме по популярности в голосовании: "Пакет анализа" и "Работа с датами" соответственно.

Сегодня же я еще немного расскажу о нюансах работы с текстом.

В работе часто приходиться вытягивать из текстовых строк числовые значения. Например, из строки "800000 рублей на аренду" в формулу нужна цифра 800 000. Хотим ее в дальнейшем суммировать с другими числами.

Мы такие берем функцию = ЛЕВСИМВ("800000 рублей на аренду";6) и рассчитываем получить число.

Но вот trouble. Excel воспринимает 800 000 как текст. Ведь мы его выдергиваем из текста. И в дальнейшем формула выдаст ошибку.

Что делать?

— Вариант 1 —
Использовать функцию ЗНАЧЕН. Она преобразует текст в число. Конечно, она работает в случаях когда это возможно: 256 в текстовом формате он преобразует в число 256. Но не надо ждать, что имя "Василий" она преобразует в 777.

То есть формула = ЗНАЧЕН(ЛЕВСИМВ("800000 рублей на аренду";6) выдаст ожидаемое значение 800 000.
То что надо)

— Вариант 2 —
Попытаться Excel навязать арифметические операции с числом из текста.

= ЛЕВСИМВ("800000 рублей на аренду";6) + 0
= ЛЕВСИМВ("800000 рублей на аренду";6) * 1
= -- ЛЕВСИМВ("800000 рублей на аренду";6)

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

Кстати, это может работать не только с числами, но и с датами в тексте.

До встречи!

Сводные таблицы

Добрый вечерочек, друзья)

Сегодня мы врываемся в новый цикл материалов - сводные таблицы. Инструмент нереально полезный.
Быстро и просто схлопывает огромные таблицы в компактные и динамичные отчеты.
Причем чтобы переделать отчет, необходимо лишь перетащить пару полей. Полный режим конструктора.

Залетайте на материал↓

🔗 Изучить материал | Связь
⠀

​​7.2 Варианты расчета значений в сводной

Салют!
Сегодня короткий пост о вариантах расчета значений в сводной таблице.
Из прошлого материала могло показаться, что сводная таблица только суммирует значения по ФИО, датам.
Но нет. Есть возможность посчитать среднее, кол-во значений, мин, макс и многое другое.

— Шаг 1 —
Нажимаете ПКМ по значениям в таблице и выбрать пункт Параметры полей значений.

— Шаг 2 —
Открывается окно параметров.
На первой же вкладке Операция выбираете что вы хотите сделать со значениями. Наиболее популярные варианты: среднее, кол-во значений, минимум и максимум.

В верхней части ставите имя поля.

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

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

Еще больше подробностей в коротком видео↓

​​7.3 Группировка полей в сводной

Бонджорно!
Мы уже поняли, что сводная группирует значения и выводит более компактную версию таблицы.
Помимо этого существует функция группировки строк и столбцов. Расскажу о 2-х видах группировки: автоматической и ручной.

Автоматическая группировка

Применяется в-основном для полей времени и дат.
Excel уже априори знает, что дата 01.04.2020 содержит в себе день, месяц, квартал, год.
А время 11:40:50 содержит в себе секунды, минуты и часы.
Система может автоматически разбить подобные значения.

Чтобы разбить даты 01.04.2020, 02.10.2020… по кварталам, требуется:

— Встать на любое значение строки/столбца.
— На ленте, на вкладке Анализ (Работа со сводными таблицами), выбрать раздел Группа и нажать на Группировка по полю.
— В открывшемся окне, выбрать диапазон значений для группировки. В поле С шагом: выбрать единицу группировки. Это может быть день, месяц, квартал, год и т.д.
Есть возможность создания вложенной группировки. Например по годам, а внутри по кварталам. Для этого выбирайте сразу 2 значения: Годы и Кварталы.
— Нажать Ок.

Ручная группировка

Применяется для полей, которые не работают с автоматической группировкой. Excel же не знает, что Иванов и Петров это отдел продаж, а Максимова и Ямцов – отдел маркетинга)
Поэтому нам нужно вручную выделить строки и объединить их в группу.

— Выделить все группируемые значения в строках/столбцах.
— На ленте, на вкладке Анализ (Работа со сводными таблицами), выбрать раздел Группа и нажать на Группировка по выделенному.
— Группу можете переименовать вручную, в строках сводной таблицы.

Удаление группировки

Финальный аккорд.
Когда группировка не нужна, мы это все разгруппировываем.

— Встать на любое группируемое значение
— На ленте, на вкладке Анализ (Работа со сводными таблицами), выбрать раздел Группа и нажать на Разгруппировать.

Видео↓

​​Кейс №15. Условное форматирование по времени и дате

Всем привет!
Пост по рубрике #кейсы. Недавно получил от подписчика вопрос: можно ли сделать условное
форматирование ячеек (заливка цветом), по истечению определенной даты?

Да, это легко можно сделать.

— Заходим в Условное форматирование на главной вкладке ленты.
— Кнопка Создать правило.
— В открывшемся окне выбираем пункт Использовать формулу для определения форматируемых ячеек.

В строке внизу требуется ввести формулу. Какие варианты?

Если требуется заливать цветом ячейки, например до того момента как наступит 30 апреля 2020, то следует написать формулу:

= СЕГОДНЯ() < ДАТА(2020;04;30)

Система будет сравнивать сегодняшнюю дату с 30.04.2020 и если она меньше, то ячейки буду отформатированы.

— Ну и конечно в конце нужно выбрать форматирование – кнопка Формат. В нашем случае – залить зеленым цветом.

Использовать подобные приемы можно на различных графиках исполнения работ, загрузки и пр.
Видео↓

​​7.4 Итоги в сводной таблице

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

1. Промежуточные итоги

Этот инструмент суммирует строки или столбцы по созданным группам в сводной таблице - вспоминаем прошлый материал 7.3 Группировка полей в сводной.

Например, в сводной вы сделали 2 группы менеджеров по продажам: работающих на на международном и отечественном рынке. Если вам интересно сколько заработала каждая из групп, то инструмент "Промежуточные итоги" то что нужно.

Чтобы включить суммирование по группам.
— Встаем на любое поле сводной таблицы. На ленте появляются доп. вкладки. Открываем Конструктор.
— Нажимаем ярлык Промежуточные итоги. Варианты на выбор:

▫️Не показывать промежуточные суммы - отключение всех итогов по группам.
▫️Показывать все промежуточные итоги в нижней части группы - вставляет последнюю строку с итогами для каждой группы.
▫️Показывать все промежуточные итоги в заголовке группы - вставляет итоги в первую строку группы заголовок).

Важный момент

По-умолчанию в промежуточных итогах рассчитывается сумма значений. Но никто не запрещает рассчитать среднее или кол-во значений.

Для этого нужно открыть Параметры поля, которое группирует строки или столбцы.
На вкладке Промежуточные итоги и фильтры есть чек-бокс.
▫️Автоматически - расчет суммы в итогах
▫️Нет - отключить промежуточные итоги для поля.
▫️Другие - для расчета среднего, мин, макс и т.д.

2. Общие итоги

Интуитивно понятно из названия - инструмент рассчитывает итоги по всем строкам/столбцам сводной таблицы.

— На вкладке Конструктор нажимаем на ярлык Общие итоги. Варианты на выбор:

▫️Отключить для строк и столбцов - отключение всех итогов.
▫️Включить для строк и столбцов - включает расчет общих итогов по всем фронтам.
▫️Включить только для строк.
▫️Включить только для столбцов.

Подробнее в видео↓

​​7.5 Рассчитываем долю от общего

Алоха!
Помните пост о рассчитываемых средних значениях, мин, макс? Если забыли, то вот ссылка на пост.

Так вот, есть еще вариант - расчет доли в %.
Этот прием помогает понять вклад персонала в рамках одного дня, или понять какая доля затрат по статьям в рамках одного месяца.
Просто must have в работе со сводными таблицами!

Как рассчитать долю в %

Описываю вариант с созданием дополнительных значений. Если оно вам не требуется - пропустите первый шаг.

— Создаете дубль поля значений. В рамках видеопримера это поле Оценки. Просто перетягиваете его в блок Значения, повторно.
— ПКМ по полю. Выбрать Параметры полей значений. Вкладка Дополнительные вычисления.
— В выпадающем списке выбрать вариант % от суммы по строке. На мой взгляд самый рабочий вариант. Рассчитывает долю в рамках одной строки. Если у вас в строках месяцы, то доля будет в рамках одного месяца.

Видео↓

7.6 Вычисляемые поля сводной таблицы

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

По традиции, материал с большим количество видео и изображений я публикую в telegraph.

Залетайте на материал↓

🔗 Изучить материал | Связь
⠀

​​7.7 Срезы в сводных таблицах

Привет, дорогие подписчики!
Сводные таблицы шикарны, даже фильтры в них отличные. Об особом виде фильтров и поговорим, называются они — Срезы.

Срез - это группа интерактивных кнопок, с помощью которых мы быстро и легко отфильтруем сводную.
Например срез по ФИО, в котором каждая кнопка - это ФИО 1-го сотрудника, или срез по датам.

Чтобы включить срез для сводной:
— Встаем на сводную и залетаем на вкладку Анализ.
— Нажимаем на кнопку Вставить срез.
— В открывшемся окне выбираете поле, по которому хотите построить срез (фильтр).
— Well done. Появилось окно среза.

Варианты использования:
1) Если требуется выбрать одно значение среза, то просто нажимаете ЛКМ.
2) Если требуется выбрать сплошной список, то нажимаете ЛКМ по первому элементу и с зажатым Shift нажимаете на последний элемент списка.
3) Если требуется выбрать несколько значений вразнобой, то в правом углу среза включите режим Выбрать несколько объектов и выбирайте нужные значения.

Прочитали? Предлагаю еще и посмотреть)

Кейс №16. Быстрое протягивание формул

Буэнос диас!
Наверно многие заполняли формулами таблицы с тысячами строк. Хорошо когда соседний столбец заполнен всплошную (без пустых ячеек) и формула тянется двойным кликом по темной точке в углу.

А как быстро протянуть формулу на тысячи строк с разрывами и пустыми ячейками, да еще чтобы не поседеть от этого занятия?

Ловите простой кейс:
— Заполняем формулой первую ячейку в столбце. Выделяете ячейку.
— Зажимайте Shift и с помощью бегунка, прокручиваете вниз таблицы. К последней ячейке, где должна быть формула.
— Выделяете последнюю ячейку и нажимаете комбинацию Ctrl + D. Это сочетание заполняет все выделенные ячейки содержимым ячейки выше. — а там формула)

Посмотрите в видео↓

​​7.8 Временные шкалы

Салют, друзья!
Уверен, что когда я рассказывал о срезах, многие заметили отдельный инструмент Временные шкалы. Сегодня о нем коротко и поговорим.

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

Как сделать?
— Встаем на сводную и переходим на вкладку Анализ.
— Нажимаем на кнопку Вставить временную шкалу.
— В открывшемся окне выбираете поле дат/времени.
— Готово. Достаточно выбрать группировку: год/квартал/месяц/день. И с помощью бегунка выбираем промежуток необходимых дат.

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

Видео↓

7.9 Визуализация в сводных таблицах

Hello, friends!
Давно мы с вами не виделись) Готовлю для вас второй проект, который также поможет преодолеть любые рабочие и личные задачи с помощью таблиц. В ближайшие дни анонсирую его на канале.

Так, у нас осталась самая малость по сводным таблицам - это их визуальная составляющая. Здесь особо не разгуляться, в сводных всего 2 инструмента визуализации:

▫️Стили сводных таблиц
▫️Сводные диаграммы

О них сегодня и поговорим. И если у вас остались вопросы по сводным таблицам или считаете что материала не хватило - пишите мне в бота - @excelstudybot.

Следующим циклом будет материалов будет Пакет анализа Excel. Многие о нем не знают, а вещь стоящая.

А теперь залетайте на последний материал по сводным↓

🔗 Изучить материал | Связь
⠀