Сообщество - Лайфхак
Лайфхак
421 пост 9 386 подписчиков
1390

Очистка текста в Excel

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


1. Когда в названиях городов присутствует лишняя буква «г.». Выделяем текст, нажимаем «Ctrl+H» и дедовским способом заменяем «г.» на пустую строку.

Очистка текста в Excel Excel, Таблица, Полезное, Лайфхак, На заметку, Текст, Длиннопост

2. Дальше почистим текст от лишних пробелов, которые встречаются не только в начале слова, но и в середине, и в конце, оставив нужный пробел только между слов. Для этого в соседней строке вводим формулу =СЖПРОБЕЛЫ(A1:A3) и нажимаем «Enter», затем протягиваем край ячейки на всю длину текста

Очистка текста в Excel Excel, Таблица, Полезное, Лайфхак, На заметку, Текст, Длиннопост

3. Теперь очистим текст от апострофов, которые встречаются при выгрузке из различных баз данных и 1С, а также переносов слов (Alt+Enter), которые некоторые пользователи так любят ставить. Минус апострофа в том, что его не видно в ячейке, но он мешает при сортировке и вручную его не убрать. Для этого в соседней строке вводим формулу =ПЕЧСИМВ(A1:A3) и нажимаем «Enter», затем протягиваем край ячейки на всю длину текста

Очистка текста в Excel Excel, Таблица, Полезное, Лайфхак, На заметку, Текст, Длиннопост

4. Определим, где встречаются латинские буквы. Для этого есть макрос, но латинские буквы не так часто попадаются в тексте, чтобы менять формат документ на «xlsm», поэтому используем простой способ. Выделяем текст и применяем любой шрифт, не имеющий кириллицы, латинские буквы сразу станут видны.

Очистка текста в Excel Excel, Таблица, Полезное, Лайфхак, На заметку, Текст, Длиннопост

5. После того, как весь текст будет очищен, копируем данный текст и вставляем его в свободные столбцы использовав специальную вставку «Значение». В этом случае вставляется только сам текст без привязки к формулам.

Очистка текста в Excel Excel, Таблица, Полезное, Лайфхак, На заметку, Текст, Длиннопост

6. Остальные столбцы удаляем, а в освободившееся время, читаем пикабу, работаем))

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

Расширенный фильтр или умная таблица EXCEL

Видео пример работы

Расширенный фильтр или умная таблица EXCEL Excel, Длиннопост, Лайфхак, Гифка

Для начала создаем 2 таблицы, первая это диапазон условий, вторая наши данные

Расширенный фильтр или умная таблица EXCEL Excel, Длиннопост, Лайфхак, Гифка

Теперь нажимаем дополнительно

Расширенный фильтр или умная таблица EXCEL Excel, Длиннопост, Лайфхак, Гифка

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

Расширенный фильтр или умная таблица EXCEL Excel, Длиннопост, Лайфхак, Гифка

Теперь нужно добавить макрос на лист

Расширенный фильтр или умная таблица EXCEL Excel, Длиннопост, Лайфхак, Гифка
Расширенный фильтр или умная таблица EXCEL Excel, Длиннопост, Лайфхак, Гифка

1. Это диапазон условий, замете что он начинается с А2 заголовок я пропускаю
2. Это откуда начинаются наши данные А7, заголовок тоже пропускаем

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A2:G5")) Is Nothing Then

On Error Resume Next

ActiveSheet.ShowAllData

Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1").CurrentRegion

End If

End Sub

Сохраняем, и тестим

Расширенный фильтр или умная таблица EXCEL Excel, Длиннопост, Лайфхак, Гифка
Расширенный фильтр или умная таблица EXCEL Excel, Длиннопост, Лайфхак, Гифка

Дополнил пост
Можно делать поиск по неполному условию

Расширенный фильтр или умная таблица EXCEL Excel, Длиннопост, Лайфхак, Гифка

Поиск пустых ячеек

Расширенный фильтр или умная таблица EXCEL Excel, Длиннопост, Лайфхак, Гифка

Поиск по количеству символов

Расширенный фильтр или умная таблица EXCEL Excel, Длиннопост, Лайфхак, Гифка

Искать все слова без буквы А

Расширенный фильтр или умная таблица EXCEL Excel, Длиннопост, Лайфхак, Гифка

Поиск больше указанного числа

Расширенный фильтр или умная таблица EXCEL Excel, Длиннопост, Лайфхак, Гифка
Показать полностью 12
800

Смекалка или хитропопость

Прочитав пост про смекалку детей в Турции на водяных горках https://pikabu.ru/story/smekalochka_6725684

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

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

Смекалка или хитропопость Австрия, Отдых, Смекалка, Лайфхак, Дети, Хитрожопость

(Картинка стола из интернета)


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


Брат мой, так что тег "моё".

587

Суммирование чисел, написанных через дробь

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

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

Для начала выделите ячейки с цифрами и задайте им текстовый формат, чтобы числа не переводились в даты. Затем в первой ячейке, как указано на скриншоте, введите формулу =СУММПРОИЗВ(--ЛЕВСИМВ(B3:F3;ПОИСК("/";B3:F3)-1))

Суммирование чисел, написанных через дробь Excel, Таблица, Сложение, Лайфхак, Полезное, На заметку, Длиннопост

А во второй =СУММПРОИЗВ(--ПСТР(B3:F3;ПОИСК("/";B3:F3)+1;999)) затем, удобным для вас способом, протяните обе формулы на другие ячейки вниз.

Суммирование чисел, написанных через дробь Excel, Таблица, Сложение, Лайфхак, Полезное, На заметку, Длиннопост

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

245

Спарклайны

Продолжаю серию постов по Excel.

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

Для этого нужно выделить пустые ячейки, куда мы хотим поместить спарклайны, и на вкладке «Вставка» выбрать кнопки группы «Спарклайны»:

Спарклайны Excel, Таблица, Лайфхак, Полезное, Длиннопост

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

Спарклайны Excel, Таблица, Лайфхак, Полезное, Длиннопост

Применив кнопку «Гистограмма» мы получим мини-графики, которым в разделе «Конструктор» можно задать цвет минимальных и максимальных значений:

Спарклайны Excel, Таблица, Лайфхак, Полезное, Длиннопост

Аналогичным способом, применив кнопку «График», указываем диапазон данных, в результате мы получим кривую линию отображающую динамику цифровых значений:

Спарклайны Excel, Таблица, Лайфхак, Полезное, Длиннопост

Во вкладке «Конструктор» можно добавить «Маркеры», тогда каждое значение будет отмечено точкой. Здесь также можно подобрать цвет линий и точек.

Спарклайны Excel, Таблица, Лайфхак, Полезное, Длиннопост

Кнопкой «Выигрыш/проигрыш» мы получаем положительные и отрицательные значения, оно подходит для небольших цифр, например результаты матчей.


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

Спарклайны Excel, Таблица, Лайфхак, Полезное, Длиннопост

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

Спарклайны Excel, Таблица, Лайфхак, Полезное, Длиннопост
Показать полностью 4
368

Подсветка дат за 30 и более дней

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

Здесь я покажу формулу для выделения дат за 30 и более дней.

Чтобы не повторяться, я не буду подробно описывать каждое действие, потому что при вводе формул в «Условное форматирование» используется одинаковый принцип.

1. Создаём таблицу в Excel.

2. Создаём текущую обновляемую дату. Наводим курсор на удобное для вас место и вводим формулу: =СЕГОДНЯ()

3. Чтобы подсветить даты за 3 месяца вперёд выделяем всю таблицу Excel (без шапки) и выбираем на вкладке ГлавнаяУсловное форматирование Создать правило. В открывшемся окне задаём последний тип правила: Использовать формулу для определения форматируемых ячеек и вводим в строку следующую формулу: =$H1<СЕГОДНЯ()+120

Не забываем в графе «Формат» выбрать цвет заливки.

4. Аналогичным способом, для подсветки дат за 2 месяца вперёд, в новое правило вводим: =$H1<СЕГОДНЯ()+90

5. Также подсвечиваем даты за 1 месяц вперёд: =$H1<СЕГОДНЯ()+60

6. Даты меньше текущей даты подсветим с помощью формулы: =$H1<$L$2

7. Для пустых ячеек вводим: =ЕПУСТО($H1).

В итоге у вас должно получиться так:

Подсветка дат за 30 и более дней Excel, Таблица, Контроль, Срок, Полезное, Лайфхак

Также для ещё большего удобства можно создать фильтр, где имеется сортировка по цвету.

9833

15 неэтичных лайфхаков

1

15 неэтичных лайфхаков Лайфхак, Неэтично, Длиннопост, Reddit, Мат
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆


2

15 неэтичных лайфхаков Лайфхак, Неэтично, Длиннопост, Reddit, Мат
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆


3

15 неэтичных лайфхаков Лайфхак, Неэтично, Длиннопост, Reddit, Мат
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆


4

15 неэтичных лайфхаков Лайфхак, Неэтично, Длиннопост, Reddit, Мат
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆


5

15 неэтичных лайфхаков Лайфхак, Неэтично, Длиннопост, Reddit, Мат

-inurl:(htm | html | php | pls | txt) intitle:index.of "last modified" (mp4 | wma | aac| avi)

☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆


6

15 неэтичных лайфхаков Лайфхак, Неэтично, Длиннопост, Reddit, Мат
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆


7

15 неэтичных лайфхаков Лайфхак, Неэтично, Длиннопост, Reddit, Мат
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆


8

15 неэтичных лайфхаков Лайфхак, Неэтично, Длиннопост, Reddit, Мат
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆


9

15 неэтичных лайфхаков Лайфхак, Неэтично, Длиннопост, Reddit, Мат
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆


10

15 неэтичных лайфхаков Лайфхак, Неэтично, Длиннопост, Reddit, Мат
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆


11

15 неэтичных лайфхаков Лайфхак, Неэтично, Длиннопост, Reddit, Мат
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆


12

15 неэтичных лайфхаков Лайфхак, Неэтично, Длиннопост, Reddit, Мат
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆


13

15 неэтичных лайфхаков Лайфхак, Неэтично, Длиннопост, Reddit, Мат
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆


14

15 неэтичных лайфхаков Лайфхак, Неэтично, Длиннопост, Reddit, Мат
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆


15

15 неэтичных лайфхаков Лайфхак, Неэтично, Длиннопост, Reddit, Мат

☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆

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

Чья машина?

Сейчас остановился возле небольшого продуктового магазина местного масштаба, выхожу из машины и прохожу возле разгрузочной зоны. Стоит Газель, мужик хочет запарковаться на разгрузку, а ему мешает пустая Тойта Королла преградившая ему путь встав возле ворот.
Я прохожу мимо, а водитель газельки мне крикнул - "молодой человек, если вы в магазин идете, не могли бы там на входе спросить чья машина?"
Мне не сложно, я не гордый, сказал что окрикну на входе, но бегать искать ни кого не буду. Водитель меня поблагодарил, а я пошёл в магазин.
Захожу в магазин, а он небольшой такой, практически всё на ладони, стоят люди на кассе, кто-то рядом в полках ковырется, народу не много. Ну я беру и громко один раз спрашиваю - "чья там белая тойота королла?" И после этого я выдерживаю небольшую паузу.... в зале тишина, всем похуй. И я продолжаю - "Там Газель ей крыло смяла!"
И сразу мужик который в полках что-то копался, со словами - "чё за хуйня?!" быстрым шагом пошёл на улицу. А я прошёл в середину магазина, мужика этого больше не видел уже.

819

Завтрак за 3 минуты

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

Завтрак за 3 минуты Блины, Приготовление, Еда, Вкусно, Лайфхак, ЗОЖ
581

Операторы и реклама

Лайфхак, как Я победил навязчивые звонки от операторов, банков, бирж и т.д..

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

Обычно диалоги имеют такой вид:

-Здравствуйте, мы хотим предложить вам мазь для уве..
—Здравствуйте, похоронное агенство "name", всегда к вашим услугам
-Извините, не туда попал
—И вам всего доброго, до скорой встречи.
Иногда особо отбитые продолжают говорить по скрипту и пытаться что либо нам впарить, то тогда стараемся рекламировать услуги нашего "агенства", все зависит от вашей фантазии. Но нужно это делать в жёсткой форме, что бы психологически подавить человека и пробить его на эмоции, обычно операторы начинают заикаться или у них становится подавленный голос.

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

14 проверенных сервисов и приложений, чтобы быть здоровым

14 проверенных сервисов и приложений, чтобы быть здоровым Длиннопост

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

Отзывы на врачей

Сайт: prodoctorov.ru


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

Аптечные агрегаторы и аналоги

Сайты: uteka.ru, webapteka.ru


Фармацевтика — это бизнес, в котором действуют те же законы, что и при продаже мороженого или одежды. Скидки, акции, бешеные накрутки из воздуха. Аптечные агрегаторы помогают отслеживать цены в разных городах и регионах и находить выгодные варианты. И если цена спрея от насморка с разницей в 20 рублей вряд ли заставит вас проехать лишнюю станцию на метро, то в ситуации, когда пациент вынужден покупать дорогое лекарство каждый месяц — разница ощутима. И счет в перспективе идет на сотни и тысячи рублей.


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

14 проверенных сервисов и приложений, чтобы быть здоровым Длиннопост

Разгрузка для глаз

Сайт: blimb.su


Мы можем просидеть за компьютером несколько часов подряд и даже не заметить этого. Вот сколько раз вы сегодня отрывали глаза от экрана монитора или смартфона, чтобы отдохнуть? Сервис blimb запускает трехминутные упражнения для тонуса и разгрузки глаз, а после предлагает сделать отдых — но если в этот момент вы не можете порелаксировать, он напомнит через час.

Калькулятор алкоголя и сочетаемость с лекарствами

Сайты: kalkulaator.alkoinfo.ee, zdr.ru


Калькулятор, который помогает отслеживать, сколько вы пьете. «Удручающий» для большинства из нас. Особенно тех, кто до его посещения считал себя вполне социально допустимым членом общества. Сервис эстонский, но встроенный переводчик в браузере поможет вам справиться с незнакомым языком. После того, как добавите все выпитое за вечер, посмотрите на результаты. Калькулятор скажет, превысили ли вы норму единиц алкоголя и сколько калорий получили с напитком. Мотивирует.


А на zdr.ru смотрите, какие лекарства и как ведут себя при сочетании с алкоголем. Ответ на вечный вопрос: а с антибиотиками можно? Нельзя!

Йога и фитнес дома

Сайт: yougifted.ru


Видеотренировки для здоровой спины, занятий растяжкой, йогой, медитациями, гимнастикой для лица и многими другими. Сервис показан тем, кому лень тратить время на дорогу до фитнес-центров. Помогает заниматься и в путешествиях: запускается не только с мониторов, но и со смартфонов. Но на большом UltraWide мониторе LG тренер будет словно с тобой в комнате. Абонемент: первые семь дней — бесплатно, затем 6 000 рублей в год. Все равно дешевле очных занятий.

14 проверенных сервисов и приложений, чтобы быть здоровым Длиннопост

Калорийность блюд

Сайты: calorizator.ru, diets.ru


Калории считают не только для похудения, но и чтобы быть здоровыми. Практически в каждом приложении по подсчету калорий встроена своя база. Но практически каждый пользователь такого приложения все же проверяет калорийность некоторых блюд через поисковики. Чаще всего они попадают на calorizator.ru — на нем большая база популярных продуктов и блюд в готовых вариантах, особенно много фастфуда. Здесь же подробные описания по разным типам диет и возможность подсчитать калорийность блюда, которые приготовили сами. Конкретно эту же опцию можно делать на калькуляторе сайта diets.ru, только немного удобнее.

Психолог онлайн

Сайт: connect-psychologists.com


Есть много сервисов с онлайн-подбором врачей. Но мы еще такой формат на себе не испытали, поэтому советовать не будем. А вот психолог-онлайн уже испробован. Когда у вас стресс, кризис или просто перманентная работа без перерывов и выходных — возможность сидеть на любимом диване с котиком на коленях и общаться со специалистом онлайн — альтернатива очным сеансам. Сервис помогает определиться со специалистом по уровню и цене, а затем передает ваши контакты психологу.

Бонус: несколько приложений про здоровья для смартфона


1. Трекер таблеток Medisafe, чтобы вы не пропускали свои дозы.

Play Market | App Store

2. Welltory измеряет через камеру смартфона уровень стресса. Это не магия, а просто ваш пульс. Рассказывает, в каком вы состоянии.

Play Market | App Store

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

Play Market | App Store

4. Waterbalance напоминает, когда надо пить воду и в каком количестве.

Play Market


Еще немного важной информации. Вы можете выиграть классный монитор LG UltraWide 29WK600-W в рамках месяца медицины на Пикабу. Вот такой:

14 проверенных сервисов и приложений, чтобы быть здоровым Длиннопост

Для этого нужно до 22 ноября написать авторский пост на Пикабу по теме месяца, поставить тег #медицина и метку [моё]. Лучшие посты попадут в голосование, а дальше судьба монитора – в руках пикабушников и пикабушниц. Удачи!


Текст: Анастасия Яблоновская

Показать полностью 3
Отличная работа, все прочитано!