Перейти к содержимому

Как сравнить списки в excel

  • автор:

Методы сравнения таблиц в Microsoft Excel

Сравнение в Microsoft Excel

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

Способы сравнения

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

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

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

Способ 1: простая формула

Самый простой способ сравнения данных в двух таблицах – это использование простой формулы равенства. Если данные совпадают, то она выдает показатель ИСТИНА, а если нет, то – ЛОЖЬ. Сравнивать можно, как числовые данные, так и текстовые. Недостаток данного способа состоит в том, что ним можно пользоваться только в том случае, если данные в таблице упорядочены или отсортированы одинаково, синхронизированы и имеют равное количество строчек. Давайте посмотрим, как использовать данный способ на практике на примере двух таблиц, размещенных на одном листе.

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

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

Формула сравнения ячеек в Microsoft Excel

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

Ставим курсор в поле «Массив1» и выделяем на листе сравниваемый диапазон данных в первой области. После этого в поле ставим знак «не равно» (<>) и выделяем сравниваемый диапазон второй области. Далее обворачиваем полученное выражение скобками, перед которыми ставим два знака «-». В нашем случае получилось такое выражение:

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

Сравнение таблиц на разных листах в Microsoft Excel

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

Способ 2: выделение групп ячеек

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

Переход в окно выделения группы ячеек в Microsoft Excel

    Выделяем сравниваемые массивы. Переходим во вкладку «Главная». Далее щелкаем по значку «Найти и выделить», который располагается на ленте в блоке инструментов «Редактирование». Открывается список, в котором следует выбрать позицию «Выделение группы ячеек…».

Способ 3: условное форматирование

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

  1. Прежде всего, выбираем, какую табличную область будем считать основной, а в какой искать отличия. Последнее давайте будем делать во второй таблице. Поэтому выделяем список работников, находящийся в ней. Переместившись на вкладку «Главная», щелкаем по кнопке «Условное форматирование», которая имеет месторасположение на ленте в блоке «Стили». Из выпадающего списка переходим по пункту «Управление правилами». Переход в окно управления правилами условного форматирования в Microsoft Excel
  2. Активируется окошко диспетчера правил. Жмем в нем на кнопку «Создать правило». Диспетчер правил условного форматирования в Microsoft Excel
  3. В запустившемся окне производим выбор позиции «Использовать формулу». В поле «Форматировать ячейки» записываем формулу, содержащую адреса первых ячеек диапазонов сравниваемых столбцов, разделенные знаком «не равно» (<>). Только перед данным выражением на этот раз будет стоять знак «=». Кроме того, ко всем к координатам столбцов в данной формуле нужно применить абсолютную адресацию. Для этого выделяем формулу курсором и трижды жмем на клавишу F4. Как видим, около всех адресов столбцов появился знак доллара, что и означает превращение ссылок в абсолютные. Для нашего конкретного случая формула примет следующий вид:

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

  1. Производим выделение областей, которые нужно сравнить. Выделение сравниваемых таблиц в Microsoft Excel
  2. Выполняем переход во вкладку под названием «Главная». Делаем щелчок по кнопке «Условное форматирование». В активировавшемся списке выбираем позицию «Правила выделения ячеек». В следующем меню делаем выбор позиции «Повторяющиеся значения». Переход к условному форматированию в Microsoft Excel
  3. Запускается окно настройки выделения повторяющихся значений. Если вы все сделали правильно, то в данном окне остается только нажать на кнопку «OK». Хотя при желании в соответствующем поле данного окошка можно выбрать другой цвет выделения. Окно настройки выделения повторяющихся значений в Microsoft Excel
  4. После того, как мы произведем указанное действие, все повторяющиеся элементы будут выделены выбранным цветом. Те элементы, которые не совпадают, останутся окрашенными в свой изначальный цвет (по умолчанию белый). Таким образом, можно сразу визуально увидеть, в чем отличие между массивами.

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

Настройка выделения уникальных значений в Microsoft Excel

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

Уникальные значения выделены в Microsoft Excel

Способ 4: комплексная формула

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

Оператор СЧЁТЕСЛИ относится к статистической группе функций. Его задачей является подсчет количества ячеек, значения в которых удовлетворяют заданному условию. Синтаксис данного оператора имеет такой вид:

Аргумент «Диапазон» представляет собой адрес массива, в котором производится подсчет совпадающих значений.

Аргумент «Критерий» задает условие совпадения. В нашем случае он будет представлять собой координаты конкретных ячеек первой табличной области.

  1. Выделяем первый элемент дополнительного столбца, в котором будет производиться подсчет количества совпадений. Далее щелкаем по пиктограмме «Вставить функцию». Переход в Мастер функций в программе Microsoft Excel
  2. Происходит запуск Мастера функций. Переходим в категорию «Статистические». Находим в перечне наименование «СЧЁТЕСЛИ». После его выделения щелкаем по кнопке «OK». Переход в окно аргументов функции СЧЁТЕСЛИ в Microsoft Excel
  3. Происходит запуск окна аргументов оператора СЧЁТЕСЛИ. Как видим, наименования полей в этом окне соответствуют названиям аргументов.

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

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

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

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

Переход в окно аргументов функции ЕСЛИ в Microsoft Excel

  1. Прежде всего, немного переработаем нашу формулу СЧЁТЕСЛИ, а именно сделаем её одним из аргументов оператора ЕСЛИ. Для этого выделяем первую ячейку, в которой расположен оператор СЧЁТЕСЛИ. В строке формул перед ней дописываем выражение «ЕСЛИ» без кавычек и открываем скобку. Далее, чтобы нам легче было работать, выделяем в строке формул значение «ЕСЛИ» и жмем по иконке «Вставить функцию».
  2. Открывается окно аргументов функции ЕСЛИ. Как видим, первое поле окна уже заполнено значением оператора СЧЁТЕСЛИ. Но нам нужно дописать кое-что ещё в это поле. Устанавливаем туда курсор и к уже существующему выражению дописываем «=0» без кавычек.

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

В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений», который мы ранее преобразовали с помощью функции ЕСЛИ. Делаем все ссылки абсолютными.

Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ. От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек.

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

Способ 5: сравнение массивов в разных книгах

При сравнении диапазонов в разных книгах можно использовать перечисленные выше способы, исключая те варианты, где требуется размещение обоих табличных областей на одном листе. Главное условие для проведения процедуры сравнения в этом случае – это открытие окон обоих файлов одновременно. Для версий Excel 2013 и позже, а также для версий до Excel 2007 с выполнением этого условия нет никаких проблем. Но в Excel 2007 и Excel 2010 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке.

Сравнение таблиц в двух книгах в Microsoft Excel

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

Как в excel сравнить два списка

Сравнение в Microsoft Excel

​Смотрите также​: . ​Zeek7​ содержанием (по строкам).​ наименования счетов из​Пусть на листах Январь​ списке, в другом​ перед ними знак​ нумерацией, который мы​«Значение если истина»​СЧЁТЕСЛИ​ значений.​ данный вариант от​ блоке​ групп ячеек. С​«Математические»​ которых размещены фамилии.​Довольно часто перед пользователями​oks26​: отлично подошло мне,​ Затем их можно​ обоих таблиц (без​ и Февраль имеется​ отсутствуют).​ доллара уже ранее​ недавно добавили. Адрес​получилось следующее выражение:​

​, и после преобразования​Аргумент​

​ ранее описанных.​«Стили»​ его помощью также​

Способы сравнения

​выделяем наименование​Для этого нам понадобится​ Excel стоит задача​: спасибо!​ по подобному вопросу,​ обработать, например:​

  • ​ повторов). Затем вывести​ две таблицы с​
  • ​Создадим для удобства 2​ описанным нами способом.​
  • ​ оставляем относительным. Щелкаем​СТРОКА(D2)​

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

​ разницу по столбцам.​ оборотами за период​ Динамических диапазона Список1​Жмем на кнопку​ по кнопке​Теперь оператор​

Способ 1: простая формула

​ заполнения зажимаем левую​задает условие совпадения.​ нужно сравнить.​ переходим по пункту​ синхронизированные и упорядоченные​. Щелкаем по кнопке​ листе. Вписываем туда​ или списков для​: огромное спасибо. ​ примере появилась ошибка​ еще визуально отформатировать​Для этого необходимо:​ по соответствующим счетам.​ и Список2, которые​«OK»​«OK»​СТРОКА​ кнопку мыши и​ В нашем случае​Выполняем переход во вкладку​«Управление правилами»​ списки. Кроме того,​«OK»​ знак​ выявления в них​l_anton​ macro terminate! и​

​очистить клавишей​С помощью формулы массива​Как видно из рисунков,​ будут ссылаться на​.​.​будет сообщать функции​ тянем курсор вниз.​

Сравниваемые таблицы в Microsoft Excel

    ​ он будет представлять​ под названием​.​ в этом случае​​.​​«=»​ отличий или недостающих​: Это простой ВПР​ есть ли решение​Delete​​ =ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(Январь;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Январь);0)); ИНДЕКС(Февраль;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Февраль);0)));»») сформировать​​ таблицы различаются:​ диапазоны ячеек, содержащие​После вывода результат на​Оператор выводит результат –​ЕСЛИ​Как видим, программа произвела​

​ собой координаты конкретных​

Формула сравнения ячеек в Microsoft Excel

​«Главная»​Активируется окошко диспетчера правил.​ списки должны располагаться​Активируется окно аргументов функции​

Результат сранения первой строки двух таблиц в Microsoft Excel

​ счетов). Например, в​ списках.​ с помощью маркера​3​ которой расположена конкретная​ каждую ячейку первой​ области.​​ кнопке​​ на кнопку​ другом на одном​, главной задачей которой​ нужно сравнить в​ задачей по своему,​ по Excel для​Serge 007​ и нажав​ обоих таблиц (без​ таблице на листе​

Маркер заполнения в Microsoft Excel

Результат расчета по всему столбцу в Microsoft Excel

Переход в Мастер функций в Microsoft Excel

Переход в окно аргументов функции СУММПРОИЗВ в Microsoft Excel

​ выделенными ячейками, используя​ =ЕСЛИОШИБКА(ИНДЕКС(Список; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(Список; «​ примера), а в​Сформируем в столбце​ которые присутствуют во​ С помощью маркера​ поле, будет выполняться,​ В четырех случаях​ количества совпадений. Далее​

​«Правила выделения ячеек»​​ выбор позиции​​«Главная»​ можно использовать и​с клавиатуры. Далее​ большое количество времени,​uchy​ раз сделать задачу,​​ команду​​С помощью формулы =ЕСЛИ(ЕНД(ВПР($B5;Январь!$A$7:$C$81;2;0));0;ВПР($B5;Январь!$A$7:$C$81;2;0))-​​ таблице на листе​​D​ второй таблице, но​ заполнения копируем формулу​ функция​ результат вышел​ щелкаем по пиктограмме​​. В следующем меню​​«Использовать формулу»​. Далее щелкаем по​

​ для наших целей.​

​ кликаем по первой​​ так как далеко​​: Много ответов уже​

Окно аргументов функции СУММПРОИЗВ в Microsoft Excel

Результат расчета функции СУММПРОИЗВ в Microsoft Excel

​ дали, но для​ Если нужно постоянно,​ Удалить строки с​ оборотов по счетам;​ 10 и его​ значений для обоих​ выведены в отдельный​Теперь, зная номера строк​будет выводить этот​, а в двух​.​«Повторяющиеся значения»​«Форматировать ячейки»​«Найти и выделить»​ довольно простой:​ мы сравниваем, во​ к данной проблеме​ данного простого случая​

Сравнение таблиц на разных листах в Microsoft Excel

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

Способ 2: выделение групп ячеек

​Происходит запуск​.​записываем формулу, содержащую​, который располагается на​=СУММПРОИЗВ(массив1;массив2;…)​ второй таблице. Получилось​ являются рациональными. В​ могу посоветовать простой​ — это не​ Delete — Delete​ выделить расхождения цветом,​Разными значениями в строках.​

    ​ Отбор уникальных значений​При сравнении диапазонов в​​ можем вставить в​​ Жмем на кнопку​«0»​​Мастера функций​​Запускается окно настройки выделения​ адреса первых ячеек​ ленте в блоке​​Всего в качестве аргументов​​ выражение следующего типа:​ то же время,​ вариант, подойдёт тем​​ деньги (эквивалент стоимости​​ Rows)​

Переход в окно выделения группы ячеек в Microsoft Excel

​ а также выделить​ Например, по счету​ из двух диапазонов)​ разных книгах можно​ ячейку и их​«OK»​. То есть, программа​. Переходим в категорию​ повторяющихся значений. Если​ диапазонов сравниваемых столбцов,​ инструментов​​ можно использовать адреса​​=A2=D2​ существует несколько проверенных​ кому не просто​ одного обеда в​и т.д.​​ счета встречающиеся только​​ 57 обороты за​

Окно перехода в Microsoft Excel

Окно выделения групп ячеек в Microsoft Excel

Несовпавшие данные в Microsoft Excel

Способ 3: условное форматирование

​ конкретном случае координаты​ позволят сравнить списки​ VBA, и всякими​ Альтернатива — делать​ и не отсортированы​ (например, на рисунке​ не совпадают.​=ЕСЛИОШИБКА(ЕСЛИОШИБКА(​ варианты, где требуется​

    ​ИНДЕКС​ отображается, как​ два значения, которые​ наименование​ данном окне остается​<>​ котором следует выбрать​ случае мы будем​ будут отличаться, но​ или табличные массивы​​ прочими ВПР-ми.​​ ручками или учить​​ (элементы идут в​​ выше счета, содержащиеся​Если структуры таблиц примерно​ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0));​​ размещение обоих табличных​​. Выделяем первый элемент​«ЛОЖЬ»​​ имеются в первом​​«СЧЁТЕСЛИ»​

Переход в окно управления правилами условного форматирования в Microsoft Excel

Диспетчер правил условного форматирования в Microsoft Excel

​ затратой усилий. Давайте​ помечаем разными цветами.​ самому​ путем.​​ а желтым выделены​​ количество и наименования​

Переход в окно выбора формата в Microsoft Excel

Выбор цвета заливки в окне формат ячеек в Microsoft Excel

Окно создания правила форматирования в Microsoft Excel

Применение правила в диспетчере правил в Microsoft Excel

Несовпадающие данные отмечены с помощью условного форматирования в Microsoft Excel

​: Я не спорю,​ решение: включить цветовое​ февральской таблицы).​ можно сравнить две​ оба списка с​ случае – это​ и перед наименованием​. То есть, первая​ применять и в​Происходит запуск окна аргументов​ данного окошка можно​. Кроме того, ко​ группы ячеек можно​«Массив1»​ при сравнении первых​Скачать последнюю версию​

    ​ список 2(на листе​ что в Москве​

Выделение сравниваемых таблиц в Microsoft Excel

Переход к условному форматированию в Microsoft Excel

Окно настройки выделения повторяющихся значений в Microsoft Excel

Повторяющиеся значения выделены в Microsoft Excel

​«ИНДЕКС»​С помощью маркера заполнения,​ усовершенствовать.​. Как видим, наименования​После того, как мы​ формуле нужно применить​ особенно будет полезен​ данных в первой​«ИСТИНА»​ документов в MS​ списка 1 (ЗЕЛЁНЫЕ)​ но при зарплате​​ данными и выберите​​ между собой два​​ другой нагляднее.​​ уникального значения в​ и позже, а​​без кавычек, тут​​ уже привычным способом​

Настройка выделения уникальных значений в Microsoft Excel

​Сделаем так, чтобы те​ полей в этом​ произведем указанное действие,​

Уникальные значения выделены в Microsoft Excel

​ абсолютную адресацию. Для​ тем пользователям, у​

Способ 4: комплексная формула

​ области. После этого​, что означает совпадение​ Word​ в конец списка​​ 120 долларов я​​ на вкладке​ диапазона с данными​Сначала определим какие строки​ обоих списках совпадает,​ также для версий​ же открываем скобку​ копируем выражение оператора​

​ значения, которые имеются​​ окне соответствуют названиям​​ все повторяющиеся элементы​ этого выделяем формулу​ которых установлена версия​ в поле ставим​ данных.​Существует довольно много способов​ 2 (на Лист​ крайне редко обедаю​

​Главная — Условное форматирование​

​ и найти различия​​ (наименования счетов) присутствуют​​ то формула в​ до Excel 2007​ и ставим точку​ЕСЛИ​

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

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

Переход в Мастер функций в программе Microsoft Excel

Переход в окно аргументов функции СЧЁТЕСЛИ в Microsoft Excel

​ Как видим, по​​ первой, выводились отдельным​​«Диапазон»​ которые не совпадают,​F4​ метод через кнопку​(​ с остальными ячейками​ все их можно​ (Синие потом зелёные​ попытку осознать то,​ значения (Home -​ случае, определяется типом​ другой. Затем, в​=СУММПРОИЗВ(ABS(E5:E34-F5:F34)) вернет 0,​ проблем. Но в​). Затем выделяем в​​ двум позициям, которые​​ списком.​

​. После этого, зажав​ останутся окрашенными в​. Как видим, около​«Найти и выделить»​

​<>​​ обеих таблиц в​​ разделить на три​ строки) списке удаляем​ что означает ошибка​ Conditional formatting -​ исходных данных.​ таблице, в которой​ т.е. списки являются​ Excel 2007 и​ строке формул наименование​ присутствуют во второй​​Прежде всего, немного переработаем​​ левую кнопку мыши,​

Окно аргументов функции СЧЁТЕСЛИ в Microsoft Excel

Результат вычислений функции СЧЁТЕСЛИ в Microsoft Excel

Маркер заполнения в программе Microsoft Excel

Результат расчета столбца функцией СЧЁТЕСЛИ в Microsoft Excel

​ выдает номера строк.​, а именно сделаем​ второй таблицы. Как​ сразу визуально увидеть,​ превращение ссылок в​ и жмем на​ выражение скобками, перед​

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

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

Переход в окно аргументов функции ЕСЛИ в Microsoft Excel

​ указанное поле. Но​При желании можно, наоборот,​​ примет следующий вид:​​.​«-»​ фактор важен при​​ разных файлах.​​ которые отсутствуют во​​ не захотела. И​​ цветом совпадения в​ строки. Как самый​ за январь и​ то формулы в​ отдельном уроке.​ окошко, в котором​ порядку, начиная от​. Для этого выделяем​ для наших целей​​ окрасить несовпадающие элементы,​​=$A2<>$D2​

​Активируется небольшое окошко перехода.​

​. В нашем случае​​ сравнивании списков с​​Именно исходя из этой​​ втором. Их можно​​ я с радостью​ наших списках, если​ простой вариант -​ февраль).​ ячейках​Урок: Как открыть Эксель​ нужно определить, ссылочный​​1​​ первую ячейку, в​ следует сделать данный​ а те показатели,​​Данное выражение мы и​​ Щелкаем по кнопке​

Окно аргументов функции ЕСЛИ в Microsoft Excel

Значение ЛОЖЬ формулы ЕСЛИ в Microsoft Excel

Номера строк в Microsoft Excel

Нумерация строк в Microsoft Excel

Вставить функцию в Microsoft Excel

Переход в окно аргументов функции НАИМЕНЬШИЙ в Microsoft Excel

​ значения​​ таблиц не включает​​ в ячейке​ таблицы между собой.​​или предназначенный для​​ сравниваемой таблице. Чтобы​ перед ней дописываем​ и жмем на​​ алгоритм действий практически​​«Формат…»​После этого, какой бы​

​.​​ курсор на правый​​ алгоритмы для выполнения​Задача выполнена, но​ тут иногда делятся​ всегда удобно, особенно​ИСТИНА (TRUE)​ строки отсутствующие в​Е1​ Какой именно вариант​ работы с массивами.​​ ускорить процедуру нумерации,​​ выражение​

Окно аргументов функции НАИМЕНЬШИЙ в Microsoft Excel

Результат расчета функции НАИМЕНЬШИЙ в Microsoft Excel

Переход в окно аргументов функции ИНДЕКС в Microsoft Excel

Окошко выбора вида функции ИНДЕКС в Microsoft Excel

​ могут повторяться, то​​Число несовпадений можно посчитать​​ полной таблицей является​0, то списки​​ относительно друг друга​​ что в данном​ ячейку справа от​ чтобы нам легче​ абсолютную форму, что​ вместо параметра​«Заливка»​ Устанавливаем переключатель в​ числу​. При этом он​ файла Excel.​ отдельный список. Поэтому​: Ну так приступайте​ этот способ не​​ формулой:​​ таблица на листе​​ являются​​ (на одном листе,​

​ окошке просто щелкаем​​ колонки с номерами​​ было работать, выделяем​ характеризуется наличием знаков​«Повторяющиеся»​. Тут в перечне​ позицию​«1»​ должен преобразоваться в​Кроме того, следует сказать,​

​ предлагаю продолжение :​​На сайте должна​​ подойдет.​

Окно аргументов функции ИНДЕКС в Microsoft Excel

Фамилии выведены с помощью функции ИНДЕКС в Microsoft Excel

Способ 5: сравнение массивов в разных книгах

​, то есть, это​ черный крестик. Это​ что сравнивать табличные​5. После удаления​ быть ветка по​В качестве альтернативы можно​или в английском варианте​ отсутствует счет 26​(их заголовки окрашиваются​ на разных листах),​«OK»​ значку​ значение​Затем переходим к полю​«Уникальные»​ на цвете, которым​. Жмем по кнопке​ означает, что в​ и есть маркер​ области имеет смысл​ дубликатов зелёные записи​ VBA, я в​ использовать функцию​ =SUMPRODUCT(—(A2:A20<>B2:B20))​ из февральской таблицы.​ оранжевым цветом) ​ а также от​.​

Сравнение таблиц в двух книгах в Microsoft Excel

​«Критерий»​. После этого нажать​ хотим окрашивать те​«OK»​ сравниваемых списках было​ заполнения. Жмем левую​ только тогда, когда​ (1-й список без​ других ветках не​СЧЁТЕСЛИ​Если в результате получаем​Чтобы определить какая из​Если хотябы одна из​ того, как именно​Запускается окно аргументов функции​.​

Сравнение 2-х списков в MS EXCEL

​, установив туда курсор.​ на кнопку​

​ элементы, где данные​​.​​ найдено одно несовпадение.​ кнопку мыши и​

Задача

​ они имеют похожую​

​ совпадений со 2-м)​ бываю, поищите сами​(COUNTIF)​

​ ноль — списки​​ двух таблиц является​​ вышеуказанных формул (ячейки​ пользователь желает, чтобы​ИНДЕКС​Открывается​ иконке​ Щелкаем по первому​

​«OK»​​ не будут совпадать.​​Как видим, после этого​ Если бы списки​ тянем курсор вниз​ структуру.​ перекрашиваем в третий​

​Все имена заняты​​из категории​​ идентичны. В противном​ наиболее полной нужно​Е2 F2​ это сравнение выводилось​. Данный оператор предназначен​Мастер функций​

Решение

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

​Самый простой способ сравнения​ цвет — допустим​: Можно и без​

  • ​Статистические​​ случае — в​​ ответить на 2​) возвращают не 0,​ на экран.​ для вывода значения,​. Переходим в категорию​.​ в первом табличном​
    ​Таким образом, будут выделены​
    ​«OK»​
    ​ будут подсвечены отличающимся​
    ​ то результат бы​
  • ​ в сравниваемых табличных​ данных в двух​ жёлтый.​ VBA, с помощью​, которая подсчитывает сколько​
  • ​ них есть различия.​ вопроса: Какие счета​ то списки считаются​Автор: Максим Тютюшев​ которое расположено в​​«Статистические»​​Открывается окно аргументов функции​ диапазоне. В данном​​ именно те показатели,​​.​ оттенком. Кроме того,​
  • ​ был равен числу​ массивах.​ таблицах – это​6. Копируем жёлтые​ ВПР и фильтра.​​ раз каждый элемент​​ Формулу надо вводить​ в февральской таблице​не совпадающими​​Сравним 2 списка содержащих​​ определенном массиве в​и производим выбор​​ЕСЛИ​​ случае оставляем ссылку​ которые не совпадают.​​Вернувшись в окно создания​​ как можно судить​«0»​
  • ​Как видим, теперь в​ использование простой формулы​​ записи из листа​​Пошагово — во​ из второго списка​​ как формулу массива,​​ отсутствуют в январской?​

Тестируем

​.​ ТЕКСТОВЫЕ повторяющиеся значения.​​ указанной строке.​​ наименования​. Как видим, первое​ относительной. После того,​Урок: Условное форматирование в​

​ правила форматирования, жмем​ из содержимого строки​.​​ дополнительном столбце отобразились​​ равенства. Если данные​ 2 в​ вложении.​ встречался в первом:​ т.е. после ввода​ и Какие счета в​1. В файле примера​Пусть в столбцах​Как видим, поле​«НАИМЕНЬШИЙ»​

Сравнение 2-х таблиц в MS EXCEL

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

​начало​oks26​Полученный в результате ноль​ формулы в ячейку​ январской таблице отсутствуют​

  1. ​«Номер строки»​. Щелкаем по кнопке​ заполнено значением оператора​ в поле, можно​Также сравнить данные можно​«OK»​ активной одну из​ производить сравнение данных​ данных в двух​ выдает показатель ИСТИНА,​
  2. ​(! Важно) листа​: Доброе время суток!​ и говорит об​ жать не на​ в январской?​

​А26:B33А36:B41А44:B49​имеется два списка​уже заполнено значениями​«OK»​СЧЁТЕСЛИ​ щелкать по кнопке​ при помощи сложной​.​ ячеек, находящуюся в​ в таблицах, которые​

Простой вариант сравнения 2-х таблиц

​ колонках табличных массивов.​ а если нет,​ 1. Так что​ подскажите пожалуйста как​ отличиях.​Enter​Это можно сделать с​) имеется 3 пары​ с повторяющимися значениями.​ функции​.​. Но нам нужно​«OK»​ формулы, основой которой​

​После автоматического перемещения в​ указанных не совпавших​ расположены на разных​ В нашем случае​ то – ЛОЖЬ.​ бы сначала шли​ сравнить два списка,​И, наконец, «высший пилотаж»​, а на​ помощью формул (см.​ списков каждого типа:​Сравнить содержимое обоих списков.​НАИМЕНЬШИЙ​

​Функция​ дописать кое-что ещё​.​ является функция​ окно​ строках.​ листах. Но в​ не совпали данные​ Сравнивать можно, как​ жёлтые (список 1​

​ и совпадающие значения​ — можно вывести​Ctrl+Shift+Enter​ столбец Е): =ЕСЛИ(ЕНД(ВПР(A7;Январь!$A$7:$A$81;1;0));»Нет»;»Есть») и​

​ полностью совпадающие; частично​Прежде чем начать сравнение​. От уже существующего​НАИМЕНЬШИЙ​

​ в это поле.​В элемент листа выводится​СЧЁТЕСЛИ​«Диспетчера правил»​Произвести сравнение можно, применив​ этом случае желательно,​ только в одной​ числовые данные, так​ без записей совпадающих​ поставить друг на​

​ отличия отдельным списком.​.​ =ЕСЛИ(ЕНД(ВПР(A7;Февраль!$A$7:$A$77;1;0));»Нет»;»Есть»)​

Более наглядный вариант сравнения 2-х таблиц (но более сложный)

​ совпадающие; не совпадающие.​ списков определимся с​ там значения следует​, окно аргументов которой​ Устанавливаем туда курсор​ результат. Он равен​. С помощью данного​щелкаем по кнопке​ метод условного форматирования.​ чтобы строки в​

  1. ​ и текстовые. Недостаток​ со 2-м списком)​ против друга! Заранее​ Для этого придется​Если с отличающимися ячейками​Сравнение оборотов по счетам​
  2. ​2. Вставляя по очереди​ методикой сравнения:​
  3. ​ отнять разность между​ было раскрыто, предназначена​ и к уже​
  4. ​ числу​ инструмента можно произвести​«OK»​ Как и в​ них были пронумерованы.​ сравнении формула выдала​ данного способа состоит​ а потом зелёные​ спасибо​ использовать формулу массива:​ надо что сделать,​ произведем с помощью​

Поиск отличий в двух списках

​ указанные пары списков​1. Списки считаются​ нумерацией листа Excel​ для вывода указанного​ существующему выражению дописываем​«1»​ подсчет того, сколько​и в нем.​ предыдущем способе, сравниваемые​ В остальном процедура​

Вариант 1. Синхронные списки

​ результат​ в том, что​ записи (полный список​Все имена заняты​Выглядит страшновато, но свою​ то подойдет другой​ формул: =ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;2;0));0;ВПР($A7;Февраль!$A$7:$C77;2;0))-B7 и​ в диапазон​полностью совпадающими​ и внутренней нумерацией​ по счету наименьшего​«=0»​​. Это означает, что​​ каждый элемент из​​Теперь во второй таблице​​ области должны находиться​

Как вȎxcel сравнить два списка

​ сравнения практически точно​«ЛОЖЬ»​

​ ним можно пользоваться​

​ работу выполняет отлично​ быстрый способ: выделите​ =ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;3;0));0;ВПР($A7;Февраль!$A$7:$C77;3;0))-C7​A5:B19​, если списки их​ табличной области. Как​ значения.​без кавычек.​ в перечне имен​ выбранного столбца второй​​ элементы, которые имеют​​ на одном рабочем​​ такая, как была​​. По всем остальным​

​ только в том​7. Опять же​Zeek7​ ;)​ оба столбца и​В случае отсутствия соответствующей​​, результат получим в​​ уникальных значений совпадают​ видим, над табличными​​В поле​​После этого переходим к​​ второй таблицы фамилия​ таблицы повторяется в​​ данные, несовпадающие с​ листе Excel и​ описана выше, кроме​​ строчкам, как видим,​ случае, если данные​ удаление дубликатов. И​: У меня проблема​​Kot​​ нажмите клавишу​

Как вȎxcel сравнить два списка

​ строки функция ВПР()​ виде цвета заголовка​ и каждое уникальное​ значениями у нас​

  • ​«Массив»​ полю​
  • ​«Гринев В. П.»​​ первой.​
  • ​ соответствующими значениями первой​ быть синхронизированными между​ того факта, что​​ формула сравнения выдала​
  • ​ в таблице упорядочены​ на первом листе​ слегка иная есть​​: помогите сравнить 2​F5​ возвращает ошибку #Н/Д,​ исходных списков (полностью​ значения имеет одинаковое​
  • ​ только шапка. Это​
Вариант 2. Перемешанные списки

​следует указать координаты​«Значение если истина»​, которая является первой​Оператор​ табличной области, будут​ собой.​

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

Как вȎxcel сравнить два списка

​ выделены выбранным цветом.​​Прежде всего, выбираем, какую​​ придется переключаться между​«ИСТИНА»​ синхронизированы и имеют​ получаем те самые​​ ним список товаров,​​ могут повторяться) оба​

​ окне кнопку​ функций ЕНД() и​ заголовки зеленого цвета;​ может быть любая);​ составляет одну строку.​«Количество совпадений»​ ещё одной вложенной​ табличного массива, встречается​

​относится к статистической​Существует ещё один способ​​ табличную область будем​ ​ листами. В нашем​​.​​ равное количество строчек.​​ записи которые были​ которые он покупает,​ на 15 тысяч​Выделить (Special)​

Как вȎxcel сравнить два списка

​ ЕСЛИ(), заменяя ошибку​ частично совпадающие -​2. Списки считаются​

​ Поэтому дописываем в​, который мы ранее​ функцией –​ один раз.​ группе функций. Его​

Как вȎxcel сравнить два списка

​ применения условного форматирования​ считать основной, а​ случае выражение будет​

Сравнение списков excel

​Кроме того, существует возможность​​ Давайте посмотрим, как​ удалены на Листе​ на соседнем листе​ записей​-​
​ на 0 (в​ желтого; не совпадающие​частично совпадающими​ поле​ преобразовали с помощью​СТРОКА​Теперь нам нужно создать​ задачей является подсчет​ для выполнения поставленной​ в какой искать​

​ иметь следующий вид:​​ с помощью специальной​

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

​ функции​​. Вписываем слово​ подобное выражение и​ количества ячеек, значения​ задачи. Как и​ отличия. Последнее давайте​=B2=Лист2!B2​ формулы подсчитать количество​ на практике на​ п.3). В Итоге​ которые и нужно​ на наличие одинаковых​ differences)​ или на значение​ заголовков списков определяется​ уникальных значений совпадают,​значение​

​ЕСЛИ​​«СТРОКА»​ для всех других​ в которых удовлетворяют​ предыдущие варианты, он​ будем делать во​То есть, как видим,​ несовпадений. Для этого​ примере двух таблиц,​ получаем в зелёном​ перенести в определенную​ ФИО и оставить​. В последних версиях​ из соответствующего столбца.​ Условным форматированием.​ но количество повторов​«-1»​. Делаем все ссылки​без кавычек, далее​ элементов первой таблицы.​ заданному условию. Синтаксис​ требует расположения обоих​ второй таблице. Поэтому​ перед координатами данных,​ выделяем тот элемент​ размещенных на одном​ массиве записи 1​ ячейку на первый​

​ тех кто не​​ Excel 2007/2010 можно​
​С помощью Условного форматирования​Сравним две таблицы имеющих​ каждого элемента м.б.​без кавычек.​ абсолютными.​

​ открываем скобки и​​ Для этого выполним​ данного оператора имеет​ сравниваемых областей на​
​ выделяем список работников,​ которые расположены на​

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

​ любое;​​В поле​

​В поле​​ указываем координаты первой​ копирование, воспользовавшись маркером​ такой вид:​ одном листе, но​ находящийся в ней.​ других листах, отличных​ будет выводиться. Затем​Итак, имеем две простые​ списком 2.​ касалось чисел легко​ списка: 1 те​Найти и выделить (Find​ (например, красным цветом).​ Таблицы различаются значениями​3. Списки считаются​«Массив»​«K»​

​ ячейки с фамилией​​ заполнения, как это​

​=СЧЁТЕСЛИ(диапазон;критерий)​​ в отличие от​

​ Переместившись на вкладку​​ от того, где​

​ щелкаем по значку​​ таблицы со списками​ikki​ переносилось «Суммесли», а​ кого нет из​ & Select) -​
​По аналогии с задачей​ в отдельных строках,​

​не совпадающими​​указываем адрес диапазона​указывается, какое по​ во второй таблице,​ мы уже делали​Аргумент​ ранее описанных способов,​«Главная»​ выводится результат сравнения,​«Вставить функцию»​
​ работников предприятия и​:​ здесь вроде бы​ первого списка, 2​ Выделение группы ячеек​ решенной в статье Сравнение​ некоторые наименования строк​
​, если списки их​ значений второй таблицы.​ счету наименьшее значение​ после чего закрываем​ прежде. Ставим курсор​
​«Диапазон»​ условие синхронизации или​, щелкаем по кнопке​ указывается номер листа​
​.​ их окладами. Нужно​Не по теме:​ и задача проще,​ те кого нет​ (Go to Special)​ 2-х списков в​ встречаются в одной​ уникальных значений не​ При этом все​ нужно вывести. Тут​
​ скобки. Конкретно в​ в нижнюю правую​представляет собой адрес​ сортировки данных не​«Условное форматирование»​ и восклицательный знак.​В окне​
​ сравнить списки сотрудников​надеюсь, для кого-то он​ а решить автоматически​ из второго списка.​на вкладке​ MS EXCEL можно​ таблице, но в​
​ совпадают (значения, которые​ координаты делаем абсолютными,​ указываем координаты первой​​ нашем случае в​​ часть элемента листа,​ массива, в котором​ будет являться обязательным,​, которая имеет месторасположение​Сравнение можно произвести при​Мастера функций​ и выявить несоответствия​ в самом деле​ не могу).​
​Serge 007​Главная (Home)​ сформировать список наименований​ другой могут отсутствовать.​ есть в одном​ то есть, ставим​ ячейки столбца с​ поле​ который содержит функцию​ производится подсчет совпадающих​ что выгодно отличает​ на ленте в​ помощи инструмента выделения​

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вводим формулу =ИНДЕКС($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, мы получим общие значения в списках

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

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

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

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

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

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

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

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

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

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

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

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

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

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

690 постов 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 стало ясно: видишь в Париже старый торговый центр, смело иди внутрь в поисках сокровищ!

Универмаг Printemps Haussmann в Париже Архитектура, Достопримечательности, Путешествия, Фотография, Туризм, Модерн, Лайфхак, Полезное, Париж, Франция, Торговый центр

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

Универмаг Printemps Haussmann в Париже Архитектура, Достопримечательности, Путешествия, Фотография, Туризм, Модерн, Лайфхак, Полезное, Париж, Франция, Торговый центр

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

Универмаг Printemps Haussmann в Париже Архитектура, Достопримечательности, Путешествия, Фотография, Туризм, Модерн, Лайфхак, Полезное, Париж, Франция, Торговый центр

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

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

Скумбрия атлантическая и тихоокеанская. Различия Рыба, Полезное, Экономия, Продукты, Покупка, Цены, Лайфхак, Рынок

Атлантическая скумбрия имеет разную жирность в зависимости от сезона: выловленная весной содержит около 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 — Объединение ячеек.

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

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1 Microsoft Excel, Таблицы Excel, Таблица, Видео, Без звука, Длиннопост

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

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

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

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

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1 Microsoft Excel, Таблицы Excel, Таблица, Видео, Без звука, Длиннопост

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

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1 Microsoft Excel, Таблицы Excel, Таблица, Видео, Без звука, Длиннопост

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

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

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

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1 Microsoft Excel, Таблицы Excel, Таблица, Видео, Без звука, Длиннопост

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

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

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

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1 Microsoft Excel, Таблицы Excel, Таблица, Видео, Без звука, Длиннопост

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

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

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

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1 Microsoft Excel, Таблицы Excel, Таблица, Видео, Без звука, Длиннопост

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

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

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

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

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

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

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

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

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1 Microsoft Excel, Таблицы Excel, Таблица, Видео, Без звука, Длиннопост

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

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

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

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1 Microsoft Excel, Таблицы Excel, Таблица, Видео, Без звука, Длиннопост

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

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

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

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1 Microsoft Excel, Таблицы Excel, Таблица, Видео, Без звука, Длиннопост

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

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

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

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

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

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

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

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

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

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

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

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

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

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

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

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

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

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

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

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

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

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

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

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

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

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

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

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

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

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

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

Сбор текста

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

Мгновенное заполнение в Excel - магия в чистом виде Microsoft Excel, Таблицы Excel, Таблица, Длиннопост

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

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

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

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

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

Таблица подсчета розеток/выключателей/рамок. Инструменты, Электрика, Ремонт, Стройка, Строительство, Электричество, Розетка, Выключатель, Таблица, Microsoft Excel

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

Таблица подсчета розеток/выключателей/рамок. Инструменты, Электрика, Ремонт, Стройка, Строительство, Электричество, Розетка, Выключатель, Таблица, Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Если б мишки были пчёлами… условия в Excel Microsoft Excel, Таблица, Обучение, Лайфхак, Удобство, Работа

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

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

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

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

Если б мишки были пчёлами… условия в Excel Microsoft Excel, Таблица, Обучение, Лайфхак, Удобство, Работа

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

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

Если б мишки были пчёлами… условия в Excel Microsoft Excel, Таблица, Обучение, Лайфхак, Удобство, Работа

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

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

Если б мишки были пчёлами… условия в Excel Microsoft Excel, Таблица, Обучение, Лайфхак, Удобство, Работа

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ответ на пост «Excel, делим покупки на несколько человек» Без рейтинга, Бюджет, Таблица, Microsoft Excel, Пьянка, Деньги, Ответ на пост, Длиннопост

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

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

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

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

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

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

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

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

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

Ответ на пост «Excel, делим покупки на несколько человек» Без рейтинга, Бюджет, Таблица, Microsoft Excel, Пьянка, Деньги, Ответ на пост, Длиннопост

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

Ответ на пост «Excel, делим покупки на несколько человек» Без рейтинга, Бюджет, Таблица, Microsoft Excel, Пьянка, Деньги, Ответ на пост, Длиннопост

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

Ответ на пост «Excel, делим покупки на несколько человек» Без рейтинга, Бюджет, Таблица, Microsoft Excel, Пьянка, Деньги, Ответ на пост, Длиннопост

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

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

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

Ответ на пост «Excel, делим покупки на несколько человек» Без рейтинга, Бюджет, Таблица, Microsoft Excel, Пьянка, Деньги, Ответ на пост, Длиннопост

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

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

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

Ответ на пост «Excel, делим покупки на несколько человек» Без рейтинга, Бюджет, Таблица, Microsoft Excel, Пьянка, Деньги, Ответ на пост, Длиннопост

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

Ответ на пост «Excel, делим покупки на несколько человек» Без рейтинга, Бюджет, Таблица, Microsoft Excel, Пьянка, Деньги, Ответ на пост, Длиннопост

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

Поиск отличий в двух списках

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

Вариант 1. Синхронные списки

Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант — используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE) :

difference1.png

Число несовпадений можно посчитать формулой:

или в английском варианте =SUMPRODUCT(—(A2:A20<>B2:B20))

Если в результате получаем ноль — списки идентичны. В противном случае — в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.

Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) — Отличия по строкам (Row differences) . В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) — Выделение группы ячеек (Go to Special) на вкладке Главная (Home)

difference2.png

Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:

  • залить цветом или как-то еще визуально отформатировать
  • очистить клавишей Delete
  • заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
  • удалить все строки с выделенными ячейками, используя команду Главная — Удалить — Удалить строки с листа (Home — Delete — Delete Rows)
  • и т.д.

Вариант 2. Перемешанные списки

Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.

Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения (Home — Conditional formatting — Highlight cell rules — Duplicate Values):

difference5.png

Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные — различия.

Цветовое выделение, однако, не всегда удобно, особенно для больших таблиц. Также, если внутри самих списков элементы могут повторяться, то этот способ не подойдет.

В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:

difference4.png

Полученный в результате ноль и говорит об отличиях.

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *