| Привет! Вот что ждет вас сегодня.
Табличное: несколько нюансов работы с великой и местами ужасной функцией ВПР / VLOOKUP и решение задачи с обработкой результатов опроса, где вместо чисел были текстовые строки 😊
Жизненное: про новую книгу писательницы Гретхен Рубин и хороший совет от Джеймса Клира (автора «Атомных привычек»). 2 лучших биографии Гагарина. Немного внутренней кухни про подготовку к выступлениям. | |
| | Задачка: обработать текстовые ответы на количественный вопрос | |
| | Вот с такой проблемой помогал на днях: в опросе, в котором был количественный вопрос, была свободная форма ответа. То есть нужно было рассказать о потреблении чего-то, и кто-то отвечал не в точности «50» или «500», а «100−200», кто-то «от 10 до 20» и так далее. Это все текстовые строки и их надо было обработать. Конечно, в идеале надо было настроить вопрос так, чтобы можно было выбирать только два числа — нижнюю и верхнюю границе — или вообще одно число. Но мы работали с тем, что есть.
1 шаг: забираем из столбца с ответами одно или два числа. В Google Таблицах (где и были данные) или в новом Excel проще всего использовать регулярные выражения.
Начинаем с первого: | |
| | Извлекаем число (\d+) — если в процессе возникнет ошибка из-за того, что в ячейке не текст (а REGEXEXTRACT работает с текстом), а число — то мы сразу забираем это число. Если не ошибка — то извлекаем его и делаем число с помощью двойного умножения на минус единицу (два минуса). То есть никак не меняем, но делаем извлекаемый текст настоящим числом (можно также сделать это с помощью функции ЗНАЧЕН / VALUE).
=ЕСЛИ (ЕОШИБКА (REGEXEXTRACT (A2;"\d+")); A2;--REGEXEXTRACT (A2;"\d+"))
Второе число (до) извлекаем так:
=ЕСЛИОШИБКА (--REGEXEXTRACT (A2;"[- —]{1,3}(\d+)");B2)
Что тут происходит? Мы ищем пробел или дефис или тире ([- —]) от одного до трех символов из этого набора и затем извлекаем число, идущее после этого набора. В случае ошибки понимаем, что второго числа в ответе нет — и забираем первое, у нас получится набор из двух одинаковых чисел в таком случае. | |
| | 2 шаг: считаем среднее по двум значениям: | |
| | 3 шаг: считаем среднее/моду/медиану/другие нужные показатели по всем значениям: | |
| | Нюансы функции ВПР / VLOOKUP | |
| | Это древняя и легендарная функция. И хотя многие пользуются ей не один десяток лет, не все знают про некоторые нюансы.
В ВПР работают символы подстановки! Кстати, новая функция ПРОСМОТРX / XLOOKUP не работает с ними по умолчанию, нужно включать такой режим отдельным аргументом.
Да, как в СУММЕСЛИМН и ряде других функций.
* - любой текст любой длины
? — один любой символ
Например, вот так будем искать ячейку с текстом, который начинается на «Lenovo Yoga», а далее любой текст:
=ВПР ( "Lenovo Yoga*"; диапазон; номер столбца; 0)
И тут важно учитывать следующий пункт:
ВПР всегда ищет сверху вниз (то есть при 2 и более совпадениях найдет первое). Иначе говоря, если у вас в таблице три разных модели Lenovo Yoga, будет найдено то, которое сейчас идет первым по порядку. Если нужно извлечь все, используйте новую функцию FILTER.
Номер столбца, из которого мы извлекаем данные ВПР-ом — число. Оно не привязано к столбцам, к листу Excel. То есть если мы извлекали из второго столбца, а потом вставили между первым и вторым еще столбцы, ВПР не отреагирует на это изменение исходных данных.
Если хочется сделать более гибкую формулу, можно искать номер нужного столбца по его заголовку функцией ПОИСКПОЗ / MATCH:
=ВПР (искомое значение; диапазон; ПОИСКПОЗ ("заголовок нужного столбца"; строка с заголовками в диапазоне; 0) ;0)
Почему тут два нуля? И у ВПР, и у ПОИСКПОЗ последний аргумент, равный нулю, определяет точный режим поиска, то есть поиск полного совпадения.
А что будет, если забыть его указать?
По умолчанию этот аргумент равен единице, и это интервальный просмотр. Он подходит для поиска ближайшего наименьшего числа, но для текста не очень. С текстом он сработает, если у вас данные отсортированы по алфавиту (по столбцу, в котором вы ищете). Потому что работает алгоритм бинарного поиска. Он быстрее, но требует сортировки данных и риски слишком серьезные:
1. Кто гарантирует, что у вас всегда данные будут отсортированы в нужном порядке по нужному столбцу?
2. Если искомого значения в таблице не будет, ВПР «принесет» почти случайное значение, но не просигнализирует об ошибке.
Так что если ищем текст — у ВПР и ПОИСКПОЗ не забываем последний аргумент, он должен быть равен нулю.
У новых функций ПОИСКПОЗX / XMATCH и ПРОСМОТРX / XLOOKUP этот режим уже работает по умолчанию. | |
| | Курс по сводным таблицам в Google Spreadsheets | |
| Я закончил работу над онлайн-курсом про сводные таблицы в Google… Таблицах. от основ (зачем вообще сводные нужны и как построить сводную) до нюансов и деталей (как работают рассчитываемые поля, как правильно извлекать данные из сводной, как подготовить данные для сводной таблицы, в том числе сделать «анпивот» формулами, и многое другое). Говорим и про визуализацию, и форматирование. Так что у вас будет все необходимое для создания отчетов (в том числе для предварительной подготовки данных). Курс — это 20 видео с нормальным качеством и звуком, исходными и готовыми таблицами со всеми примерами, которые можно копировать и забирать себе для разбора или в качестве шаблонов. Доступ к курсу сразу после покупки. Доступ вечный. Если вам не понравятся уроки, вы поймете, что не узнали вообще ничего нового и/или скажете, что качество видео/звука плохое — я верну вам 100% стоимости курса без вопросов в течение 2 недель после покупки. Один из уроков бесплатно выкладывал тут — можно посмотреть и оценить формат ( https://t.me/google_sheets/1640). Подробная программа, скриншоты с примерами и покупка — все по ссылке: https://shagabutdinov.ru/pivot_googleВот несколько картинок из курса ( больше по ссылке): | |
| | Секреты взрослой жизни от Гретхен Рубин | |
| У Гретхен Рубин есть несколько книг, в том числе весьма популярная и изданная на русском книга «Проект счастье». Но я купил последнюю книгу, которая вышла на английском буквально на днях и разочаровался. Она стоила недорого для электронной книги на Амазоне (около 9 долларов), но за эти деньги можно было купить хорошую бумажную книгу в России… Книга короткая и не очень содержательная. По сути, сборник афоризмов и коротких фраз. Вот несколько неплохих цитат: сэкономлю деньги хотя бы вам, раз самому не получилось 😊 Этой книге далеко до действительно крутой книги Кевина Келли в схожем формате ( тут цитаты из нее) Одно из ключевых решений — изменить свою карьеру юриста на карьеру писательницы. Я поняла, что лучше буду ошибаться как писатель, чем преуспевать как юрист. Перфекционизм объясняется не высокими стандартами, а тревожностью. Работа — одна из самых опасных форм прокрастинации. Удача играет огромную роль в успехе. Тяжелый труд притягивает удачу. | |
| | Две лучших биографии Гагарина | |
| | Недавно был День космонавтики. Искренне рекомендую две книги — лучшую российскую и лучшую переводную биографии Гагарина. Даже если вам не очень интересна тема космоса, получите удовольствие от чтения, уверен. | |
| | Интересная идея от Джеймса Клира (автора бестселлера «Атомные привычки»):
Представьте, что вашей жизнью один день будет управлять внешний наемный CEO (генеральный директор). Квалифицированный. От чего он избавится в первую очередь? | |
| | Немного внутренней кухни про выступления | |
| | Недавно проводил вебинар про организацию работы над книгой для слушателей курса «Цех». Три часа пролетели на одном дыхании!
У меня на вебинарах всегда много источников, цитат и историй. Как все это коплю? В течение месяцев/лет, непрерывно и постоянно, заношу интересное в Google Keep (у вас может быть другой инструмент для заметок — это не принципиально) с соответствующим ярлыком. Например, «Цех» или «Excel». Потом перед вебинаром на «Цехе», допустим, я открываю заметки только с этим ярлыком и точно не пропущу ничего из прочитанного-накопленного за все время. | |
| | Вы получили это письмо, поскольку подписались на новостную рассылку на сайте Рената Шагабутдинова | Вы можете отказаться от подписки в любое время — просто нажмите здесь | |
| |
