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

MS, Libreoffice & Google docs

203 поста 8 549 подписчиков
187

Статистика сообщества за полгода

Внимание!

Проводится голосование за ачивку пользователю

https://pikabu.ru/surveys.php?id=zG9sO3GEynvL

Приветствую всех подписчиков сообщества MS, Libreoffice & Google docs!

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


За этот короткий срок было опубликовано 104 поста, а у сообщества появилось 7410 подписчиков и количество растёт! Это несомненно радует, что тема сообщества интересна Пикабушникам, а также вдохновляет авторов на написание новых постов.

Статистика сообщества за полгода Microsoft Excel, Статистика

Многие не жалуют новорегов за то, что они постят бессмысленную дичь, но это не относится к новичкам нашего сообщества @ExceLifeHack, @Petrov210217, @SensaiKudisai. Они постят годный контент, который многие сохраняют, а авторы получают благодарность и обрастают подписчиками))


В нашем сообществе есть даже девушки @1Irina1985 и @mymamalama, посты которых получили теплый приём и плюсы в репутацию)) продолжайте пилить посты, у вас это хорошо получается)

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


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

Статистика сообщества за полгода Microsoft Excel, Статистика

Скачать таблицу можно здесь.

Этот пост будет в закрепе, поэтому вы всегда сможете быстро посмотреть нужную вам информацию.


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

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

Как фильтровать строки в Google Таблицах по дате?

В Excel при фильтрации колонки с датами есть такое замечательно древо группировки - take.ms/i69O6

Сначала все даты группируются в года, потом в месяца, а потом уже можно выбирать дни. Благодаря этому можно быстро фильтровать строки таблицы по годам и месяцам.

В Гугл Таблицах это просто список значений на каждую дату - take.ms/NCWmb . При этом упорядочивается этот список по первой цифре, а не по календарной последовательности. То есть 28.05.14 оказывается после 27.08.14 и перед 30.04.14.

Как можно быстро посмотреть все строки таблицы с датами, относящимися к одному месяцу?

Текст вопроса полностью содран отсюда https://qna.habr.com/q/107327 но ответа там нет. Буду очень благодарна за подсказки, поскольку поиск по инструкциям ничего не дал((

23

Фильтр по датам в гугл таблицах

Вопрос: как сделать так, чтобы фильтр в гугл таблицах отображался так же, как в эксель?

в эксель это выглядит так:

берем произвольные даты

Фильтр по датам в гугл таблицах Microsoft Excel, Google docs, Длиннопост

включаем фильтр и видим группировку по месяцам

Фильтр по датам в гугл таблицах Microsoft Excel, Google docs, Длиннопост

делаем то же самое в гугл таблицах

Фильтр по датам в гугл таблицах Microsoft Excel, Google docs, Длиннопост

группировка отсутствует, отображаются только даты.

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

Исключение неверных текстовых значений в Excel

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

1075

Динамические выпадающие списки Excel

Ты уже наверняка создавал выпадающие списки в Excel. Делается это очень просто: выбираем ячейку, в которой нужен выпадающий список (1), идём в Данные -> Проверка данных (2).

Динамические выпадающие списки Excel Microsoft excel, Vba, Видео, Длиннопост

В открывшемся окне, в поле «Тип данных» выбираем «Список» (3), и затем кликнув в поле «Источник» выделяем данные с рабочего листа для выпадающего списка (4). Подтверждаем нажатием на «Ок» (5).

Динамические выпадающие списки Excel Microsoft excel, Vba, Видео, Длиннопост

Теперь при нажатии на символ с выпадающего списка мы можем выбирать один из определенных в источнике вариантов.

Динамические выпадающие списки Excel Microsoft excel, Vba, Видео, Длиннопост

Однако, проблема заключается в том, что созданный таким образом выпадающий список является статичным. То есть если в таблице появится еще одна строка, выпадающий список останется прежним – соответственно неполным.


Чтобы обойти данную проблему и сделать выпадающий список полностью динамичным, следует сделать следующее. Во-первых, данные следует хранить в умных таблицах, так как благодаря структурированным ссылкам они позволяют динамически отслеживать диапазоны каждого столбца. Вот пример структурированной ссылки на значения нужного нам столбца «Модель»:

Динамические выпадающие списки Excel Microsoft excel, Vba, Видео, Длиннопост

Копируем текст этой структурированный ссылки и вставляем её в поле «Источник» окна добавления выпадающего списка. Поскольку в этом поле нельзя напрямую вписывать структурированные ссылки, «оборачиваем» нашу структурированную ссылку в функцию ДВССЫЛ (Не забудь! Текст структурированной ссылай должен быть написан в кавычках!). По итогу, в поле «Источник» у тебя должно быть вписано следующее:

Динамические выпадающие списки Excel Microsoft excel, Vba, Видео, Длиннопост

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


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

Приятного просмотра! 😊

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

Как обрезать изображение по кругу

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


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

Чтобы это реализовать, выберите нужную картинку и перейдите в раздел Формат → Обрезать → Обрезать по фигуре.

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

Как обрезать изображение по кругу Microsoft Excel, Обрезка, Рисунок, На заметку
92

Январь, Феварь, Марарь, Апрарь… Excel?!

Наверняка ты при работе в Excel уже замечал выскакивающие иногда попытки программы помочь тебе в заполнении того или иного столбца – это так называемое «Мгновенное заполнение» (Flash Fill), работающее для распознавания шаблонов в твоих действиях.

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

1. Скопируй весь этот столбец с сокращёнными названиями месяцев и вставь в рабочем листе Excel, чтобы вышло как у меня в демонстрации:


янв

фев

мар

апр

май

июн

июл

авг

сен

окт

ноя

дек

Январь, Феварь, Марарь, Апрарь… Excel?! Microsoft Excel, Vba, Гифка, Видео, Длиннопост

2. Пропиши в соседней (слева или справа) от «янв» ячейке  полное название месяца «Январь»

Январь, Феварь, Марарь, Апрарь… Excel?! Microsoft Excel, Vba, Гифка, Видео, Длиннопост

3. Пока ты писал слово «Январь», Flash Fill уже искал закономерности в том, что ты делаешь. И сейчас ты в этом убедишься: Переходи в ячейку ниже и также начинай прописывать слово «Февраль». А вот и врывается в игру «Мгновенное заполнение», правда, достаточно интересным способом. Если нажимаем на Enter, то Excel «протягивает» предложенное решение:

Январь, Феварь, Марарь, Апрарь… Excel?! Microsoft Excel, Vba, Гифка, Видео, Длиннопост

В итоге мы имеем январь, феварь, марарь, апрарь, майарь, июнарь, июларь, авгарь, сенарь, октарь, нояарь и декарь.

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

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

Январь, Феварь, Марарь, Апрарь… Excel?! Microsoft Excel, Vba, Гифка, Видео, Длиннопост

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

Кроме того, если ты хочешь узнать еще больше разных советов по работе в Excel (и особенно по автоматизации с помощью VBA), то приглашаю тебя подписаться на мой канал на YouTube! Вот, например, плейлист по шорткатам / фишкам и особенностям работы с функциями и формулами:

А это новый курс по VBA для новичков:

До встречи! 😊

P.S. Комбинация клавиш для того, чтобы заставить Excel попробовать найти шаблоны: CTRL + E

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

Динамический график с картой – работает с любой версией Excel!

В последних версиях Excel есть возможность использовать встроенный тип графика «Картограмма»:

Динамический график с картой – работает с любой версией Excel! Microsoft Excel, График, Диаграмма, Видео

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

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

Приятного просмотра! 😊

429

Извлечение чисел из текста в Excel

Извлечь числа из строки текста в Excel, естественно можно с помощью формул. Например, в этом может помочь следующая формула массива:

Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост

Тем не менее, у использованной выше формулы есть определенные минусы:


• Во-первых, все числа, например, из текста «Задача 5 от 19 Ноября» выдаются не разделёнными, образую таким образом одно слитное число, тогда же как информация о том, что числа на самом деле в оригинальном тексте разделены другими словами потенциально может быть важной.


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


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

Function extractDelimitedNumbers(ByVal strOriginalText As String) As String

Dim strExtractedNumbers As String

Dim lngTextLength As Long

lngTextLength = Len(strOriginalText)

Dim lngPositionCounter As Long

'Проверка, указано ли название файла

If strOriginalText <> "" Then

'Проверка каждой позиции названия

For lngPositionCounter = 1 To lngTextLength

'Если число...

If IsNumeric(Mid(strOriginalText, lngPositionCounter, 1)) = True Then

'... то сохраняем в переменную

strExtractedNumbers = strExtractedNumbers & Mid(strOriginalText, lngPositionCounter, 1)

'Разделение отдельно стоящих в названии чисел с помощью "_"

If lngPositionCounter + 1 <= lngTextLength Then

If IsNumeric(Mid(strOriginalText, lngPositionCounter + 1, 1)) = False Then

strExtractedNumbers = strExtractedNumbers & "_"

End If

End If

End If

Next lngPositionCounter

'Удаляем по итогу лишний нижний пробел, если таковой имеется

If Right(strExtractedNumbers, 1) = "_" Then

strExtractedNumbers = Left(strExtractedNumbers, Len(strExtractedNumbers) - 1)

End If

extractDelimitedNumbers = strExtractedNumbers

Else:

extractDelimitedNumbers = ""

End If

End Function

Как использовать этот код:

1. Открыть файл Excel, в котором нужно применить функцию (лучше его копию)

Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост

2. Открыть редактор VBA с помощью комбинации клавиш Alt+F11

Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост

3. В верхнем левом углу нажать на «Insert» и затем «Module».

Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост

4. Скопировать текст функции и вставить в открывшееся окно в центре редактора VBA

Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост

5. Сохранить файл в формате xlsm (формат xlsx не сохраняет макросы!). Для этого открываем окно сохранить как при помощи клавиши F12 либо File -> Save as -> Browse. По открытии окна сохранения файла в поле «Тип файла» выбираем «Книга Excel с поддержкой макросов»

Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост

6. Подтверждаем сохранение. Теперь функция может использоваться как самая обычная функция на рабочем листе Excel. То есть ставим знак равно, и прописываем название нашей пользовательской функции «extractDelimitedNumbers». В скобках указываем текст, из которого должны быть извлечены числовые значения:

Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост
Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост

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

Конечно, для этого видео нужно уже владеть определенными знаниями VBA. Если ты еще совсем новичок в области VBA, то также могу предложить посмотреть вот этот курс VBA:

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

Приемы редактирования текста в VS Code: мультикурсор

Приемы редактирования текста в VS Code: мультикурсор Полезное, Редактирование текста, Длиннопост

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


При работе с текстовыми файлами часто возникает необходимость массового редактирования некоторых фрагментов. С простыми случаями (заменить 'abc' на 'def') справляется интуитивно понятная функция автозамены, которая есть в любом распространённом редакторе. Но что делать, если Вам нужно, например, найти все слова, начинающиеся на букву "t", и поменять их местами со словом, которое идет после них?


Мультикурсор через поиск


Для начала давайте разберёмся, как составить выражение для поиска. Откроем стандатное окно поиска командой Ctrl-F (от слова Find) и вводим ' t'. Редактор автоматически подсвечивает найденные места (буква t, перед которой есть пробел), и мы можем заметить, что забыли о словах, перед которыми стоит знак препинания (например, кавычка или дефис). К счастью, в задачах вроде нашей приходят на помощь регулярные выражения, в частности, вида '\Wt', где \W - специальная последовательность, обозначающая начало слова. Активировать функцию "регулярок" можно кнопкой .*


Регулярные выражения - это отдельная очень большая тема. Если кому-то интересно, пишите в комментариях - напишу отдельный пост, посвященный им. Также можете изучить их самостоятельно на regexone.com и потренироваться на regex101.com


Теперь мы можем нажать Alt-Enter, и редактор вставит по курсору в каждом месте, где был найден искомый фрагмент:

Приемы редактирования текста в VS Code: мультикурсор Полезное, Редактирование текста, Длиннопост

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

Приемы редактирования текста в VS Code: мультикурсор Полезное, Редактирование текста, Длиннопост

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


Теперь можно вырезать выделенные фрагменты (Ctrl-X), переместиться на одно слово вправо (Ctrl-вправо) и вставить их (Ctrl-V). Если количество курсоров при вставке осталось неизменным, то каждый вырезанный фрагмент будет вставлен на своё место:

Приемы редактирования текста в VS Code: мультикурсор Полезное, Редактирование текста, Длиннопост

Конечно, пример "высосан из пальца", но есть немало задач, где этот способ будет полезен. Например, найти и заменить все email-адреса в тексте, исправить тэги в html или xml файле, поменять местами колонки в файле csv.


Вставка мультикурсора вручную


Множественные курсоры можно также вставить вручную, командами Ctrl-D (следующий совпадающий с выделенным фрагмент) и Ctrl-Shift-L (вставить курсоры в каждый фрагмент, совпадающий с выделенным). Например, я выделил фрагмент 'текст' и два раза нажал Ctrl-D:

Приемы редактирования текста в VS Code: мультикурсор Полезное, Редактирование текста, Длиннопост

Кстати, вам не нужно заучивать сочетания клавиш, которые я упомянул. Достаточно лишь запомнить "магическую" команду Ctrl-Shift-P, которая даёт доступ ко всем командам:

Приемы редактирования текста в VS Code: мультикурсор Полезное, Редактирование текста, Длиннопост

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

Приемы редактирования текста в VS Code: мультикурсор Полезное, Редактирование текста, Длиннопост

В эта статье я рассказал лишь о нескольких функциях, которыми обладает этот редактор. Вы можете найти больше полезной информации на официальном сайте: Visual Studio Code Tips and Tricks. Поделитесь в комментариях, какие функции и расширения VS Code Вы считаете самыми полезными.


TL;DR

Основные команды:

Ctrl-D - добавлять курсоры по одному, на каждое совпадение (с выделенным куском)

Ctrl-Shift-L - добавить курсоры на все совпадения

Alt-Enter (в окошке поиска) - добавить курсоры на все совпадения к запросу

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