MS, Libreoffice & Google docs

Для начала - обнял, подбросил всех своих уже 56 фолловеров! Впечатлен, растроган, буду дальше для вас стараться)

И как раз один из вас @ashvud,  написал в Вологду-гдугдугду вот о такой проблеме:

Скажу сразу - долго не бился, на тест ушло чуть более минуты, так как пример был очень относительный - решение будет тоже относительное, но, если бы столкнулся сам, то решил бы ИЛИ регуляркой по заглавным, точнее по чередованию строчнаяЗАГЛАВНАЯ (в кодировке это разные символы, вот по ним бы и да) с последующей заменой, или скачиванием базы имен и тупо поиску по строке с заменой при нахождении вот по такой маске:

ИМЯ -> ИМЯ+Разделитель

Но правильное и полностью рабочее решение - в конце рецепта, а пока погнали полуфабрикаты готовить..

Рецепт 2. Побеждаем кривое форматирование, неправильный перенос по строкам.

Открыли табличку и смотрим:

Ага, у нас есть подсказка - если заполнена строка в столбце A - то это начало ФИО в столбце B. Для примера - ячейка A5 - в ней начинается новая часть ПОЛНОЙ строки.

А еще строка А5 намекает на то, что в строке B4 содержится последние символы из "кривого куска".

Что нам это дает? Да собственно решение всей проблемы)

ШАГ А.  ДОБАВЛЯЕМ столбец между существующими A и B, который будем использовать как технический:

В ячейку B2 вбиваем следующее:

=IF(A2<>"";"WUT";"")

Это мы проверяем - если значение в столбце А указано - ставим технический символ, а если нет - не ставим. Тем самым мы обозначаем как и окончание полной ПРАВИЛЬНОЙ строки, так и начало полной правильной строки.

Для простоты, кто не в курсе - наводим на нижний правый угол нужной ячейки (B2 в нашем случае), появляется черный крестик. На него два раза - и автозаполнение пройдет до последней заполненной строки справа или слева. (где последняя - туда и дойдет)

ШАГ Б. Поехали рвать вам пуканы в мой любимый текстовый редактор.Выделяем столбцы B, C, копировать, вставить в текстовый редактор.

Наша задача - удалить лишние переносы, при этом не убить текущие правильные пробелы. ОК ГУГЛ, не страшно)
Найти и заменить:

1. SPACE -> --

2. TAB -> (пусто, просто удалить делаем через найти и заменить)

3. Тыкаем вот сюда, выделяется кусок "пустоты". Выделяем его, копировать-вставить в найти.
ПУСТОТА - > (пусто, просто удалить делаем через найти и заменить)

4. Тыкаем в конец файла, жмем ДВАЖДЫ Enter. Получается вот так:

МАССИВ_ТЕКСТА_СЛИТНО

LUL

Выделяем LUL, жмем вверх, выделяется еще и пустая строка. Отлично, копируем получившееся (вместе с пустой строкой). Очищаем ОБЕ строки Найти / Заменить, вбиваем

WUT -> "

LUL"

5. LUL -> (пусто, просто удалить делаем через найти и заменить)

6. -- -> SPACE

Оп-па менты!

ШАГ В. Чтобы получившееся вернуть в Эксель ПОСТРОЧНО, возвращаемся в файл, и там такие применяем фильтр по строке "НОМЕР", убирая значения BLANKS.

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

Сохраняемся в конце уровня и просим маму принести полотенце, так как битва была жаркой))

Оставшееся слитные строки - правим руками, потому что мы уже автоматизировали более 90% работы менее чем за 2 минуты, а автоматизировать оставшиеся 10% займет уже дольше времени, чем руками. Потому не ленимся, помним - Эксель это чтобы БЫСТРО.
Ну а точнее - это можно поправить в самой обработке, используя шаги 1-6, просто обработки я не вижу, но автоматизировать на 100% заняло бы минуты 2-3.

Ну, собственно, вот как-то так)

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

Для начала - рад видеть, что моих любимых и обожаемых подписчиков стало 21.. Шутки про  "За 2 дня на Пикабу у меня - очко" оставлю себе))) В первую лавочку пишу для вас, ну и отдельная вам благодарочка за то, что вам это интересно.

Так как я такой же логичный, как стол-жираф-48, начну с того, с чего надо было начать изначально - а зачем, в принципе, Эксель то нужен, и что с ним можно делать (кроме Зиночка_Счет_В_Экселе_Сделает). Тут уже предвижу холивар, прям чувствую, потому сразу подкину на вентилятор.
Основные задачи в классическом понимании это НЕ ХРАНЕНИЕ данных, а их обработка. То есть, если рассматривать классическую MVC-модель - это контроллер. Потому что для представления есть бумага и принтер.

То, что мы храним в Экселе данные - ну да, нам так проще. Но по факту любая СУБД Эксель уничтожит по скорости работы непосредственно с данными. Но, чтобы что-то похожее на обработку организовать на уровне работы с СУБД - придется уже городить несколько этажей SQL запросов.

Потому данные выгружают в Эксель, а там уже и "понеслась звезда по кочкам".

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

Отсюда, как мне кажется, возникает одна такая ошибочка - Эксель это не способ сделать "правильно", это способ сделать БЫСТРО и РАБОЧЕ. 90% расчетных файлов в экселе понимает разработчик и Майкл Джексон, ситуация ровно такая же, как и с ремонтом ( "вот пусть тот криворукий исправляет пол, без пола лампочку не повесить").

Далее чуть объяснений и примеров...

В последнее время очень "модно и молодежно" вводить машинный анализ в прогнозирование возможного поведения клиента (про b2c сегмент говорим). То есть, допустим, на основе покупок клиента делать прогноз (не статистику собираем, а Вангу косплеим) совершения им следующей покупки (как даты, так и состава комплексной покупки) и о возможных similar к ней. И уже на основании этого делать какие-либо выводы и офферы разные предлагать (ну там сырный соус к покупке, ну вы поняли да?)) ).

Реализация этого в экселе занимает от часов до дня, реализация этого в CRM занимает от недель до "на третьи сутки после второго пришествия". Причем реализация на уровне CRM нифига не будет работать без прототипа, реализованного или на бумажке (в виде ТЗ) или в виде костылявых обработок в Экселе. Ведь программист, про кодера даже не будем, не обязан и не будет представлять себе вашу бизнес-логику, ему до нее как Ильичу до лампочки. Да и по поводу ТЗ крайне метко выразился мой препод в институте - то, что вы написали в ТЗ не будет работать так, как это вы написали, а будет так, как прочел (понял) разработчик. Потому мы делает что? Правильно - бьемся челом об сруб светлицы что-то невообразимо-кривое в Экселе, но при перемножении лося и порося получаем искомые 63,3%. Что и служит уже как и вашей (дядиной) прибыли, так и методом контроля разработки. То есть вы берете чистые данные, которые хранятся НЕ в Экселе, а в СУБД, и уже их вьювите и контролите.

Ну и, чтобы разбавить это "много-букофф-ниочем" - боевой пример.

Делаем простейшую экспертную систему в Экселе (без регистации и смс).

Вот таблица:

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

Сама формула:

=VLOOKUP(I109/J109*1000;_tech!$F$2:$G$10;2;TRUE)+
IF(K109="";0;IF(K109>43689;15;VLOOKUP(DATEDIF
(K109;"12/8/19";"D");_tech!$J$2:$K$9;2;TRUE)))

А теперь понимаем как это сделать.

Конкретно этот пример - фитнес. Далее мы ищем данные. Конкретно в моем случае искалось так:

- Таблица с клиентами - CRM (оттуда выгрузка в эксель, листы 62_кк)

- СКУД - с сервера СКУД (да, в CRM она интегрирована криво + карты можно и "забыть" внести в саму CRM, потому что некогда админам) (лист "посещения_приведенные", и "пос_")

- Статистика звонков - с телефонии (потому что оттуда она тупо информативнее, и мне не интересно мнение менеджера о клиенте. Если менеджер говорил более минуты - значит клиенту интересно, просто ему предлагали не то, что ему нужно). (лист 4)

- Дополнительные покупки - CRM (лист "Этот")

- Экспертная оценка - эмпирически-добытые факты. (лист _tech)
(часть данных потер, ибо прайваси-все-дела + обработку специально взял аж с августа, с того времени их было еще версий 20)

Теперь включаем чем думать:

Клиент ходит постоянно -> его все устраивает

Клиент купил карту за сумму N -> сумма его устраивает (то есть предложи столько же или меньше, но не больше)

Клиент покупает что-либо еще -> деньги у клиента не кончились

Карта заканчивается в течение квартала -> купит сейчас, если пункты 1-3 соблюдены.

Клиент женщина 30+, на дворе август, клиент не ходит с июня -> предложить карту и детские занятия со скидкой

Клиенту не звонили с сервисными звонками в течение месяца и пункты 1-3 соблюдены -> звонить в ПЕРВУЮ очередь (не задрочен)

Клиент ходил, сейчас не ходит, возраст до 21 -> звонить и предлагать рассрочку, свалил на лето.

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

Все просто, да? А теперь попытайтесь внедрить это в CRM за неделю) А за две? А за месяц? Да фиг выйдет.. В экселе заняло чуть больше 2х часов.

Коэфы задаем отдельно, даже цвета для выделения строк - тоже задаем через "техническую" вкладку.

И да, это тоже можно сделать по другому. А можно и не делать, ведь зачем нужно что-то делать, когда можно просто обсудить все в комментах, неправда ли?))

Привет всем, моему одному подписчику - отдельное трямс)
Подумалось - надо чуть поделиться некоторыми наработками, которые собрались в голове за последние лет эдак 20 работы с различными БД (к коим я с легкостью отношу Эксель). В связи с чем будет ажно целый ряд статеечек на тему, в первую очередь, оптимизации рабочего времени (ну в смысле сделал за 10 минут и дальше листаешь пикабушечку). Поехали с достаточно частой задачи:

РЕЦЕПТ 1:  Преобразовываем ФИО в Ф / И / О, без использования VBA и прочей нехристи.
Для начала чуть теории - большинство, наверняка, сталкивались с такой задачей - есть ФИО в одном столбце, а нужно вытянуть только имя, ну или два столбца ИМЯ и ФАМИЛИЯ. Да или даже поменять местами, задачи разные - суть одна. Теперь давайте разбираться что имеем - имеем строку с N-количеством слов, разделенных одинаковым символом " " (ну или чуть сложнее с массивом, содержащим N-количество элементов, в роли разделителя " "). Тут важно понять сам смысл - все, что имеет закономерность, подлежит автоматизации. В нашем случае закономерность будет вот такая - СЛОВО" "СЛОВО" "СЛОВО, следовательно мы ИЛИ должны "выбрать" нужное нам СЛОВО из всей строки, или чуть схитрить) Но начнем с выбора.

ВАРИАНТ 1: Средствами экселя. Определяем нахождение разделителей по длине строки.
1. Создаем отдельную страницу (на всякий случай)
2. Вставляем данные - Допустим вид будет вот такой
ID | ФИО
1  | Иванов Иван Иванович (b2)

3. Выбираем ПЕРВОЕ СЛОВО, ячейка (c2)
=TRIM(LEFT(B2;FIND(" ";B2;1)))
Что сделали - нашли первое вхождение символа " " в строку, и резанули все, что после него.

4. Выбираем ВТОРОЕ СЛОВО, ячейка (d2)
=TRIM(MID(B2;FIND(" ";B2;FIND(" ";B2;1))+1;FIND(" ";B2;FIND(" ";B2;1)+1)-FIND(" ";B2;FIND(" ";B2;1))))
Что сделали - указали Экселю на "координаты" первого и второго разделителей, скорректировали координаты (порезали длину строки на лишний символ " ").

5. Выбираем ТРЕТЬЕ СЛОВО, ячейка (e2)
=TRIM(MID(B2;FIND(" ";B2;FIND(" ";B2;1)+1)+1;LEN(B2)))
Что сделали - то же самое, что и со ВТОРЫМ СЛОВОМ, только так как нам не нужно резать по второй координате - за нее взяли длину строки.

На выходе получили вот так:

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

СПОСОБ 2: Подмена разделителя
1. Выделяем столбец с ФИО.
2. Открываем текстовый редактор (дада, Блокнот / TextEdit), вставляем туда. ОБЯЗАТЕЛЬНО переведите его в формат Plain Text/ просто текст (формат сохранения .txt) (!)
3. Находим функцию "Найти и заменить".
3.1. В поле найти введите символ " " (то есть просто поиск по одиночному пробелу).
3.2. В поле заменить вставьте символ TAB. Для этого на первой строке нажмите TAB, выделите его, cmd+c,  cmd+v (или control, у кого какая религия)

3.3. Примените, скопируйте, вставьте в Эксель... И собственно так, если не знали - встречайте, TAB - символ переноса на следующую ячейку))

Надеюсь данные способы вам потребуются) И, главное, на забывайте - работает не только на ФИО)
Понравилось? Что-то интересно? Прошу в комменты)  Ну и там лайк/ подписка / кошелек / очки / мотоцикл)

Представления (Custom Views) — это своего рода альтернативный вариант «Группировки» и «Фильтра», при создании которого запоминается положение столбцов и ячеек, т.е. скрыты ли они или видны, какие группы строк-столбцов свёрнуты/развёрнуты и условия фильтрации.

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

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

Допустим, что вы часто используете фильтр мужчины/женщины. Выбираем в фильтре пол «М», затем на вкладе Вид нажимаем Представления «Добавить» и в открывшемся окне вписываем имя «Мужчины», затем проделываем то же самое с фильтром «Женщины».

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

Пример действия Представлений:

Для удобной работы с отчётными таблицами можно сделать Представления с подробной статистикой и по кварталам:

Выделяем столбцы с месяцами C-E, G-I и скрываем их правой кнопкой мыши - Скрыть или нажав Ctrl+0. Затем создаём Представление тем же способом или вписав новое имя в выпадающий список на панели быстрого доступа и нажав Enter.

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

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

Примечание:

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

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

Примеры файлов можно скачать здесь

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

Для начала - добрейшего утречка)
С одной стороны - эксель это инструмент в первую очередь для работы, и "хочу красиво" к нему не очень относиться,  а с другой... а почему бы и нет, черт подери?) Тем более все достаточно просто - нужно только желание сделать красиво и один раз потратить немного времени.
Оптимально для составления справочников/ файла с константами.
В развернутом виде:

Сделать полосу можно и через рамку поля, тут кому как удобнее.

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

Остается только "шлепнуть печать" и написать "Утверждаю"

Все сокращения нативно понятны для сотрудников, но на всякий случай есть шпаргалка:

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

Картинки мои, файл мой, циферки тоже сам рассчитывал)
Если будет интерес - покажу еще парочку применений в справочниках

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

Для построения такой диаграммы нам нужна таблица, где формула =ОКРВВЕРХ.МАТ(МАКС($B$3:$B$7);1000)-B3 будет добавлять в отдельном столбце недостающую сумму от округлённого максимального значения столбца «В» (это необходимо для заполнения диаграммы):

1. Выделяем нашу таблицу, на вкладке «Вставка» выбираем диаграмму «Гистограмма с накоплением»:

2. Настраиваем формат оси, назначив предельное значение 6000

3. Удаляем сетку и легенду:

4. В значке «Фильтры диаграммы» выбираем «Выбрать данные», затем «Изменить», где в открывшемся поле выделяем диапазон «Года»:

5. Кликаем мышкой по центру диаграммы и в открывшемся меню вставляем рисунок:

6. Ставим боковой зазор 0%

7. Выделяем оранжевый фон и заменяем его на белый:

8. Затем выделяем столбцы диаграммы и выбираем «Нет заливки»:

9. Определяем границы диаграммы выбрав белый цвет и ширину 3 пт

10. Меняем название диаграммы нажав знак «равно» и указав ячейку «Доходы»

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

Добрый день!

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

на указанную дату (столбец H) за какой-либо период (например за месяц). В идеале, чтобы можно было учитывать дополнительные условия, например считать количество по каждому отдельному поставщику (столбец I).

Отдельные условия удалось найти в интернете как сделать (столбцы B, C, F, G), но как их совместить - не хватает знаний и понимания, как все это работает.

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

https://docs.google.com/spreadsheets/d/1WX9Jl33RvnBWSF5UNhzB...

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

Например, на дату 01.01.2020 Яблоки, Груши - значит 2 типа. На дату 02.01.2020 Яблоки - так как яблоки уже были и месяц тот же, то значит на дату 02.01.2020 должно также быть 2 типа. На дату 03.01.2020 Яблоки, Бананы, Груши - так как яблоки и груши были, то добавились только бананы, месяц тот же, значит прибавляем их - 3 типа и т.д. (в столбце D написал, как должно получаться, только это должно работать по формуле).

Исходные данные - только столбец Фрукты, Дата и Поставщик. Остальные - мои попытки сделать формулу