Сообщество - MS, Libreoffice & Google docs
Добавить пост

MS, Libreoffice & Google docs

711 постов 14 987 подписчиков

Популярные теги в сообществе:

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции

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

Ссылка на файл - https://disk.yandex.ru/i/Ahq-9fH2XmS_3Q

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

Что же, давайте приступим.

Задача

Есть столбец с текстом:

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

Нам необходимо в отдельные столбцы разнести код и наименование.

Решение

Первое, на что обращаем внимание, есть ли здесь закономерность? С кодом всё понятно. Нужно извлечь текст до первого пробела. Отлично. Находим порядковый номер первого пробела с помощью функции НАЙТИ (FIND):

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

Далее всё просто. С помощью функции ЛЕВСИМВ (LEFT) извлекаем всё, что до пробела, отнимая от порядкового номера пробела единицу:

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

Я показал пошаговое решение. Но это можно всё сделать и в одной формуле. Вместо D2 вставляем НАЙТИ (FIND):

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

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

Надо подсчитать, а сколько в принципе у нас пробелов в тексте. Сделать это можно вот как:

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

ДЛСТР (LEN)(А2) (как вам название?) - подсчитывает общее количество символов в ячейке. ДЛСТР(ПОДСТАВИТЬ(A2;" ";"")) - подсчитывает количество символов без пробелов. От них мы избавились благодаря функции ПОДСТАВИТЬ (SUBSTITUTE), которая заменила пробелы на пусто.

Теперь очень мудрёный шаг. Нам надо заменить последний пробел на какой-нибудь другой произвольный символ:

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

ПОДСТАВИТЬ (SUBSTITUTE)(A2;" ";"*";E2) как раз это и делает. Даём ей исходный текст, что нужно найти, на что заменить, и какой символ по порядку (именно для этого нам нужно было узнать, сколько всего пробелов).

Зачем нам это? Немного терпения, друзья, сейчас всё будет.

Логика решения меняется. Теперь нам надо извлечь текст между первым пробелом и звёздочкой. А вот это реализовать уже можно. Находим порядковый номер звёздочки:

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

Финальный шаг:

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

ПСТР (MID) - как много в этом слове... А функция, вообще-то, полезная. Указываем ей ячейку, порядковый номер, с которого хотим начать извлекать символы, и само количество символов.

А2 - понятно, ячейка с текстом.

D2+1 - начиная с какого символа будем извлекать (первый пробел + 1)

G2-D2-1 - количество извлекаемых символов. Порядковый номер звёздочки - порядковый номер первого пробела - 1 (чтобы исключить лишний символ в конце).

А теперь комбо! Всё в одной ячейке:

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

Говорил же, что будет страшно, а вы не верили.

Заключение

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

В файле, который идёт к этой статье, есть ещё один готовый пример. Это тоже вполне себе реальная задача, которую слушатели предложили решить во время корпоративного обучения. Исходный текст нужно было переделать в то, что указано в "Должно быть":

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

Здесь уже расписывать не буду, а то ещё одна "простыня" получится. Кому интересно, посмотрите. Там, правда, уже не так всё подробно по шагам расписано. Но я в вас верю!

Можно и ещё более сложные конструкции использовать. В комментариях к прошлой статье были варианты с обработкой пустых ячеек. Про всё не расскажешь. Если честно, я перед собой такую цель и не ставлю. Главное, что я хотел вам показать - это то, что прежде, чем кидаться искать функции, посмотрите, а есть ли вообще логика в том, что вы хотите? Если есть, тогда и решение найдётся.

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

П.С. недавно проводил мастер-класс по разным штукам в Excel, про которые здесь почти не писал. Например, рассказал про то, что такое расширенный фильтр.

Ссылка - (59) Полезные приёмы при работе в Excel. Часть 2. - YouTube

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

ChatGPT и Excel

В комментариях к одной из многочисленных тем с помощью в Excel, Боги Excel, помогите чайнику! в шутку или всерьез пользователь @lostoldnameagain предложил

ChatGPT и Excel Microsoft Excel, Таблицы Excel, Нейронные сети, Будущее наступило

А если действительно спросить?

Прямо так, практически как сформулировал ТС, только уточним, что все в Excel

В программе Excel в столбце A массив чисел от 0 до 1. Как вывести из этого массива числа от 0,097 до 0,101 в отдельный столбец?

Ответ, формально верный, но не совсем подходящий

Вы можете отфильтровать данные в столбце A, чтобы показать только значения от 0,097 до 0,101, а затем скопировать и вставить эти значения в новый столбец. Вот шаги: 1. Выберите диапазон ячеек, содержащий данные в столбце A. 2. Щелкните вкладку «Данные» на ленте Excel. 3. Нажмите кнопку «Фильтр», чтобы добавить фильтр к данным. 4. Щелкните стрелку раскрывающегося списка в заголовке столбца A и снимите флажок «Выбрать все». 5. Установите флажки рядом со значениями 0,097, 0,098, 0,099, 0,1 и 0,101, чтобы в столбце отображались только эти значения. 6. Скопируйте отфильтрованные значения и вставьте их в новый столбец.

Уточним немного запрос:

В программе Excel в столбце A массив чисел от 0 до 1. Как вывести из этого массива числа от 0,097 до 0,101 в отдельный столбец? Нельзя применять фильтр по отдельным значениям чисел.

И получаем уже вполне рабочий ответ:

Вы можете использовать функции «ЕСЛИ» и «И» с логическими операторами для отображения чисел от 0,097 до 0,101 в отдельном столбце. Предполагая, что ваши данные начинаются в ячейке A1, в ячейке B1 вы можете использовать следующую формулу: `=ЕСЛИ(И(A1>=0,097, A1<=0,101), A1, "")` Затем перетащите эту формулу вниз, чтобы заполнить остальные ячейки в столбце B. Это отобразит числа от 0,097 до 0,101 в столбце B, а остальные ячейки останутся пустыми.

И это, блин, реально круто.

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

С юбилеем ! Сегодня ровно 45'000 дней с момента начала исчисления дат по версии Microsoft Excel

Все версии Excel для Windows вычисляют даты на основе "системы дат 1900".

Отсчёт ведётся от 0 января 1900 года 00 часов 00 минут.

Если в ячейку Excel вы введёте 0 и выберете формат ячейки "длинный формат даты", то увидите там именно это :-)

С юбилеем ! Сегодня ровно 45'000 дней с момента начала исчисления дат по версии Microsoft Excel Microsoft Excel, Подсчет, Дата, Календарь, Юбилей

Но и это ещё не всё :-)

Если из сегодняшней даты, 15.03.2023, вычесть "абсолютный" 0, то вы получите ровно 45'000 дней.

С юбилеем ! Сегодня ровно 45'000 дней с момента начала исчисления дат по версии Microsoft Excel Microsoft Excel, Подсчет, Дата, Календарь, Юбилей

С юбилеем вас, товарищи :-)

P.S. Официальная справка Microsoft гласит: "В системе дат 1900 даты вычисляются с использованием 1 января 1900 г. в качестве отправной точки. При вводе даты она преобразуется в порядковый номер, который представляет количество дней, за которое прошло с 1 января 1900 г.", но ЗА НОЛЬ принимается именно 0 января 1900 :-)

P.P.S. А узнал я это благодаря табличке по оплате ипотеки, которую веду в Excel. Удалив дату увидел что платить мне через -45'000 дней, но к сожалению платить мне ещё долго :-)

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

История размеров слайда в PowerPoint

С момента как я узнал, что размер слайда в PowerPoint можно менять, мне не давало покоя, почему он равен 19.05 x 33.867 см или 7.5 x 13.333". Особенно смущало, что по горизонтали размер задан до тысячных. Какая точность! Но почему?

История размеров слайда в PowerPoint Microsoft PowerPoint, Слайды, Видео, YouTube, Без звука, Длиннопост

Чтобы разгадать эту загадку, отправимся в 1987-ой. Хороший выдался год! В Ванкувере открывается первый Starbucks за пределами США, Маргарэт Тэтчер дает интервью телевидению СССР, выходит первая серия Симпсонов, Матиас Руст сажает свой Цессна 172 «Скайхоук» на Большом Москворецком мосту, Nike выпускает кроссовки AirMax, а в Кремниевой долине выходят первые версии Photoshop и PowerPoint.

Девяностые

PowerPoint 1.0 вышел в 1987 году для Mac и лишь в 1990 для Windows. Эта программа не только один из ключевых инструментов моей работы, мы с ней еще и одногодки.

36 лет назад презентовали разными способами, интересный экскурс на эту тему сделали Visme, мы же сфокусируемся на оверхед-проекторе (кодоскоп, графопроектор). Слайды для таких презентаций кропотливо создавались вручную на специальных прозрачных пленках, дизайн стоил больших денег, а вносить правки за полчаса до дедлайна было невозможно. Золотые были времена.

Демонстрация работы оверхед-проектора.

Появление PowerPoint сменило парадигму. И хотя еще долгое время слайды оставались аналоговыми и PP лишь упрощал их создание, до полного перехода в цифру и тотальной демократизации процесса оставались считанные годы.

История размеров слайда в PowerPoint Microsoft PowerPoint, Слайды, Видео, YouTube, Без звука, Длиннопост

Обратите внимание на первые иконки – слайд рисуется рукой. Эволюция иконки PowerPoint. Автор: Владимир Бугаев

Назывались такие пленки transparency, viewfoil или viewgraph. Размер составлял 8.5 x 11". Ниже пару упоминаний из источников разных лет.

История размеров слайда в PowerPoint Microsoft PowerPoint, Слайды, Видео, YouTube, Без звука, Длиннопост

Grist, Speaking of Interpretation, 1964, Источник: Google Books

История размеров слайда в PowerPoint Microsoft PowerPoint, Слайды, Видео, YouTube, Без звука, Длиннопост

Заметка на сайте АНБ о том как презентовали до PowerPoint. Источник: Агентство Национальной Безопасности США

Возможно, размер слегка варьировался от проектора к проектору, либо в PowerPoint сделали отступы для печати, однако размер слайда c 1987 по 2010 год был на дюйм меньше: 7.5 x 10 или 19.05 x 25.4 см.

История размеров слайда в PowerPoint Microsoft PowerPoint, Слайды, Видео, YouTube, Без звука, Длиннопост

Размеры слайда в PowerPoint определил аналоговый формат пленок для слайдов на оверхед-проекторе.

Нулевые

История размеров слайда в PowerPoint Microsoft PowerPoint, Слайды, Видео, YouTube, Без звука, Длиннопост

Соотношение сторон экрана – это произведение количества квадратов по горизонтали и вертикали.

80-е и 90-е — время экранов с соотношением сторон 4:3, а вот в нулевые экраны постепенно стали расширяться. Или сплющиваться? Так или иначе, а стандартом к концу декады стало 16:9.

В Microsoft Office решили, что пора бы и слайдам сплющиваться. Новый формат под 16:9 сделали 14.29 x 25.4 см. То есть срезали по вертикали 4.76 см.

В PowerPoint 2010 добавили широкоэкранный формат слайдов.

Десятые

В 2013 передумали и все таки решили расширяться. Вернули по вертикали 19.5 см, а по горизонтали добавили 9.737 см. Тысячные доли появились из-за соотношения сторон 16:9. С тех пор размер слайда равен 19.05 x 33.867 см или 7.5 x 13.333".

В PowerPoint 2013 решили не уменьшать по вертикали изначальный формат слайда, а расширить его по горизонтали. И правильно сделали, так старый формат помещается в новый с меньшими потерями размеров.

Надеюсь, это было интересно не только мне :)

Кое-что еще о PowerPoint на Пикабу

10 необычных применений PowerPoint: от трехмерных боевых роботов до современного искусства

8 мест с бесплатными и качественными шаблонами презентаций

От ромба к кругу: история иконки PowerPoint

5 фишек PowerPoint, о которых вы не знали

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

Помогите с либраофис

На работе перешли на либру. И столкнулись с такой проблемой, что в доковских файлах в таблице, когда проставляешь + перед числом, то он просто исчезает. Иногда срабатывает поставить + потом энтер, потом удалить этот энтер. Но иногда не помогает и вообще ничего не работает. Может кто знает как решить эту проблему?

Мгновенное заполнение в Excel - магия в чистом виде

Друзья, всем привет. Сегодня хочу рассказать вам про мгновенное заполнение в Excel.

Ссылка на файл, чтобы можно было потренироваться - https://disk.yandex.ru/i/HyW0N215F6CuUg

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

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

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

Извлечение данных

Предположим, у нас есть вот такой столбец с текстом:

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

Нам нужно извлечь отдельно номер договора и дату. Это можно сделать с помощью инструмента "Текст по столбцам". Правда, потом придётся от символа "№" ещё избавляться. А вот мгновенное заполнение справится с этим намного быстрее. Просто вводим справа от текста в первую ячейку номер договора (1), нажимаем Enter. Далее возможны два варианта.

Вариант 1. Вручную вводим в ячейку первую цифру второго договора (2). Excel предлагает свои варианты, жмём Enter - PROFIT!

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

Вариант 2. После того, как перешли ко второй ячейке, сразу нажимаем сочетание Ctrl + E (Е английская, конечно). Именно это сочетание отвечает за запуск мгновенного заполнения. Аналогично с датами. Вводим в ячейку С2 дату первого договора - Enter - Ctrl + E - наслаждаемся результатом.

ОЧЕНЬ ВАЖНАЯ ЧАСТЬ СТАТЬИ.

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

Ух ты! И так будет работать всегда?! Строго говоря - нет. Иногда, Excel не может с одной ячейки распознать логику. В этом случае нужно вручную заполнить не одну, а две, три, четыре (если случай совсем запущенный) ячейки. И только после этого нажимать Ctrl + E. Чем больше ячеек заполняешь, тем выше вероятность того, что твоя логика будет верно распознана могучим интеллектом Excel. Порой мгновенное заполнение не справляется с поставленной задачей:

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

Даты в первом столбце указаны в формате ГГГГ-ММ-ДД. При попытке привести их в формат ДД-ММ-ГГГГ получается вот такая "красота". Поэтому не поленитесь после того, как все ячейки будут заполнены, пробежаться по ним, а тот ли в них результат, который ты ожидал увидеть.

Образцы вводите в соседнем столбце от источника (можно справа или слева). Не "убегайте" далеко от данных, результат может быть непредсказуемым или вообще ничего не будет.

Ещё одно важное дополнение: мгновенное заполнение работает в версиях Excel 2013 и выше.

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

Извлечение только чисел из столбца

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

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

В данном конкретном случае я прописал вручную две первых ячейки, иначе Excel не понимал, что нужны только числа.

Работа с текстом

В столбце указаны Имя и Фамилия. Нам нужно получить результат в виде "Имя Ф." В первой ячейке вводим образец - Enter - Ctrl + E:

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

Кстати, если попробовать получить Фамилия И., то будьте внимательны. Если прописать два примера, потом начать вводить третий, то появляется довольно забавный список:

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

Но если не начинать вводить в третью ячейку текст, а сразу нажать на Ctrl + E, то всё будет нормально. Раз на раз не приходится. Временами мгновенное заполнение ведёт себя очень странно.

Извлечение части сплошного текста

Необходимо разбить слипшийся текст на части. Вводим в первых двух ячейках образец - Ctrl + E:

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

С номером поступаем аналогично.

Сбор текста

В отдельных столбцах есть различная информация, которую необходимо собрать в одно предложение. Обратите внимание, что порядок столбцов для мгновенного заполнения роли не играет. Прописываем предложение в первой ячейке - Enter - Ctrl + E:

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

Заключение

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

В качестве небольшой рекламы позвольте оставить здесь ссылку на мастер-класс, который я буду проводить 9 марта. Кто хочет узнать ещё несколько полезных приёмов при работе в Excel (там почти не будет того, о чём я писал здесь), а ещё хочет услышать чуть больше про то, где я работаю, записывайтесь - Полезные приемы при работе в Excel. Часть 2 (specialist.ru)

На этом всё. Как обычно, спасибо огромное всем, кто потратил своё драгоценное время и осилил данное полотно. Надеюсь, было полезно. Видео по данной статье обязательно появится на моём канале - (36) Андрей Митрохин - YouTube

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

Заполнение ячеек по условию

Добрый день!

Есть excel файл (microsoft office 2019), в нем 2 листа.

В первом упорядоченный список (около 200 строк):

Заполнение ячеек по условию Microsoft Excel, Microsoft office, Текст

Как мне сделать так, чтобы если я заполняю 3й столбец (по умолчанию он пуст), но заполняю не подряд (могу заполнить 2, 10, 15,123... и так далее строки), то у меня бы на втором листе заполнялся список подряд. Другими словами эти же строки бы копировались, но без пропусков строк, а подряд, и только те которые заполнены полностью (с датой).

Есть предположение что эта нужно смотреть в сторону если, поискпоз или ВПР, но судя по всему не хватает знаний. Реализовать не могу, очень надеюсь что тут не нужен VBA. В нем я совсем ноль

Вы заканчивали онлайн-магистратуру? Расскажите, пожалуйста, о своем опыте!

Дистанционное обучение сегодня круче, чем когда-либо. Даже магистратуру теперь можно закончить из дома, да еще и с дипломом государственного образца.

Если вы получали высшее образование онлайн, поделитесь, пожалуйста, своим опытом. Нам интересно ваше мнение.

Вопрос по Word. Анимация изменения размеров таблицы

Раньше при изменении размера таблицы в Word она двигалась вместе с содержимым, а сейчас показывается только контур. Все настройки облазил, так и не нашел, где включить, чтоб отображалась вся табличка при изменении размера. Подскажите, пожалуйста, может кто знает, как это сделать?

Отличная работа, все прочитано!