Никак.

Всем доброй пятницы, дорогие друзья!

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

Я подготовил два бесплатных курса по MS Excel на популярной платформе Stepik. Надо сказать, что это первые два мои курса на этой платформе. Но начало положено!

Базовый курс Excel:

https://stepik.org/course/185640

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

Продвинутый Excel:

https://stepik.org/course/185674

Сводные таблицы, функции анализа данных, ВПР, ГПР, ИНДЕКС, ПОИСКПОЗ и т.д.

Оба курса сопровождаются практическими файлами и небольшими тестами знания материала.

Буду рад Вашему отзыву!

В Excel таблицах, особенно больших, дубликаты данных – довольно частое явление. Иногда они нужны и их можно не трогать, иногда их следует удалить, чтобы они не влияли на статистику, а иногда их просто нужно выделить.

Чтобы быстро найти повторяющиеся значения:

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

2. На вкладке «Главная» выберите «Условное форматирование» → «Правило выделения ячеек» → «Повторяющиеся значения» → «ОК».

3. В разделе «Сортировка и фильтр» выберите «Фильтр» и установите фильтр по цвету выделения.

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

Еще больше интересных и полезных советов вы найдете в моем телеграмм канале : https://t.me/TechLiveHack

Вчера пришёл Генеральный и говорит: "Нужно создать простой и понятный инструмент для составления графика работы водителя на предприятии".

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

Итак, решаем поэтапно задачу.

1. Сначала нужен общий вид этой визуализации.

Был придуман и разработан такой вид:

2. Определяем настройки графика, которые нам нужны для отображения.

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

Из прикольного:

• от изменения месяца меняется количество отображаемых дней;

• от установленного интервала меняется отображение шапки таблицы;

• от изменения времени начала и окончания рабочего дня также изменяется отображение;

• всё оформлено в удобном виде с использованием выпадающих списков;

• "заливка" календаря сформирована условным форматированием;

Т.е. заполняя постоянно таблицу с событиями в любой месяц можно получить отображение.

3. Оформляем таблицу для занесения информации.

У неё очень просто и понятный вид:

Умная таблица, которая легко продляется вниз.

4. "Всхлапываем" обе таблицы.

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

Формулу, конечно, я переработал и получил такой её вид:

=ЕСЛИ(И($J3<>"";K$2<>"");--(ЕСЛИ(ИЛИ($J3="";K$1="");"";СУММПРОИЗВ(($J3=Таблица2[[Дата]:[Дата]])*((--ЛЕВСИМВ(K$1;5)>=Таблица2[[Время окончания]:[Время окончания]])+(--ПРАВСИМВ(K$1;5)<=Таблица2[[Время начала]:[Время начала]])=0)))>0);"")

Надо разобрать формулу, чтобы понять, что сделали ребята и как получили нужный результат. А что за результат? В календаре в задействованную дату и время выставляется 1 и на этой основе выстраивается условное форматирование.

Давайте разбирать поэтапно:

1. --ЛЕВСИМВ(K$1;5) - получает из строки "09:00 - 09:15" начальное_время в формате числовом (0,375) (если не значете, что это за число, то это 1/24/60*(9*60);

2. --ПРАВСИМВ(K$1;5) - - получает из строки "09:00 - 09:15" конечное_время в формате числовом (0,385416667) (если не значете, что это за число, то это 1/24/60*(9*60+15), где 15 - заданный временной интервал для отображения таблицы;

3. Соответственно, конструкция вида --ЛЕВСИМВ(K$1;5)>=Таблица2[[Время окончания]:[Время окончания]] возьмёт массив времени окончания и каждое значение сравнит с временем начала. Результатом такого действа для времени 09: 00 станет массив такого вида:

Что проверяется с помощью данного куска? Есть ли среди времени окончания такое, которое меньше для начала проверяемого интервала. Если нет, то формула выдаёт ЛОЖЬ

4. Аналогичным образом для начального времени операция вида --ПРАВСИМВ(K$1;5)<=Таблица2[[Время начала]:[Время начала]] выдаст результат:

5. Таким образом, операция вида (--ЛЕВСИМВ(K$1;5)>=Таблица2[[Время окончания]:[Время окончания]])+(--ПРАВСИМВ(K$1;5)<=Таблица2[[Время начала]:[Время начала]]) позволит получить массив такого вида:

6. Дальнее сравнение с 0 операции (--ЛЕВСИМВ(K$1;5)>=Таблица2[[Время окончания]:[Время окончания]])+(--ПРАВСИМВ(K$1;5)<=Таблица2[[Время начала]:[Время начала]])=0 приведёт к образованию массива вида:

Графическая интерпретация такова:

7. Поскольку в столбце "↓↓ ДНИ ↓↓" на рисунке "Пример отображения" на самом деле находятся даты, то операция вида ($J3=Таблица2[[Дата]:[Дата]]) выдаст массив подходящих дат для даты 01.09.2023 такого вида:

8. После перемножения результата проверки даты с результатов проверки времени, если получат ИСТИНА, то всё, мы говорим, что это время задействовано.

9. СУММПРОИЗВ необходимо, чтобы проверить везде и по всем сочетаниям событий и проверяемой даты. Сравнение с ">0" просто приведёт к постановке исключительно "1" в ячейку, т.е. не будет учитываться количество раз, если пересечение есть несколько раз.

Решение гениально и просто. Приложение полностью рабочее!

Вот готовый файл!

P.S. Если Вам было полезно, то рассчитываем на благодарность (автор формулы не останется неудел)!

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

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

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

Поделюсь и тут этим способом, вдруг кому пригодится

1. Создаём примечание Shift + F2

2. Наводим курсор на границу примечания и нажимаем ПКМ

3. Переходим в «Формат примечания», переходим во вкладку «Цвета и линии - Цвет - Способы заливки»

4. В окне «Способы заливки» - во вкладку «Рисунок» и там, ткнув на кнопку «Рисунок», выбираем нужную картинку

Довольно часто вижу такую ситуацию, когда пользователи формируют отчетность в 1С, а потом дорабатывают её в Excel. Интересно мнение пользователей. Вы так тоже работаете?

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

Друзья, всем привет.

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

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

Важное примечание!

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

Что же, начнём.

1 - Объединение ячеек.

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

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

Как сделать лучше.

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

Ничего не объединяя, пишем название месяца в первую ячейку (В2, если речь про ЯНВАРЬ), далее выделяем ячейки, в которых январь должен быть виден (В2:F2) - правая кнопка мыши - Формат ячеек (или CTRL + 1 с клавиатуры):

Далее вкладка Выравнивание - по горизонтали - по центру выделения:

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

2 - "Правильная" таблица

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

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

Как сделать лучше.

Всё просто - не делайте трёхмерные таблицы. Плоская таблица - вот то, что нам нужно. То есть выглядеть та же самая таблица должна вот так:

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

3 - "Правильные" даты

Сразу заявлю, что я понимаю боль тех, кто сталкивается с уже волшебно введёнными датами. Но речь про то, как делать это правильно изначально. С датами в Excel вообще всё весело. Я хочу рассказать про самый, с моей точки зрения, чудесный способ ввод дат. Делаем мы таблицу, дошло дело до дат. И мы, как ответственные, трудолюбивые работники выбираем ячейку, и, как мама учила, начинаем вводить "29 марта 2023 г.". Целый день так работали в поте лица, устали. Довольные проделанной работой и гордые за себя. Но если вдруг нас просят отфильтровать данные только по одному конкретному месяцу или посчитать разницу между двумя датами, то мы впадаем в депрессию, потому что "тупой иксель" не фильтрует, не считает:

Ну не умеет он так даты воспринимать. Все мы неидеальны.

Как сделать лучше.

Вводить даты, используя православные разделители, которые Excel понимает. Их три:

1. / - слэш

2. . - точка

3. "-" - дефис/тире/минус (кому как удобнее)

А потом уже через формат ячеек установить то, что тебе нужно.

Про то, как исправлять уже сотворённое, думаю, отдельную статью напишу. Хотя в комментариях к предыдущим статьям разные способы мелькали. Соберу их в одном месте.

4 - Цветовое оформление таблицы.

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

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

Как сделать лучше.

Форматировать данные как таблицу.

Встаём в любую ячейку таблицы, Главная - Стили - Форматировать как таблицу. Выбираете цвет своего настроения (если ничего не хотите, то потом можно поменять или выбрать Создать свой стиль таблицы), проверяем диапазон и галочку "Таблица с заголовками" - Ок. Всего 4 (!!!) нажатия на кнопку мыши и готово. В этих наших интернетах это называется "умная" таблица. Инструмент обсуждаемый. Кто-то их боготворит, кто-то проклинает. Про всё здесь не расскажешь (наверное, отдельную статью писать придётся). Затрону только пару вещей. "Умная" таблица резиновая. То есть все столбцы и строки, которые мы будем добавлять вручную или копированием и вставкой, сразу будут отформатированы как и всё остальное в столбцах (включая формулы и форматы). Выделить столбец можно либо встав в любую ячейку столбца и нажав CTRL + пробел, либо наводим курсор на верхнюю часть ячейки с заголовком, ловим момент, когда он превратится в чёрную (чёрные стрелки важны) стрелку вниз, и нажимаем левую кнопку мыши.

5 - Ручное закрашивание данных

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

Вроде бы, и хорошо. А как теперь посчитать, сколько человеки наши в неделю работали? Встроенные функции по цветам считать не умеют. Можно, конечно, пользовательскую функцию написать на VBA, но...

Как сделать лучше.

Условное форматирование плюс немного магии пользовательских форматов. В ячейки, где человек работал, ставим "1". Выделяем весь диапазон, где хотим что-то закрашивать. Главная - Стили - Условное форматирование - Правила выделения ячеек - Равно. Равно 1, в окошке справа выбираем пользовательский формат. На вкладке "Заливка" выбираем нужные нам цвета. А вот самое интересное происходит на вкладке "Число". Выбираем "(все форматы)", в поле "Тип" прописываем следующий формат ";;;" (без кавычек три точки с запятыми). Если кратко, то каждой точкой с запятой мы задаём формат для положительных, отрицательных, нулевых и текстовых значений. А так как ничего конкретного не прописали, Excel никак данные отображать и не будет. То есть физически в ячейке будет "1", но визуально ячейка будет пустой. Мало того, что мы теперь можем подсчитать количество рабочих дней с помощью СУММ или СЧЁТ, так ещё и при внесении изменений всё у нас будет меняться. Кстати, если делать это с "умной" таблицей, то при добавлении новых сотрудников созданное нами правило будет работать и на них.

Заключение

На этом, пожалуй, первую часть закончу. Как всегда, огромное спасибо всем, кто осилил данный материал, потратив своё драгоценное время и внимание. Надеюсь, было полезно. Делитесь своими соображениями по поводу того, чего делать НЕ НУЖНО, работая с данными в Excel. Совместными усилиями будем искоренять нечестивые данные.