Поиск текста

Привет👋🏻
Продолжаем цикл текстовых функций. В материале 2 простые функции о поиске текста.
Они помогут вам отыскать местонахождение фрагмента в текстовой строке. Или например, местонахождение определенной буквы в слове.

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

​​Проверка на совпадение

Как проверить совпадение текста 1 и текста 2?
Первое, что приходит на ум:

= ЕСЛИ ("текст 1" = "текст 2"; 1; 0)

Всё гуд, но есть подвох😬
Такая запись не учитывает регистр. "ПРИМЕР" и "пример" для формулы будут одинаковыми строчками.

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

= СОВПАД (текст 1; текст 2)

Синтаксис интуитивно понятен. Работает просто: совпадает - 1; нет - 0.
Подробнее в видео.

​​​​Хай!
Друзья, настало отличное время для перемен в лучшую сторону.
В ближайшую неделю я всем желаю научиться чему-то новому. Научитесь тому, о чем давно мечтали, но всегда откладывали.
Завтра будьте лучше и сильнее, чем вчера.

🧠 А сейчас я вам предлагаю размять мозги и ответить на 2 викторины по текстовым функциям.
Всего один правильный ответ.
Поехали!

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

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

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

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

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

Конструкция = ПОВТОР ("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.

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

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