Как сравнить три столбца в excel на совпадения

Как сравнить три столбца в Excel (с примером)

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

Эта конкретная формула сравнивает значения в ячейках B2 , C2 и D2 .

Если все значения равны, формула возвращает Equal.В противном случае возвращается Not Equal .

В следующем примере показано, как использовать эту формулу на практике.

Пример: сравнение трех столбцов в Excel

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

Мы можем ввести следующую формулу в ячейку E2 , чтобы проверить, равны ли все три значения в первой строке:

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

Формула возвращает Not Equal , поскольку не все три имени в первой строке совпадают.

Затем мы можем перетащить эту формулу из ячейки E2 в оставшиеся ячейки в столбце E, чтобы проверить совпадения в каждой строке:

Обратите внимание, что формула возвращает значение « Равно » только для тех строк, в которых значения ячеек равны в каждом из трех столбцов.

Вы также можете применить условное форматирование к строкам, в которых все три значения ячеек равны, выделив диапазон ячеек E2:E11 , затем нажав кнопку « Условное форматирование » на вкладке «Главная», затем щелкнув « Выделить правила ячеек» и нажав «Равно »:

В появившемся новом окне введите « Равно » в поле и выберите цвет заливки, затем нажмите « ОК »:

Строки с совпадающими значениями во всех трех столбцах теперь будут окрашены зеленым цветом:

Дополнительные ресурсы

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

Пример функции ПОИСКПОЗ для поиска совпадения значений в Excel

Функция ПОИСКПОЗ в Excel используется для поиска точного совпадения или ближайшего (меньшего или большего заданному в зависимости от типа сопоставления, указанного в качестве аргумента) значения заданному в массиве или диапазоне ячеек и возвращает номер позиции найденного элемента.

Примеры использования функции ПОИСКПОЗ в Excel

Например, имеем последовательный ряд чисел от 1 до 10, записанных в ячейках B1:B10. Функция =ПОИСКПОЗ(3;B1:B10;0) вернет число 3, поскольку искомое значение находится в ячейке B3, которая является третьей от точки отсчета (ячейки B1).

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

Например, массив <"виноград";"яблоко";"груша";"слива">содержит элементы, которые можно представить как: 1 – «виноград», 2 – «яблоко», 3 – «груша», 4 – «слива», где 1, 2, 3, 4 – ключи, а названия фруктов – значения. Тогда функция =ПОИСКПОЗ(«яблоко»;<"виноград";"яблоко";"груша";"слива">;0) вернет значение 2, являющееся ключом второго элемента. Отсчет выполняется не с 0 (нуля), как это реализовано во многих языках программирования при работе с массивами, а с 1.

Функция ПОИСКПОЗ редко используется самостоятельно. Ее целесообразно применять в связке с другими функциями, например, ИНДЕКС.

Формула для поиска неточного совпадения текста в Excel

Пример 1. Найти позицию первого частичного совпадения строки в диапазоне ячеек, хранящих текстовые значения.

Вид исходной таблицы данных:

Для нахождения позиции текстовой строки в таблице используем следующую формулу:

• D2&»*» – искомое значение, состоящее и фамилии, указанной в ячейке B2, и любого количества других символов (“*”);
• B:B – ссылка на столбец B:B, в котором выполняется поиск;
• 0 – поиск точного совпадения.

Из полученного значения вычитается единица для совпадения результата с id записи в таблице.

Сравнение двух таблиц в Excel на наличие несовпадений значений

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

Вид таблицы данных:

Для сравнения значений, находящихся в столбце B:B со значениями из столбца A:A используем следующую формулу массива (CTRL+SHIFT+ENTER):

Функция ПОИСКПОЗ выполняет поиск логического значения ИСТИНА в массиве логических значений, возвращаемых функцией СОВПАД (сравнивает каждый элемент диапазона A2:A12 со значением, хранящимся в ячейке B2, и возвращает массив результатов сравнения). Если функция ПОИСКПОЗ нашла значение ИСТИНА, будет возвращена позиция его первого вхождения в массив. Функция ЕНД возвратит значение ЛОЖЬ, если она не принимает значение ошибки #Н/Д в качестве аргумента. В этом случае функция ЕСЛИ вернет текстовую строку «есть», иначе – «нет».

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

Как видно, третьи элементы списков не совпадают.

Поиск ближайшего большего знания в диапазоне чисел Excel

Пример 3. Найти ближайшее меньшее числу 22 в диапазоне чисел, хранящихся в столбце таблицы Excel.

Вид исходной таблицы данных:

Для поиска ближайшего большего значения заданному во всем столбце A:A (числовой ряд может пополняться новыми значениями) используем формулу массива (CTRL+SHIFT+ENTER):

Функция ПОИСКПОЗ возвращает позицию элемента в столбце A:A, имеющего максимальное значение среди чисел, которые больше числа, указанного в ячейке B2. Функция ИНДЕКС возвращает значение, хранящееся в найденной ячейке.

Для поиска ближайшего меньшего значения достаточно лишь немного изменить данную формулу и ее следует также ввести как массив (CTRL+SHIFT+ENTER):

Подсветка и сравнение двух и более списков ⁠ ⁠

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

Допустим, что у нас имеется два списка с повторяющимися словами:

Самый быстрый и лёгкий способ найти отличия в двух таблицах – это применить условное форматирование. Итак, выделяем оба диапазона удерживая клавишу «Ctrl» и на вкладке Главная – Условное форматирование – Правила выделения ячеек – Повторяющиеся значения выбираем опцию «Уникальные», в результате Excel подсветит все ячейки, где нет повторов. Выбрав вариант «Повторяющиеся», будут выделены совпадения:

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

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

Чтобы получить отличия отдельным списком я пошагово покажу процесс создания такого списка. Для этого вводим в соседней ячейке D2 формулу =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$9;C2)=0;СТРОКА(C2)) которая будет проверять количество вхождений с помощью функции СЧЁТЕСЛИ и если оно равно 0, то выводить номер строки для текущего элемента функцией СТРОКА.

Для того, чтобы номер ячейки стал абсолютным, т.е. со знаком $, нужно в строке формулы навести курсор на номер и нажать F4.

Дальше в ячейке F2 используем формулу СТРОКА(F1)

Затем в ячейку G2 вводим формулу =НАИМЕНЬШИЙ($D$2:$D$10;F2) которая выведет последовательно номера строк от меньшего к большему:

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

=ИНДЕКС($C$2:$C$10;НАИМЕНЬШИЙ($D$2:$D$10;F2)-1)

которая показывает значение из массива-столбца по порядковому номеру:

Теперь, чтобы избавиться от вспомогательного столбца, вместо диапазона D2:D10 вставим в нашу формулу логическую проверку количества вхождений с помощью функций ЕСЛИ и СЧЁТЕСЛИ, которую мы применили в самом начале:

Вводим формулу =ИНДЕКС($C$2:$C$10;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$9;$C$2:$C$10)=0;СТРОКА($C$2:$C$10));F2)-1) Чтобы формула массива заработала нажимаем сочетания клавиш Ctrl+Shift+Enter и протягиваем формулу вниз. После этого столбец D можно удалить.

Добавим красоты спрятав ошибку #ЧИСЛО!, возникающие в избыточных ячейках. Добавляем к формуле функцию =ЕСЛИОШИБКА, получается:

=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$10;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$9;$C$2:$C$10)=0;СТРОКА($C$2:$C$10));F10)-1);)

Убираем нули в Файл – Параметры – Дополнительно – Показывать нули… и получаем результат

Заменив цифру 0 на 1, мы получим общие значения в списках

Для поиска совпадений в трёх и более списках проделаем следующее.

Сначала озаглавим наши списки, чтобы использовать их в формулах. Для этого выделим оба диапазона вместе с названиями, удерживая клавишу «Ctrl» и на вкладке Формулы – Создать из выделенного в открывшемся окне включим галочку «в строке выше» и жмём ОК:

Excel даст нашим спискам имена, взяв их из первых строк выделенных диапазонов, т.е. Metal1 и Metal2. Проверить именованные диапазоны можно на вкладке Формулы — Диспетчер имён:

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

Нужная нам формула для поиска и вывода общих элементов в этих двух списках будет выглядеть следующим образом:

=ИНДЕКС(metal1;ПОИСКПОЗ(1;СЧЁТЕСЛИ(metal2;metal1)*НЕ(СЧЁТЕСЛИ($H$1:H1;metal1));0))

Плюсом является то, что при увеличении количества списков достаточно будет добавить ещё один именованный диапазон (Metal3) и множитель в нашу формулу-массив проверки совпадений с помощью ещё одной функции СЧЁТЕСЛИ:

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

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

689 постов 15K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

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

Ну круто. Автор молодец, пишет на развлекательной сайте уроки круче, чем на тематических сайтах про них написано)))

А не проще ли было использовать функцию ВПР? Вместо СЧЁТЕСЛИ и прочего.

Лайфхак как-бы подразумевает простоту решения. Написанное — это вынос мозга, а не лайфхак. Вот лайфхак: grep -f a.txt b.txt

Вот сидел , читал и думал, а куда такой лайфхак вообще применить можно. И понял что никуда.

Я бы первым делом таблицу упорядочил и все сделал в один столбец.

Ответ на пост «Быстрая чистка от нагара металлического фильтра кухонной вытяжки»⁠ ⁠

Привет мои маленькие любители клининга!
Прочитал начало это высера, подумал про ответный пост. Так и вышло.
За 9 лет в общепите я перемыл много квадратных метров вытяжек, хуитяжек и прочего профессионального инвентаря.
Большая часть культурного слоя на вытяжке это жир, смывать его жиром это такая себе идея, по сути вы размочили старое масло маслом посвежее и с Фейри смыли, лол, ну серьезно?
Как говорили ораторы в комментариях азелит, шуманит и прочие антижиры это ваши лучшие друзья, пишу пошагово
1. Жирный инвентарь ставим в подходящую по размеру мойку, НЕ МОЧИМ
2. Обильно заливаем антижиром, обильность зависит от загрязнения, стараемся не дышать, если нет запасных лёгких
3. Идём смотреть ютубчик 10-15 минут, жир пусть откисает.
4. Трём в перчатках щеткой, губкой, в зависимости от деликатности материала, вся вкусняха будет слезать легко, продолжаем стараться не дышать
5. Ополаскиваем, хорошо так, несколько раз, готово, вы восхитительны!
Активного вашего участия требуется 1 минута на пунктах 1-2, 3-6 минут на пунктах 4-5.
Аллергикам и астматикам такой способ не подойдёт, если вы сильно ленивая жёпь, а инвентарь мобилен, несите на автомойку, ваш вопрос решат за сотню-две российских рублей, но не мажьте новым маслом старое, вы огорчаете здравый смысл.
З.Ы. Мойку потом тоже надо будет ополоснуть

Ответ на пост «Быстрая чистка от нагара металлического фильтра кухонной вытяжки»⁠ ⁠

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

Наконец один друг пришел в гости и между делом: «Не парься Кабзон. Иди купи гранулы в пятерочке для прочистки труб. Синие такие в маленьком пакете. Махом отмоют». А потом и говорит:

— А знаешь почему у других людей работает, а у тебя не работает?

— Жир другой что ли у них?

— Моё мнение такое: Там нет жира.

— Твоя подруга моет сеточки. Так? А когда она моет, жир реагирует с химией. А вот то чем у тебя там в глубине сеток за годы забилось что угодно только не жир. Это то, что осталось после реакции жира и то что не вступило в реакцию с мылом. Понимаешь? У парня забилось жиром, и он взял фери с маслом и помыл. Работает? Безусловно. Твоя подруга так и делала. И у неё отмывалось. Только за пять лет в сетке скопилось то, что она не вымывала. Там сейчас вперемешку с пылью собралась вся таблица менделеева устойчивая к кислоте. Да и пыль которая спеклась с этими смолами ни фери и не масло не растворят. А гранулы- запросто. Они для этого и созданы.

— Вот те раз, подумал я. У меня подруга химик по образованию. И то не смогла справится. Видимо химики устроены линейно и в голове выстраивают связь жир- кислота.

Я кстати когда ей рассказал версию друга, она подумала и сказала: Логично.

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

И сетка не расплавилась, кстати.

Если найду фото, оформлю отдельным постом.

Быстрая чистка от нагара металлического фильтра кухонной вытяжки⁠ ⁠

Старая губка для мытья посуды (готовая на выброс)

2. Растительное масло (любое, кол-во зависит от степени загрязнения решетки и её площади, у меня решетка одна на всю вытяжку и небольшая, хватает 50-70 мл. масла).

Средство для мытья посуды, которое вы используете.

Перчатки любые (опционально, но я использую).

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

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

И без всяких агрессивных моющих средств, кипячения и прочих ужасов, что я видела в «полезных» советах в интернете. Нет, у нас это быстро и просто!

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

Лайфхаки отправок почтой России (если отправляете часто, или много)⁠ ⁠

Сам я занимаюсь отправками, более 6 лет. Практически ежедневно отправляю посылки, и заказные письма/бандероли.

Не покупать упаковку (пакеты, конверты, коробки на почте). Цена на пакеты даже к примеру, в 10-12 раз выше, ежели их покупать в интернете. Покупаю раз в год на мешке разные пластиковые пакеты. К примеру пакет размером 229х324 на том же сайте от 6.25р на 02.09.2023. Более мелкие еще дешевле. На почте он уже 50р, более мелкие от 30р (114х162). Никакой разницы в них нету, только лишь в разнице надписи по краю — Почта и Почта России. Разницы никакой. Доходят отлично в любую точку России. Коробки же можно, любую обернуть в темную пленку или обмотать непрозрачным скотчем.

Предварительное предоформление посылки или письма онлайн. Очень удобная функция. А главное, меньшая вероятность ошибок при отправлении. Да, зачастую операторы косячат, с ФИО и даже адресами.

Наклейка марок (касается бандеролей и писем). Лучше покупать марки заранее, и наклеивать их заранее самому. Наклейка марок на одно письмо стоит 28р 80 копеек. На каждое (в разных регионах эта сумма может разнится)

Не нужно переплачивать за 1 класс, если это касается бандеролей и писем. Идет по времени практически одинаково. Но разница в цене в 2.5-3 раза.

Маленькие отправления, если отправляете посылкой до 200 грамм — выгоднее отправлять 1 классом.

И самое главное — заранее самому рассчитывать сумму отправку заранее, на сайте! Спасает от лишних расходов.

Также можете поделится, своими лайфахаками. Спасибо за внимание!

Универмаг Printemps Haussmann в Париже⁠ ⁠

После Galeries Lafayette и La Samaritaine стало ясно: видишь в Париже старый торговый центр, смело иди внутрь в поисках сокровищ!

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

Внутри при входе ничего не напоминало о старине, интерьер недавно обновили на полностью современный. Но что же наверху? Поднимаясь на последний этаж, мы делали ставки, будут там купола или нет. И да! Они остались! Причем, сразу два.

Скумбрия атлантическая и тихоокеанская. Различия⁠ ⁠

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

Атлантическая скумбрия имеет разную жирность в зависимости от сезона: выловленная весной содержит около 3% жирности, а осенью – до 30%.

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

А вот тихоокеанская (макрель) имеет мало жира, она сухая и стоит дешевле. На оптовом закупе цена ее сейчас ниже 80 рублей за кг.

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

Так вот я купил тихоокеанскую и очень не доволен, она сухая. Теперь собираю донаты на атлантическую. 😆

Рогоз⁠ ⁠

Рогоз широколистный, или Бочарная трава, или Куга, или Чакан (Tpha latiflia) — многолетнее земноводное травянистое растение; вид рода Рогоз. Растение легко узнаётся по толстым буровато-коричневым плотным, цилиндрическим соцветиям — початкам.

Общий вид цветущего рогоза широколиственного

Вид полиморфный; гибридизирует с рогозом узколистным, с которым нередко произрастает совместно.

Рогоз — вкусен и полезен!

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

Самая часто используемая в кулинарии часть — это корни, которые едят в течение всего года. Именно из них в древние времена и толкли муку. По вкусу они похожи на топинамбур, так как богаты крахмалом.

У рогоза есть и множество других применений. Это отличный индикатор чистоты воды, в древности, поджигая, его использовали как факел. Индейцы плели из него верёвки, корзины, шляпы, пончо, делали матрасы и подушки. А еще из рогоза изготовляли гель-анестик и припарки от укусов насекомых!

Иногда рогоз ошибочно называют камышом, а также путают его с тростником

Камыш, рогоз и тростник проще всего различить по их соцветиям!

Соцветия-початки рогоза возносятся вверх на толстых прочных цветоносах

Камыш имеет невзрачные мелкие красноватые цветки в колосках, которые собраны в зонтиковидные или головчатые соцветия

Тростник является злаком и венчается пышной, густой метёлкой серовато-кофейного цвета

Все о промышленных светильниках. Как вычислить неликвид⁠ ⁠

Примерно год назад встала задача, на первый взгляд наипростейшая, – поменять 120 светильников. Изначально в решение закладывали два условия: 1) нужны были круглые (чтобы не мудрить с монтажом, и просто заменить старые на новые), 2) светодиодные.

Но пообщавшись с одним особо опытным энергетиком, оказалось, что вообще-то светодиодные светильники – это отдельный инженерный мир с кучей подводных камней. При этом, обнаружить их не так просто – они скрыты от человеческого глаза, но напрямую влияют на долговечность. Так, хорошие светильники можно повесить и забыть на 10 лет, а плохие напомнят о себе через год (или после окончания гарантийного срока).

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

МАТЧАСТЬ

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

По нормам СП коэффициент пульсаций колеблется от 5 до 10%, где 10% – для помещений без постоянного нахождения людей. Если процент пульсаций больше, то сотрудники испытывают на себе негативные последствия в виде головных болей, быстрого переутомления и других симптомов.

Отдельно упомяну стробоскопический эффект. Это самый опасный случай мерцания светильников. Рабочему он может стоить руки. На 2 и 3 изображении объект как будто бы неподвижен. Такое случается и на производстве. Из-за того, что частота вращения механизма совпадает с частотой мерцания лампы, работнику кажется, что станок выключен и не представляет опасности.

Коэффициент мощности, «косинус фи» (PF, power factor) – Это достаточно сложный показатель.

Скажем так, он показывает, насколько разумно и аккуратно устройство распоряжается сетевой энергией. Измеряется в процентах или относительных единицах, идеальное значение – 100% или 1. Для сети это самая дружелюбная нагрузка.

Коэффициент мощности ниже 0,8 приводят к эффектам, которые в глобальном масштабе доставляют серьезные проблемы энергетическим компаниям. Также, если в линии много светильников с низким cos φ, сетевой провод перегревается, и в конечном счете может возникнуть пожар.

В общем, чем больше коэффициент мощности, тем лучше.

Обозначение cos φ на светильниках

Цветовая температура (CT, CCT) – этот показатель про то, какого оттенка излучаемый свет. Нижний порог – оранжевый оттенок (теплый), верхний – сине-белый (холодный). Измеряется в «кельвинах, К».

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

Например, 4200K соответствует утреннему солнцу, а 6500K – это световой день, когда солнце в зените.

Световой поток. По простому этот показатель про то, насколько ярко светит светильник (сколько света выдает светильник через конкретную площадь за определенный промежуток времени). Измеряется в люменах, лм.

Освещенность. Если световой поток показывает, сколько света излучает конкретный светильник (этот показатель условно неизменен), то освещенность про то, какое количество света приходится на поверхность.

Освещенность сильно зависит от высоты: чем выше висит светильник, тем большую площадь он освещает, и тем тусклее. Измеряется в люксах люксометром (лк, lux, lx).

По санитарным нормам освещенность для складов – 200 лк, жилой комнаты – 150 лк, офиса – 300 лк.

Сравнение медиум, премиум и эконом сегментов

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

1) СДСВЕТ

Это средний ценовой сегмент, купольный светильник обойдется в 6250 – 9250 руб/шт (зависит от модификаций).

ПЛЮСЫ

Коэффициента мерцания меньше 1%. Согласно нормам СП, коэффициент пульсаций должен быть в пределах 5-10%.

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

Если у светильника коэффициент пульсаций 6%, а по нормам должен быть не более 10, то через пару лет, придется менять светильник.

У СДСВЕТ коэффициент мерцания – 1%, это дает запас времени, прежде чем деградация достигнет 10%.

Небольшая цена. Минимальная стоимость освещения (без монтажа) составит

587 000 руб. При этом на горизонте 30 000 часов по энергоэффективности они обгоняют бюджетные JazzWay. В конце – дам расчеты.

Хороший световой поток. По тех. паспорту он составляет 13 000 лм, поэтому для освещения нашего склада потребуется не 120 светильников, а – 94.

Высокая энергоэффективность. Затраты на обслуживание 94 светильников составят – 132 342,6 руб/год*.

*Считала с учетом стоимости электроэнергии в МО – 5,7 руб за кВт/час.

МИНУСЫ

Срок доставки. СДСВЕТ работают только в Москве. Все отправки за пределы Москвы и МО – через транспортные компании (СДЭК, ПР, Boxberry). Доставка товара обычно занимает несколько дней (зависит от транспортной компании).

Сложность с техобслуживанием регионов. Если вы находитесь в Москве и МО, проблемы – нет: производитель заберет вышедший из строя светильник и предоставит новый (либо заменит детали светильника).

Но, если вы в другом регионе – это усложняет задачу, так как нужно самостоятельно отправить в головной офис (за свой счет), а потом ещё какое-то время ждать новый/отремонтированный светильник.

2) Световые Технологии

Это премиальный сегмент, стоимость подобного светильника – от 25 000 до 44 000 руб/шт (зависит от модификаций).

ПЛЮСЫ

Коэффициент мощности соответствует нормам – 0,96. Он выше, чем у светильников в среднем и эконом сегментах. Согласно постановлению Правительства, для светильников с потреблением более 25 Вт коэффициент мощности должен быть не менее 0,95. Световые Технологии полностью соответствуют этому требованию.

Световой поток – 15 000 лм. Этот показатель выше, чем у производителя из среднего сегмента, поэтому для освещения помещения потребуется 80 светильников вместо 120 и 94.

Высокая энергоэффективность. Затраты на обслуживание 80 светильников составят около 112 632 рублей в год.

Срок службы до 90 000 часов. Светильники Световых Технологий самые долговечные среди сравниваемых в обзоре.

Про срок службы светильников хочу рассказать чуть более подробно.

Светильники обычно выходят из строя намного раньше, чем это заявлено производителем (речь идет про всех производителей). Причина – в уязвимости источника питания светильника. Он очень сильно зависит от температурных условий помещения и критичен к перегреву.

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

Количество часов, указанное в паспорте – рассчитывают теоретически (стресс-тесты). Реально значения измеряются на промежутке – 10 000 часов, а дальше указывают теоретическое значение.

МИНУСЫ

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

587 000 руб (СДСВЕТ), в эконом

643 000 (JazzWay).

3) JazzWay

Это эконом сегмент, стоимость на указанные светильники колеблется от 3500 до 11000 руб/шт.

ПЛЮСЫ

Низкая стоимость. Так, нужную модель можно купить за 3500 руб./шт. Вариантов дешевле я не встречала.

Представлены по всей России. Светильники продаются более чем в 260 городах России через дилеров и маркетплейсы: Я.Маркет, OZON и Wildberries.

МИНУСЫ

Коэффициент пульсаций выше, чем у конкурентов – 5%. Выше я уже отмечала, что по нормам СП, этот показатель должен быть до 5-10%, в нашем случае (производственное помещение), показатель должен быть до 5%. Учитывая, что есть деградация светильников, для нас этот показатель критичен.

Энергоэффективность значительно ниже, чем у конкурентов: на 30% ниже чем у СДСВЕТ, и на 50% – чем у Световых технологий.

Световой поток ниже, чем у конкурентов. Он составляет 10 000 Лм. На освещение нашего склада потребуется 120 светильников.

Срок службы ниже, чем у конкурентов. По паспорту – 30 000 часов.

ВМЕСТО ЗАКЛЮЧЕНИЯ

Я делала таблицу окупаемости (расчеты довольно грубые, но суть отражают). Ниже поделюсь хаками. Ссылка на таблицу окупаемости.

1 — Мощность, кВт; за основу брала светильники с одинаковой мощностью в 100 Вт.

2 — Стоимость светильников, руб.

3 — Стоимость электроэнергии, кВт/час. Коэффициент для Московской области , 5,7 руб.

4 — Количество светильников, шт. Этот показатель рассчитывается исходя из светового потока. Чем он больше, тем меньше требуется светильников.

5 — Количество рабочих часов в год. В этом году: 247 рабочих дней считала по 10-часовому рабочему дню, в итоге получилось – 2470 рабочих часов в год.

6 — Количество электроэнергии, потребляемой в час всеми светильниками, кВт/час.

7- Количество электроэнергии, потребляемой в год, кВт/час.

8 — стоимость потребленной за год электроэнергии, руб.

9 – Значение, по которому делала сравнение. В качестве ориентира был взят срок службы по паспорту у JazzWay (30 000 часов), так как это минимальный срок службы среди трех производителей, и было интересно оценить, как ведут себя данные светильники на этом горизонте.

10 — Расход электроэнергии за 30 000 часов в рублях.

11- Срок службы светильника (часы) по паспорту. Для удобства расчета далее брала максимальный срок в 90 000 часов, и по этому показателю замеряла количество замен эконом и медиум светильников (см. следующий пункт 12).

12 — Количество замен. В этом показателе ориентировалась на срок службы Световых Технологий, так как он по паспорту наибольший. Этот показатель про то, сколько замен светильников в эконом и среднем сегментах будет за время службы дорогих светильников.

13 — Стоимость замены, руб.Здесь считаю в деньгах, во сколько мне обойдутся замены светильников.

ВЫВОДЫ

1) При выборе светильника – стоимость имеет значение, но только вкупе с такими характеристиками, как срок службы, затраты на электроэнергию, световой поток.

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

Их световой поток ниже на 30 и 50% СДСВЕТа и Световых Технологий, поэтому светильников JazzWay потребуется 120 шт. (СДСВЕТ – 94 шт. и Световых Технологий – 80 шт.).

По стоимости JazzWay уже на этом этапе проигрывает СДСВЕТ: 643 000 руб против 587 000 руб.

СРАВНИМ ЕЩЕ

Годовое энергопотребление JazzWay – 29 640 кВт, у СДСВЕТ – 23 218 кВт, у Световых Технологий – 19 760 кВт.

В деньгах экономия составляет 36 605 рублей в год при установке СДСВЕТ вместо JazzWay и 56 316 рублей в год при установке Световых Технологий (см. таблицу выше).

С точки зрения энергопотребления на длинном горизонте (30 000 часов) экономия составляет 444 600 рублей при установке светильников СДСВЕТ и 684 000 рублей при установке Световых Технологий вместо JazzWay.

Еще один сравниваемый показатель – срок службы. Если установить светильники Световых Технологий (с длительным сроком службы в 90 000 часов), то за этот период JazzWay придется заменить три раза, а SDSVET – два.

В деньгах это будет – 1 929 600 руб у JazzWay, 1 175 000 руб у СДСВЕТ. Однако три поколения светильников JazzWay все равно перекрывают стоимость партии светильников Световых Технологий (2 млн рублей за подвесные светильники).

Затраты на электроэнергию и замены светильников на горизонте 10 лет

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

Инструменты OSINT для Onion сайтов⁠ ⁠

Небольшой список полезных инструментов для работы с сайтами в сети Tor.

Поисковые утилиты:

Инструменты для получения onion ссылок:

Tor66 Fresh Onions — http://tor66sewebgixwhcqfnp5inzp5x5uohhdy3kvtnyfxc2e5mxiuh34iid.onion/fresh

Инструменты для сканирования:

Хозяйке на заметку⁠ ⁠

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1⁠ ⁠

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

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

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

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

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

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

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

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

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

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

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

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

2 — «Правильная» таблица

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

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

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

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

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

3 — «Правильные» даты

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

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

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

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

«-» — дефис/тире/минус (кому как удобнее)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции⁠ ⁠

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

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

Что же, давайте приступим.

Есть столбец с текстом:

Нам необходимо в отдельные столбцы разнести код и наименование.

Первое, на что обращаем внимание, есть ли здесь закономерность? С кодом всё понятно. Нужно извлечь текст до первого пробела. Отлично. Находим порядковый номер первого пробела с помощью функции НАЙТИ (FIND):

Далее всё просто. С помощью функции ЛЕВСИМВ (LEFT) извлекаем всё, что до пробела, отнимая от порядкового номера пробела единицу:

Я показал пошаговое решение. Но это можно всё сделать и в одной формуле. Вместо D2 вставляем НАЙТИ (FIND):

Перейдём к более интересному — наименование. И снова начинаем с логики. Нужно извлечь текст между первым и последним пробелом. В Excel нет функции, которая по щучьему велению, по нашему хотению извлекает текст между первым и последним символом (например, пробелами). Придётся хитрить. Сначала пошагово, потом впихнём всё в одну ячейку (и оторвёмся по полной).

Надо подсчитать, а сколько в принципе у нас пробелов в тексте. Сделать это можно вот как:

ДЛСТР (LEN)(А2) (как вам название?) — подсчитывает общее количество символов в ячейке. ДЛСТР(ПОДСТАВИТЬ(A2;» «;»»)) — подсчитывает количество символов без пробелов. От них мы избавились благодаря функции ПОДСТАВИТЬ (SUBSTITUTE), которая заменила пробелы на пусто.

Теперь очень мудрёный шаг. Нам надо заменить последний пробел на какой-нибудь другой произвольный символ:

ПОДСТАВИТЬ (SUBSTITUTE)(A2;» «;»*»;E2) как раз это и делает. Даём ей исходный текст, что нужно найти, на что заменить, и какой символ по порядку (именно для этого нам нужно было узнать, сколько всего пробелов).

Зачем нам это? Немного терпения, друзья, сейчас всё будет.

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

ПСТР (MID) — как много в этом слове. А функция, вообще-то, полезная. Указываем ей ячейку, порядковый номер, с которого хотим начать извлекать символы, и само количество символов.

А2 — понятно, ячейка с текстом.

D2+1 — начиная с какого символа будем извлекать (первый пробел + 1)

G2-D2-1 — количество извлекаемых символов. Порядковый номер звёздочки — порядковый номер первого пробела — 1 (чтобы исключить лишний символ в конце).

А теперь комбо! Всё в одной ячейке:

Говорил же, что будет страшно, а вы не верили.

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

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

Здесь уже расписывать не буду, а то ещё одна «простыня» получится. Кому интересно, посмотрите. Там, правда, уже не так всё подробно по шагам расписано. Но я в вас верю!

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

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

П.С. недавно проводил мастер-класс по разным штукам в Excel, про которые здесь почти не писал. Например, рассказал про то, что такое расширенный фильтр.

Мгновенное заполнение в Excel — магия в чистом виде⁠ ⁠

Друзья, всем привет. Сегодня хочу рассказать вам про мгновенное заполнение в Excel.

Ссылка на файл, чтобы можно было потренироваться — https://disk.yandex.ru/i/HyW0N215F6CuUg

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

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

Извлечение данных

Предположим, у нас есть вот такой столбец с текстом:

Нам нужно извлечь отдельно номер договора и дату. Это можно сделать с помощью инструмента «Текст по столбцам». Правда, потом придётся от символа «№» ещё избавляться. А вот мгновенное заполнение справится с этим намного быстрее. Просто вводим справа от текста в первую ячейку номер договора (1), нажимаем Enter. Далее возможны два варианта.

Вариант 1. Вручную вводим в ячейку первую цифру второго договора (2). Excel предлагает свои варианты, жмём Enter — PROFIT!

Вариант 2. После того, как перешли ко второй ячейке, сразу нажимаем сочетание Ctrl + E (Е английская, конечно). Именно это сочетание отвечает за запуск мгновенного заполнения. Аналогично с датами. Вводим в ячейку С2 дату первого договора — Enter — Ctrl + E — наслаждаемся результатом.

ОЧЕНЬ ВАЖНАЯ ЧАСТЬ СТАТЬИ.

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

Ух ты! И так будет работать всегда?! Строго говоря — нет. Иногда, Excel не может с одной ячейки распознать логику. В этом случае нужно вручную заполнить не одну, а две, три, четыре (если случай совсем запущенный) ячейки. И только после этого нажимать Ctrl + E. Чем больше ячеек заполняешь, тем выше вероятность того, что твоя логика будет верно распознана могучим интеллектом Excel. Порой мгновенное заполнение не справляется с поставленной задачей:

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

Образцы вводите в соседнем столбце от источника (можно справа или слева). Не «убегайте» далеко от данных, результат может быть непредсказуемым или вообще ничего не будет.

Ещё одно важное дополнение: мгновенное заполнение работает в версиях Excel 2013 и выше.

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

Извлечение только чисел из столбца

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

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

Работа с текстом

В столбце указаны Имя и Фамилия. Нам нужно получить результат в виде «Имя Ф.» В первой ячейке вводим образец — Enter — Ctrl + E:

Кстати, если попробовать получить Фамилия И., то будьте внимательны. Если прописать два примера, потом начать вводить третий, то появляется довольно забавный список:

Но если не начинать вводить в третью ячейку текст, а сразу нажать на Ctrl + E, то всё будет нормально. Раз на раз не приходится. Временами мгновенное заполнение ведёт себя очень странно.

Извлечение части сплошного текста

Необходимо разбить слипшийся текст на части. Вводим в первых двух ячейках образец — Ctrl + E:

С номером поступаем аналогично.

Сбор текста

В отдельных столбцах есть различная информация, которую необходимо собрать в одно предложение. Обратите внимание, что порядок столбцов для мгновенного заполнения роли не играет. Прописываем предложение в первой ячейке — Enter — Ctrl + E:

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

В качестве небольшой рекламы позвольте оставить здесь ссылку на мастер-класс, который я буду проводить 9 марта. Кто хочет узнать ещё несколько полезных приёмов при работе в Excel (там почти не будет того, о чём я писал здесь), а ещё хочет услышать чуть больше про то, где я работаю, записывайтесь — Полезные приемы при работе в Excel. Часть 2 (specialist.ru)

На этом всё. Как обычно, спасибо огромное всем, кто потратил своё драгоценное время и осилил данное полотно. Надеюсь, было полезно. Видео по данной статье обязательно появится на моём канале — (36) Андрей Митрохин — YouTube

Таблица подсчета розеток/выключателей/рамок. ⁠ ⁠

Когда я работал в магазине электротоваров регулярно приходилось считать ЭУИ и рамки к ним по зарисовкам заказчиков или их работников, тогда я это делал на бумаге и неплохо набил на этом руку. Но у некоторых продавцов консультантов это выходит не слишком быстро и качественно. Для автоматизации процесса я решил создать таблицу в google, а затем перенес ее в Excel (последний мне нравится больше). Таблицей я намерен поделиться ссылки будут ниже, а пока краткое описание:

Это страница «Сводка» первоначально ее надо заполнить под себя и сохранить как шаблон:
— Наименования всех типов ЭУИ какие у вас могут быть (если не достаточно того, что ввел я)
— Цвета механизмов (или например серия + цвет, как удобнее будет)
— Цвета рамок (аналогично механизмам)

Потом переходим на страницу «Ввод данных»
При добавлении новой строки указываете комнату
цвет механизма, цвет рамки и наполнение постов выбираете из выпадающего списка (подтянутся варианты со страницы «Сводка»), когда вы выбираете механизм для поста — ячейка окрашивается, считая количество постов в рамке

После заполнения страницы «Ввод данных», возвращаемся на «Сводка»
При выборе нужного цвета в крайней правой таблице («Текущий цвет») в списке ЭУИ и рамок останется только то количество, которое соответствует выбранным цветам.
Кроме того, общее количество механизмов и постов в рамках и количество установочных коробок

ЗЫ Отдельное спасибо @XaXa3Pa3a

Полезные трюки при работе в Excel⁠ ⁠

Всем привет. Это моя первая статья на Пикабу, поэтому позвольте сначала представиться. Я являюсь преподавателем Microsoft Excel. Теперь, когда с формальностями покончено, можно перейти к основному.

Сомнения перед написанием

Я довольно часто читаю разный тематический материал на Пикабу, и меня восхищают большинство авторов и статей. Статьи восхищают, в первую очередь, своей интересностью (есть такое слово вообще?) и полезностью. Именно поэтому у меня были большие сомнения, а стоит ли вообще лезть со своими очередными «простыми, но полезными штуками при работе в Excel». Да и кому вообще ты со своим Excel нужен?! Тем более, что беглый поиск по сайту не выдал ни одной подобной статьи. И та часть меня, которая отвечает за неуверенность, сразу подметила, что раз нет, значит, оно никому не нужно. А может, просто плохо искал. И да, я отдаю себе отчёт в том, что подобного материала довольно много на просторах интернета. И всё-таки, принцип «лучше сделать и жалеть, чем не сделать вовсе» возобладал.

Почему я посчитал, что это будет полезно

Занимаясь преподаванием этой замечательной программы (а я и правда считаю её чудесной и, можно сказать, влюблён в неё), я довольно часто подмечал, что именно мелочи оказывают самое большое впечатление на слушателей. Рассказываешь про сочетание функций ИНДЕКС(ПОИСКПОЗ), какое оно крутое, позволяет двумерный поиск по таблице осуществлять и много чего ещё делать, все сидят, понимающе кивают. Потом в процессе показываешь какую-нибудь мелочь, вроде той, что листы можно копировать, зажав Ctrl и мышкой перетащив лист чуть правее/левее, аудитория сразу оживает: «Ну всё, не зря время потратили». Именно про такие вот простые приёмы я и хотел бы вам рассказать (про первый так уже рассказал).

Небольшое пояснение

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

Если у вас ноутбук, то функциональные клавиши могут работать только при одновременном нажатии на кнопку Fn+F1-12 (есть такие ноутбуки, в которых и этот способ не работает, тут надо уже по модели ноута смотреть).

Вообще, почти каждая функциональная клавиша отвечает за какое-то действие. Но я остановлюсь на одной, а именно — F4. И нет, речь пойдёт не про то, что этой кнопкой в Excel мы можем менять тип ссылки для ячейки.

F4 — повтор последнего выполненного пользователем действия (если нажимать её не тогда, когда курсор находится в строке формул)

Например, вам нужно для нескольких несмежных столбцов установить определённую ширину. Вместо того, чтобы каждый раз выбирать столбец, потом переходить на вкладку Главная — Ячейки — Формат — Ширина столбца. Можно один раз проделать эту операцию, потом просто выделить следующий столбец и нажать F4. И такой фокус можно проделывать со многими операциями, будь то закраска ячеек, строк, столбцов, части графика на диаграмме или банальная вставка столбцов (да, столбец можно вставлять сочетанием Ctrl + «+», но ведь это две кнопки, а F4 — одна).

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

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

И вот вы каждый раз раскрываете фильтр, устанавливаете нужные критерии, просматриваете данные, потом раскрываете фильтр, следующий критерий, потом фильтр. Думаю, суть вы уловили. «Но всё меняется, когда приходят они — представления!» © Установив нужные критерии, переходим на вкладку Вид — Режимы просмотра книги — нажимаем Представления:

Далее всё интуитивно (куда же без интуиции в этой прекрасной программе) понятно. Жмёшь «Добавить», обзываешь представление так, как тебе угодно — Ок. Здесь же, в окне добавления представления, мы можем узнать, а что, собственно, Excel сохраняет. А сохраняет он параметры печати, результаты фильтрации, скрытые строки и столбцы. Создав под каждый набор фильтров, строк и столбцов представление, потом лёгким и непринуждённым нажатием на эту команду ты будешь менять свою таблицу в мгновение ока. Это не совсем удобно? Что же, согласен. Давайте сделаем ещё удобнее и добавим представления на панель быстрого доступа. Для этого раскроем настройку панели быстрого доступа — Другие команды:

В открывшемся окне в поле «Выбрать команды из:» выбираем «Все команды». Потом находим «Представления» — Добавить:

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

Теперь у нас появился выпадающий список со всеми нашими сохранёнными представлениями. Через это же окно можно и новые представления создавать. Просто пишешь в нём название, нажимаешь Enter — готово.

ПРЕДУПРЕЖДЕНИЕ!

Представления не работают в книгах, в которых есть «умные» таблицы (таблицы, которые мы создаём через вкладку Главная — Стили — Форматировать как таблицу).

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

Два окна одной книги.

Прежде, чем кидать в меня различные предметы с криками «мало того, что про какой-то Excel пишет, так сейчас ещё будет рассказывать, как в двух окнах работать, смерд?!» позвольте пояснить. Речь пойдёт о том, как работать в двух окнах с ОДНОЙ книгой. Давайте смоделируем ситуацию. Есть у тебя два монитора (если ещё нет, обязательно заводи второй, пускай небольшой, но чтобы был), один файл Excel с несколькими листами внутри. Тебе нужно из одной таблицы перенести данные в другую (сравнить их, связать формулами и так далее). Что ты делаешь? Правильно, бесконечно долго и уныло переключаешься между листами. Второй монитор тем временем грустно за этим наблюдает. Но можно сделать этот процесс более удобным и быстрым. Прошу любить и жаловать, вкладка Вид — Окно — Новое окно:

Нажав на эту команду, мы получим ту же самую книгу, но в другом рабочем окне. Название файла будет немного изменено на «Мой файл:1» и «Мой файл:2». А дальше уже дело за тобой. Располагай окна так, как тебе удобно (на одном мониторе, на разных), копируй данные, создавай связи, формулы — в общем, работай. Но делать ты это уже будешь быстрее и удобнее. Все изменения, которые мы вносим в любое из клонированных окон, появляются сразу во всех связанных окнах. Главное, не забыть нажать «Сохранить» хоть в каком-нибудь окне.

Специальная вставка (пропускать пустые ячейки)

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

Нужно перенести данные из крайнего правого столбца второй таблицы (столбец Р) в крайний столбец первой таблицы (столбец F) таким образом, чтобы существующие номера остались. Обычным копированием-вставкой сделать это не получится, так как в столбце Р есть пустые ячейки, которые заменят собой существующие номера в столбце F. И тут на сцену выходит специальная вставка. Выделяем диапазон из столбца Р, копируем. Далее выбираем ячейку, начиная с которой нужно вставить данные (в нашем случае это F2), и либо щёлкаем правую кнопку мыши — в контекстном меню ищем «Специальная вставка», либо нажимаем сочетание клавиш Ctrl+Alt+V. Попадаем в такое окно:

Ставим галочку рядом с «пропускать пустые ячейки» — Ок. Профит!

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

P.S. Если статья покажется интересной и полезной, то на примете есть ещё несколько приёмов, про которые могу рассказать.

Друзья, создал на Ютубе свой канал. Пока только видео с первой статьёй. В ближайшие дни опубликую вторую часть. Полезные трюки и приёмы при работе в Microsoft Excel — YouTube

Если б мишки были пчёлами… условия в Excel⁠ ⁠

Решил попробовать формат коротких постов по Excel

Дети, сегодня я расскажу вам о такой удобной функции в Excel как ЕСЛИ.

В общем виде выглядит так:

ЕСЛИ (условие; результат если условие верное; результат если условие не верное)

Я буду писать формулу, а на картинке будет результат этой формулы в 5 столбце

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

=ЕСЛИ(И(A2=»овощи»;D2>50);»дорогой овощ»;»либо не дорогой, либо не овощ»)

Не забываем, что можно комбинировать несколько ЕСЛИ Главное не запутаться в скобках и точках с запятыми.

=ЕСЛИ(A2=»фрукты»;ЕСЛИ(D2>100;»дорогой фрукт»;»не дорогой»);»не фрукт»)

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

А на этом сегодня все, пойду дальше писать про макросы…

Ответ на пост «Помощь в формировании Таблицы на основе 2 таблиц»⁠ ⁠

Если по-простому, формулами, то так:

Таблицу 3 переделываем в другой вид:

Это будет состав детали, даты будем добавлять вправо.

(Таблица соответствия количества материала количеству детали)

Таблица 2 тогда будет выглядеть так:

(тупо суммируем кол-во материалов на дату)

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

2. Важно следить чтобы в составе (таблица 3) не было дубликатов пар — [деталь — материал] (желтые столбцы), иначе объем задваиваться будет.

Сумма итогов, чтобы её не переписывать каждый раз (в ячейке H2) — может выглядеть так:

Ответ на пост «Как сохранить список запрещенных в России в файл?»⁠ ⁠

Бро, на данном сайте есть таблица.
вот тебе лайфхак

@1g0rbm ты круто всё описал и, уверен, твой способ рабочий,
но зачем усложнять когда, можно не усложнять и пойти попить кофеек?)

1 минута.
и пример из множества страниц в ответ на некоторые комментарии
ссылка на пост

Ответ на пост «Excel, делим покупки на несколько человек»⁠ ⁠

И так господа, перед Вами методичка по распиливанию затрат после попоек.

Разработана она мною, но на копирайт не претендую.

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

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

Помните, между друзьями не должно быть обид или споров по денежным вопросам. Если у вас в компании есть друг, не желающий сопровождать общий счёт, объясните ему, что он сам платит за себя и главное платит СРАЗУ! Принесли счёт в ресторане, он сам запоминает и считает сумму напитого и наеденного и СРАЗУ кидает на стол деньги, а ещё фиксит это, например в общем чате. Кстати, общий чат — это тема. История с карточками — это хорошо, но налом платить в некоторых местах безопасней. Кидайте сразу в общий чат, кто, сколько и главное, за что заплатил. Поверьте, с утра вам будет не до воспоминаний, да и не всё вспомнить. Не забывайте о конспирации! Нельзя писать «3000 Диме на приват». Продумайте заранее все фразы и договоритесь об обозначениях.

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

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

1. Кидаем в чат позывной. Кто, за что, вчера платил.

2. Открываем Excell и создаём следующую таблицу:

И так: Перед вами таблица со следующими параметрами:

Столбец «Событие» — кратко фиксим события оплаты;

Столбцы «Участники» — все, кто присутствовал на пьянке;

Столбец «Сумма» — Общая сумма к разделу;

Столбец «N» — кол-во человек участвующих в разделе;

Столбец «Проверка» — собственно сумма всех сумм в столбцах B-E = 0.

Если пьянка длилась несколько дней (например Нашествие), то слева ещё вставляется таблица «Дата».

Допустим начали сначала Вася, Петя, Игорь. Зашли в Кафе, пожрали, да выпили на 9500 рубликов. Платил Петя.

Тогда для Пети пишется формула:

Для всех, кто учувствовал пишется формула:

В результате получаем:

Все кто с минусом — те в долгах;

Кто с плюсом -тот кредитор.

Формулы с $ помогают просто копипастить ячейки, и заполняя только столбцы Сумма и N, долбить долг дальше по событиям:

Как мы видим, в пабе к нам пришёл ещё Ваня.

Дальше из караоке идём в клуб. Ваня цепляет тёлку, Вася уезжает домой.

Для этого вводим ещё один столбец а с Васей прощаемся. В клубе мы оставляем 15 к.

Ваня с «Ваней — 2» уезжают, а Петя и Игорь идут:

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

Несколько способов, как сравнить два столбца в Excel

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

В этой статье мы рассмотрим различные методы и функции, которые позволят вам сравнить два столбца в Excel. Мы покажем, как использовать встроенные функции Excel, такие как ЕСЛИ, ВПР, ИНДЕКС, а также рассмотрим использование условного форматирования. Вы узнаете, как быстро сравнивать значения в двух столбцах и находить идентичные и недостающие значения.

1. Сравнение двух столбцов на совпадения строк

Это самый простой и быстрый способ, в случае если вам необходимо сравнить между столбцами каждую строку.

Пример: сравнение ячеек в одной строке

Ниже приведено два столбца, которые имеют 9 строк.

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

Эта формула сравнивает два столбца А2 и В2 между собой и выдаёт значение: ИСТИНА (совпадает), либо ЛОЖЬ (не совпадает).

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

Пример: сравнение ячеек в одной строке с помощью формулы ЕСЛИ

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

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

Если вам необходимо сравнить два столбца с учётом регистра (например, чтобы учитывалось заглавное или строчное написание “IBM” и “ibm”), то можно воспользоваться следующей формулой:

Приведённая формула покажет, в каких строках установлен одинаковый регистр, а в каких нет.

Пример: выделение строк с условным форматированием

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

1. Выделите все необходимые данные для проверки.
2. Перейдите на вкладку “Главная”.

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

2. Сравнение двух столбцов с выделением совпадений

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