Лига помощи Excel

100 постов 911 подписчиков

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

Прошу помощи с внедрением поиска

Сам не программист, пробовал переиначить примеры с макросами из интернетов - не вышло. Гештальт не закрыт(
Наткнулся на лигу, подумал: почему нет?

Дано: книга excel с множеством листов. Пусть будет 50.
Начиная с 4 или 5 листа, в столбце B со строки 2 и ниже вписано 7-значное число (идентификатор). На всех листах числа разные. На одном листе есть список всех этих значений по возрастанию. При реализации поиска планировал его убрать или скрыть, это для себя.

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

858

Ответ на пост «Рабочие полезности Excel с моей работы (Полезность 1)»1

Извините, не мог пройти мимо! Меня прям конкретно задело какими же сложными формулами вы решаете эту задачу.

Раз уж это "Лига помощи Excel", выкладываю своё решение. Оно более динамичное, масштабируемое, с огромным потенциалом доработки. Сделано на PowerQuery + сводные таблицы.

Решение можно легко расширять до безграничности: прикрутить водителей с ФИО, статистику сколько кто проехал в месяц, и т.п.

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

Всем, кто читает этот пост, рекомендую изучать Эксель в такой последовательности: "умные" таблицы, сводные таблицы, условное форматирование, Power Query.

rick1177, если нужна доработка точно как у вас в примере (с полной сеткой дней), я доделаю.

Ссылка на файл.

Ответ на пост «Рабочие полезности Excel с моей работы (Полезность 1)» Microsoft Excel, Полезное, Фишки, Длиннопост, Скриншот, Ответ на пост

График водителя на сводных таблицах и Power Query

1411

Рабочие полезности Excel с моей работы (Полезность 1)1

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

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

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

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

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

Рабочие полезности Excel с моей работы (Полезность 1) Microsoft Excel, Полезное, Фишки, Длиннопост, Скриншот

Пример отображения

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

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

Рабочие полезности Excel с моей работы (Полезность 1) Microsoft Excel, Полезное, Фишки, Длиннопост, Скриншот

Настройки календаря для отображения информации

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

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

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

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

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

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

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

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

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

Рабочие полезности Excel с моей работы (Полезность 1) Microsoft Excel, Полезное, Фишки, Длиннопост, Скриншот

Таблица занесения информации о событиях

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

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 станет массив такого вида:

Рабочие полезности Excel с моей работы (Полезность 1) Microsoft Excel, Полезное, Фишки, Длиннопост, Скриншот

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

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

Рабочие полезности Excel с моей работы (Полезность 1) Microsoft Excel, Полезное, Фишки, Длиннопост, Скриншот

Вид массива для времени окончания

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

Рабочие полезности Excel с моей работы (Полезность 1) Microsoft Excel, Полезное, Фишки, Длиннопост, Скриншот

Результат сложения массивов

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

Рабочие полезности Excel с моей работы (Полезность 1) Microsoft Excel, Полезное, Фишки, Длиннопост, Скриншот

Результат сравнения массива на шаге 5 с 0

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

Рабочие полезности Excel с моей работы (Полезность 1) Microsoft Excel, Полезное, Фишки, Длиннопост, Скриншот

Графическая интерпретация выражения

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

Рабочие полезности Excel с моей работы (Полезность 1) Microsoft Excel, Полезное, Фишки, Длиннопост, Скриншот

Пример массива подходящих дат

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

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

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

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

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

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

Пожалуйста помогите с автозаполнением

Проблема заключается в следующем, есть лист 1 (фото 1) с датой, временем и температурой. И есть лист 2 (фото 2) в котором прописана температура по дате и часам. Подскажите пожалуйста как сделать чтобы в листе 1 автоматом прописывалась температура в зависимости от даты и времени из листа 2?

Пожалуйста помогите с автозаполнением Microsoft Excel, Автозаполнение, Длиннопост, Нужен совет
Пожалуйста помогите с автозаполнением Microsoft Excel, Автозаполнение, Длиннопост, Нужен совет
Показать полностью 2
4

Вернуть первую строку

Excel 2010. Прислали файл xls, закреплено 8 строк и 3 столбца (A-C), местами используется объединение ячеек.

Из 8 закрепленных строк 4 было скрыто. Путем выделения от 5-й (видимой) верх показал строки и проставил ненулевую высоту.

Но 1-я строка не отобразилась. Могу перейти стрелками на её ячейки, она существует, но нумерация строк - со 2-й.

Пытался через макросы задать Hidden = False, RowHeight = 10, визуально ничего не меняется. Ставил курсор в A1, делал Главная-Ячейки-Формат-Высота строки... и Скрыть или отобразить-Отобразить строки - никаких изменений. Удалял строку - вторая строка поднимается на место первой и исчезает.

Как отобразить 1-ю строку?

3

VBA. Сортировка

Всем привет.

По разному работает сортировка в таблице и скриптом. Проблема в знаке ":".
Почему такое?

Прошу прощения за всратые фото, с офисного компа не зайду на пикабу

VBA. Сортировка Vba, Сортировка, Microsoft Excel
VBA. Сортировка Vba, Сортировка, Microsoft Excel
Показать полностью 2
4

Помогите с поиском дубликатов в столбцах

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

Помогите с поиском дубликатов в столбцах Компьютерная помощь, Microsoft Excel, Таблицы Excel

Как бросить курить и не сорваться: инструкция от тех, кто смог

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

Как бросить курить и не сорваться: инструкция от тех, кто смог Курение, Борьба с курением, Зависимость, Telegram (ссылка), ВКонтакте (ссылка), Длиннопост

История 1 — отражение

@ holoroad

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

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

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

История 2 — список

@ maxneb

Беременность жены, рождение ребенка, здоровье, деньги — ничего не было веским поводом бросить окончательно. Постоянно срывался. Помогло составить список, что теряю и что получаю от сигарет, и понимание, что хотя бы одна затяжка — и все насмарку: пару месяцев буду курить. Только список и его осознание. Для каждого он свой. И постоянное обращение к нему. После составления списка курил еще. Но он как заноза висел в голове с вопросом «зачем?»... Так, что-то щелкнуло и сейчас не тянет. Иногда тянет физически, но осознание бесполезности курения сразу глушит позывы. Полгода, полет нормальный...

Бросать на авось — идея, которая подойдет не всем. Нужно понимать, что делать в трудные моменты:

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

  • Замените привычки. Сигарету в руках можно заменить орешками, палочками морковки, жвачкой или даже кубиком льда.

  • Займите время. Вспомните, чем вы любили заниматься: спорт, хобби, прогулки.

  • Планируйте, что делать при тяге. Она длится всего 3–5 минут. Дыхательные практики или звонок другу помогут пережить сильное желание закурить.

  • Откажитесь от «наградных сигарет». Одна затяжка и вы откатитесь назад.

Можно бросить резко, «с понедельника», или постепенно, снижая количество сигарет до нуля. Главное — определиться и не отступать.

История 3 — переключение

@ Spaka

45 лет, стаж 30. Пытался завязать много раз, потом понял, что после каждой попытки бросить, курить начинаешь больше. Как ребенок, которому не дают вкусняшку, а она случайно попала ему в руки. Из чего мозг сделал вывод: не уверен — не бросай. Потом стал замечать, что организм уже стал сам просить перестать курить. По утрам было очень неприятно во рту, удовольствие после сигареты стало короче, а негатив, приходящий следом, ощутимее: неприятные ощущения в горле, боли миндалин, страх схватить онкодиагноз. Хотя врачи говорили, что все ок, в голове-то гоняешь мысли. Я решил попробовать обмануть сам себя. Не делать из процесса отказа какого-то события. Бросить так, как будто это и должно было произойти, но ты не знаешь когда. Про себя помолился, как сумел, и попросил помощи, хитро прищурил глаз и в момент, когда забыл купить про запас (оставалась пара штук в пачке), просто перестал курить. Мне теперь даже странно, как я раньше это делал. Так и живу почти два года. Кстати, раньше в момент завязки курящих ненавидел, дым был очень противен, до тошноты. Теперь все равно. Присоединяйтесь ;)

Есть несколько стратегий отказа от курения:

  • Резкий. Эффективный и решительный подход.

  • Постепенный. Сначала — меньше сигарет, потом — меньше затяжек. И так до нуля.

  • Психологическая замена. Каждая сигарета — это ритуал. Найдите для каждого из них «здоровую замену».

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

Каждый, кто хочет оставить зависимость в прошлом, может обратиться в центры здоровья, которые работают при поддержке нацпроекта «Продолжительная и активная жизнь», и получить необходимую помощь специалистов. Адреса доступны на официальном портале Минздрава России о здоровье: takzdorovo.ru. Также можно позвонить на горячую линию по отказу от зависимостей 8 800 200-0-200.

История 4 — форма

Аноним

Курила электронки 2 года как замену обычным сигаретам. Думала, что это не так дорого, не так вредно да и для девушки вроде более привлекательно: не пахнут волосы и руки. А потом решила привести свое тело в форму. Стала ходить в зал и поняла, что задыхаюсь на первом же упражнении, хотя женщины гораздо старше меня бодрячком. Было очень тяжело слезть. Друзья советовали заменять сигаретами. А потом уехала в отпуск в страну, где нельзя покупать электронки, отвлеклась, и после возвращения уже не тянуло. Даже на тусовках, где все дымят.

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

«Особую тревогу вызывает рост потребления табачных изделий и электронных сигарет. Согласно исследованию, проведенному в нашем Центре, 36,8% курильщиков потребляют одновременно и табак, и электронные сигареты. Среди молодежи в возрасте 25-39 лет этот показатель превышает 45%. Электронные никотиносодержащие и безникотиновые устройства поражают сердце, сосуды, дыхательную систему и ДНК организма не менее пагубно, чем традиционные сигареты, а в ряде случаев способны вызывать острые состояния, включая сосудистые поражения и летальные исходы» – рассказывает руководитель Центра профилактики и контроля потребления табака НМИЦ терапии и профилактической медицины Минздрава России Маринэ Гамбарян.

История 5 — пари

@ kernima

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

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

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

История 6 — связь

Аноним

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

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

Разберитесь, что именно тянет вас к сигарете: скука, стресс, привычка? Когда вы это осознаете, будет легче подобрать альтернативные действия — прогулку, книгу, разговор с близким. Учитесь распознавать моменты, когда особенно хочется закурить, и переключаться на что-то другое. Можно подключиться к программам или группам поддержки — это поможет не сдаваться. И главное: уберите из дома все сигареты.

История 7 — вершина

Аноним

Поднимался с сыном по Пушкинской тропе на гору Железная. Мне лет сорок пять было, идем общаемся, сын бегает туда-сюда. Ну идем короче, а сзади нас догоняет семейная пара, мирно о чем-то щебеча между собой. Догнали и обходят. И так спокойно удаляются… Все бы ничего, но им лет по шестьдесят, если не больше. Я попробовал в их темпе, но задыхаться стал. Короче, поднялся я на гору, спустился, смял пачку и выкинул в мусорную урну. Вот уже 13 лет не курю. Стаж 27 лет.

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


Каждый, кто пытался избавиться от никотиновой зависимости, знает, как это тяжело. Ломка, раздражительность, навязчивые мысли. Даже при переходе на вейпы, иллюзия «меньшего вреда» быстро развеивается: химические коктейли из ароматических смесей также бьют по легким, сосудам и всему организму.

Хорошая новость в том, что вы не одни. В сообществе «Давай бросать» (ВКонтакте и мессенджере Telegram), который работает при поддержке нацпроекта «Продолжительная и активная жизнь» знают, каково бороться с триггерами, искать замену привычке и удерживать себя от срыва. Здесь делятся историями тех, кто смог, поддерживают тех, кто только начал, и помогают разобраться в главном: как пережить отказ без мучений.

Социальная реклама. АНО «Национальные приоритеты», ИНН: 9704007633

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

Вопрос по работе функции Excel

Всем приветики!)
Небольшая вводная: в формуле ЗАМЕНИТЬ первый аргумент (исходный текст) можно указать адресом ячейки с текстом и также можно использовать в качестве аргумента другую функцию.
Так вот вопрос - есть ли такая функция что будет выбираться ячейка только при появлении в ней определенного значения? Причем желательно чтобы можно было в качестве этого определенного значения выбрать часть текста из ячейки (там будет повторяться наименование сигнала с меняющимся индексом). В остальных случаях формула может не срабатывать и выдавать ошибку.
Всем всех благ, всех обнимаю)

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