Как сравнить текстовые ячейки в excel

Excel сравнение нескольких ячеек

Функция СОВПАД в Excel предназначена для проверки двух текстовых срок на их полное соответствие друг другу и возвращает одно из двух возможных логических значений: ИСТИНА – если строки полностью совпадают, ЛОЖЬ – если обнаружено хотя бы одно несоответствие в сравниваемых данных. При работе данной функции учитывается регистр букв. Это значит, что результат сравнения двух строк «текст» и «ТеКсТ» будет равен значению ЛОЖЬ. Распространенный вариант применения рассматриваемой функции – определение вхождения некоторого текста в документ.

Сравнение двух таблиц по функции СОВПАД в Excel

Пример 1. Есть две одинаковые (на первый взгляд) таблицы данных, которые содержат наименования продукции. Одну из них предположительно редактировал уволенный работник. Необходимо быстро сравнить имеющиеся данные и выявить несоответствия.

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

Для сравнения двух строк используем следующую формулу массива (CTRL+SHIFT+Enter):

Описание параметров функции СОВПАД:

• D3 – текущая ячейка с текстом из второй таблицы;
• $B$3:$B$13 – соответствующая ячейка с текстом из второй таблицы для проверки на совпадение со значением D3.

Функция ИЛИ возвращает логическое значение ИСТИНА из массива если хотя бы одно из них совпадает с исходным значением.

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

Как видно, в сравниваемых строках были найдены несоответствия.

Выборка значений из таблицы по условию в Excel без ВПР

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

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

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

Функция мода определяет наиболее часто повторяющиеся числовые данные в диапазоне цен. Функция ПОИСКПОЗ находит позицию первой ячейки из диапазона, в которой содержится цена самого популярного товара. Полученное значение выступает в качестве первого аргумента функции адрес, возвращающей ссылку на искомую ячейку (к значению прибавлено число 2, поскольку отсчет начинается с третьей строки сверху). Функция ДВССЫЛ возвращает значение, хранящееся в ячейке по ее адресу.

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

Для определения общей прибыли от продаж iPhone 5s используем следующую запись:

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

Результат расчетов формулы:

Всего было куплено 4 модели iPhone 5s по цене 239 у.е., что в целом составило 956 у.е.

Правила синтаксиса и параметры функции СОВПАД в Excel

Функция СОВПАД имеет следующий вариант синтаксической записи:

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

1. Результат выполнения функции СОВПАД, принимающей на вход два имени, является код ошибки #ИМЯ? (например, СОВПАД(имя;имя)). Для корректной работы функции указываемые текстовые данные необходимо помещать в кавычки (например, («имя»;«имя»)).
2. Функция выполняет промежуточное преобразование числовых данных в текст. Например, результат выполнения =СОВПАД(111;111) будет логическое значение ИСТИНА. Однако, преобразование логических данных в числа текстового формата не выполняется. Например, результат выполнения =СОВПАД(ИСТИНА;1) будет логическое ЛОЖЬ.
3. Результат сравнения двух пустых ячеек или пустых текстовых строк с использованием функции СОВПАД — логическое ИСТИНА.

Добрый день!

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

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

Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

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

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

Быстрое выделение значений, которые отличаются

Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии, ну или отсутствии отличий между таблицами, вам нужно на вкладке «Главная», выбрать кнопку меню «Найти и выделить», предварительно выделив диапазон где надо сравнить две таблицы в Excel. В открывшимся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».

Сравнить две таблицы в Excel с помощью условного форматирования

Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить условное форматирование вы можете на вкладке «Главная», нажав кнопку «Условное форматирование» и в предоставленном списке выбираем «Управление правилами». В диалоговом окне «Диспетчер правил условного форматирования», жмем кнопочку «Создать правило» и в новом диалоговом окне «Создание правила форматирования», выбираем правило «Использовать формулу для определения форматируемых ячеек». В поле «Изменить описание правила» вводим формулу =$C2<>$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат». Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию. Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок».

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

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

Все вышеперечисленные способы хороши для упорядоченных таблиц, а вот когда данные, не упорядоченные необходимы иные способы один из которых мы сейчас и рассмотрим. Представим, к примеру, у нас есть 2 таблицы, значения в которых немного отличаются и нам необходимо сравнить эти таблицы для определения значения, которое отличается. Выделяем значение в диапазоне первой таблицы и на вкладке «Главная», пункт меню «Условное форматирование» и в списке жмем пункт «Создать правило…», выбираем правило «Использовать формулу для определения форматируемых ячеек», вписываем формулу =СЧЁТЕСЛИ($C$1:$C$7;C1)=0 и выбираем формат условного форматирования.

Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C$1:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.

Как сравнить две таблицы в Excel с помощью функции ВПР

В этом варианте мы будем использовать функцию ВПР, которая позволит нам сравнить две таблицы на предмет совпадений. Для сравнения двух столбиков, введите формулу =ВПР(C2;$D$2:$D$7;1;0) и скопируйте ее на весь сравниваемый диапазон. Эта формула последовательно начинает проверять есть ли повторы значения из столбика А в столбике В, ну и соответственно возвращает значение элемента, если оно было там найдено если же значение не найдено получаем ошибку #Н/Д.

Как сравнить две таблицы в Excel функции ЕСЛИ

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

Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E$2:$E$7;0));"";C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

Сравнить две таблицы с помощью макроса VBA

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

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

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

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

Пример 1. Как сравнить два столбца на совпадения и различия в одной строке

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

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

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

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

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

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

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

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

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

Пример1. Как найти совпадения в одной строке в нескольких столбцах таблицы

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

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

В формуле в качестве “5” указано число столбцов таблицы, для которой мы создали формулу. Если в вашей таблице столбцов больше или меньше, то это значение должно быть равно количеству столбцов.

Пример 2. Как найти совпадения в одной строке в любых двух столбцах таблицы

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

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

=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)

Первая функция СЧЁТЕСЛИ вычисляет количество столбцов в строке со значением в ячейке А2 , вторая функция СЧЁТЕСЛИ вычисляет количество столбцов в таблице со значением из ячейки B2 . Если результат вычисления равен “0” – это означает, что в каждой ячейке, каждого столбца, этой строки находятся уникальные значения. В этом случае формула выдаст результат “Уникальная строка”, если нет, то “Не уникальная строка”.

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

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

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.

Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.

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

Когда мы ищем совпадения между двумя столбцами в Excel, нам может потребоваться визуализировать найденные совпадения или различия в данных, например, с помощью выделения цветом. Самый простой способ для выделения цветом совпадений и различий – использовать “Условное форматирование” в Excel. Рассмотрим как это сделать на примерах ниже.

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

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

• Выделить столбцы с данными, в которых нужно вычислить совпадения;
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены цветом совпадения:

Поиск и выделение цветом совпадающих строк в Excel

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

В таблицах выше размещены одинаковые данные. Их отличие в том, что на примере слева мы искали совпадающие ячейки, а справа мы нашли целые повторяющие строчки с данными.

Рассмотрим как найти совпадающие строки в таблице:

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

Во вспомогательной колонке вы увидите объединенные данные таблицы:

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

• Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены дублирующиеся строки:

На примере выше, мы выделили строки в созданной вспомогательной колонке.

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

Для этого сделаем следующее:

• Так же как и в примере выше создадим вспомогательный столбец, в каждой строке которого проставим следующую формулу:

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

• Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15 ;
• Затем, на вкладке “Главная” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:

• В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:

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

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

Частичное текстовое сравнение ячеек Excel ⁠ ⁠

Есть две текстовые ячейки Экселя. В одной, скажем, «абырвалг», в другой — «абырвалг; главсельхоз; спасиботов» или «главсельхоз; спасиботов» (или ещё что угодно, кроме абырвалга). Какой формулой посчитать, что во второй ячейке есть «абырвалг» или процент совпадения текстов на предмет элемента «абырвалг»?

Пробовал надстройку «Fuzzy Lookup», но при попытке включения графы similarity она либо уходит в бесконечность, либо Эксель зависает.

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

Win 7/8/10, Office — настройка

149 постов 2.8K подписчиков

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

— В свободной форме комментировать и обсуждать посты и другие комментарии.

— Оскорблять кого-либо из пользователей пикабу.

— Размещать контент не по теме сообщества (последнее решение за модератором).

— Размещать рекламу (контент и/или ссылки на платные программы).

Бан можно получить:

— За регулярное нарушение правил

Будьте культурными няшами и все будет хорошо 🙂

народ совсем оленился, в гугле первый же ответ: Функция ПОИСК() в MS EXCEL

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

СУММЕСЛИ или СУММЕСЛИМН практически одинаковые, разбив предварительно на слова по разным ячейка по ;

Образец — =ПСТР взять из текста нужную построку

Поиск- =НАЙТИ или ПОИСК — искать образцовую строку в тексте

Фильтр по маске

Из простого только fuzzy. Я обычно им свожу.

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

Ответы.мэйл.ру на другом сайте.

Прошу помощи в Excel⁠ ⁠

Не могу загуглить правильно или просто я тупой. Прошу помочь.

Имеется таблица — бланк заказа. Условно говоря столбец 1 — номер, столбец 2 — лук, столбец 3 — 2кг.

Но номенклатура около 1000 продуктов, а бланк резать нельзя. Как поставить фильтр, чтобы выводились только те строки, в которых столбец 3 (количество товара) не пустой.

Заранее спасибо, если кто-то поможет.

А я вам за это фото котят.

Upd всё оказалось просто, спасибо, дорогие пикабушники.

Тормозит Excel? Файлы из 1С. Есть решение⁠ ⁠

Чтобы решить эту проблему миллионов пользователей Excel и 1С, решение которого нет даже в Майкрософт.

Надо просто отодвинуть скрытый список листов, чтобы они стали видны.

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

Причина и пользовательское решение стало известно ещё 2017-07-26, но майкрософт ещё не исправили этот баг и его можно наблюдать даже в новых версиях MS Office в том числе 2019 и 365.

Исключение неверных текстовых значений в Excel⁠ ⁠

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

Перенос строк в Microsoft Excel⁠ ⁠

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

Рассмотрим каждый из вариантов переноса подробнее и разберём обратную операцию –удаление переносов.

Автоматический перенос текста

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

На вкладке Главная ► Перенести текст (Выравнивание):

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

Если текст отображается не полностью, то задана точная высота строки (столбца) или текст находится в объединенных ячейках.

Ручной ввод разрыва строки

Новую строку текста можно начать в любом месте ячейки.

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

2. Дважды щелкните в ячейке на место, в которое нужно вставить разрыв строки, и нажмите сочетание клавиш Alt+Enter:

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

Удаление переносов строк

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

В случае с автоматическими переносами, их просто нужно отключить, на вкладке Главная ► Перенести текст (Выравнивание).

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

1. Выделите область в которой нужно удалить переносы.

2. Нажмите сочетание клавиш Ctrl+H, в открывшемся окне выделите курсором поле Найти и нажмите сочетание клавиш Ctrl+J.

Вроде ничего не произошло, но для Excel запрос для поиска понятен:

Нажмите Заменить. Готово!

Если в строке, до переноса, нет пробела замена его не добавит!

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

Решить задачу, можно встроенной функцией =ПЕЧСИМВ(), которая очищает текст от всех непечатаемых символов, включая переносы строк:

Если в строке, до переноса, нет пробела формула его не добавит!

Более продвинутый вариант, использование макросов:

Спасибо, что дочитали до конца!

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

Вывод списка файлов в папке на лист Excel⁠ ⁠

Перед вами стоит задача вывести списком содержимое папки? Этот пост для вас.

Вывести cписок файлов в папке можно макросом:

Копируйте код и сохраните его в Личной книге макросов.

Открыть редактор VBA Alt +F11, вызвать меню для выполнения или изменения макросов Alt + F8.

Word, excel — добавление часто используемых функций в панель быстрого доступа⁠ ⁠

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

Выглядит это вот так:

Чтобы это сделать, наведите курсор на нужную вам функцию и нажмите правую кнопку мыши. Во всплывающем окне выберите первую команду.

Также, в эту панель можно добавлять и макросы. Для этого нужно перейти в параметры Word (Excel, PowerPoint) и выбрать там пункт «Панель быстрого доступа», либо нажать крайнюю правую кнопку на панели и там выбрать пункт «Другие команды» :

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

1. выбрать из всплывающего списка пункт «Макросы»

2. выбрать макрос, который вы хотите добавить на панель быстрого доступа

3. нажать «Добавить»

Может быть, кому-то будет полезно)

Наша победа! Сообществу быть!⁠ ⁠

Уважаемые подписчики, спешу вас обрадовать @SupportCommunity разрешил создать сообщество посвящённое Office. Спасибо вам за поддержку))

Сообщество будет посвящено MS Office, Libreoffice и Google docs.

Я хочу чтобы сообщество приносило пользу многим, дабы облегчить работу офисному брату))

Тех кто владеет Libreoffice и Google docs призываю вас быть активней, сообществу понадобятся модераторы, чтобы следить за порядком и публиковать полезные посты.

Лига Microsoft Office⁠ ⁠

Уважаемая администрация сайта и подписчики, я хочу создать Лигу Microsoft Office, в которой будут размещаться посты про Word, Excel, Access, Power Point, Outlook, Publisher и др., где пользователи смогут узнать что-то новое или поделиться своим опытом.

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

Тем кому будет не интересна данная тематика смогут поставить сообщество в игнор.

Ссылки на мои посты:

Остальные опубликованы в сообществе «Лайфхак»
Ссылка на сообщество MS, Libreoffice & Google docs

Как сравнить и выделить цветом ячейки Excel?

Выбирая инструменты на закладке: «ГЛАВНАЯ» в разделе «Стили» из выпадающего меню «Условное форматирование» нам доступна целая группа «Правила отбора первых и последних значений». Однако часто необходимо сравнить и выделить цветом ячейки в Excel, но ни один из вариантов готовых решений не соответствует нашим условиям. Например, в конструкции условия мы хотим использовать больше критериев или выполнять более сложные вычисления. Всегда можно выбрать последнюю опцию «Другие правила» она же является опцией «Создать правило». Условное форматирование позволяет использовать формулу для создания сложных критериев сравнения и отбора значений. Создавая свои пользовательские правила для условного форматирования с использованием различных формул мы себя ничем не ограничиваем.

Как сравнить столбцы в Excel и выделить цветом их ячейки?

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

Чтобы создать новое пользовательское правило делаем следующее:

1. Выделите диапазон ячеек D2:D12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. В появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматированных ячеек».
3. В поле ввода введите формулу:
4. Нажмите на кнопку «Формат» и в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите красный цвет для данного правила, а на вкладке «Шрифт» – белый цвет. После на всех открытых окнах жмем ОК.

Обратите внимание! В данной формуле мы используем только относительные ссылки на ячейки – это важно. Ведь нам нужно чтобы формула анализировала все ячейки выделенного диапазона.

Как выделить цветом ячейку в Excel по условию?

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

1. Не снимая выделения с диапазона D2:D12 снова выберите инструмент «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. Так же в появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматированных ячеек».
3. В поле ввода введите формулу:
4. Нажмите на кнопку «Формат» и в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите оранжевый цвет. На всех открытых окнах жмем ОК.

Мы видим, что получили не совсем ожидаемый результат, так как созданное новое правило всегда имеет высший приоритет по сравнению со старыми правилами условного форматирования в Excel. Необходимо снизить приоритет для нового правила. Чтобы проанализировать данную особенность наглядно и настроить соответствующим образом необходимо выбрать инструмент: ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».

Выберите новое оранжевое правило в появившемся окне «Диспетчер правил условного форматирования» и нажмите на кнопку «Вниз» (CTRL+стрелка вниз), как показано на рисунке:

Как видите последовательность правил очень важна если их много присвоено для одного и того же диапазона ячеек:

На первый взгляд может показаться что несколько правил могут форматировать одну и туже ячейку одновременно. В принципе это так, но при определенном условии, что все правила будут использовать разные типы форматирования. Например, правило 1 – изменяет шрифт, 2 – меняет заливку, 3 – добавляет границу, 4 – узор и т.д. Но если после выполнения любого правила, когда его условие выполнено, было проверено следующее правило для данной ячейки, тогда следует в окне диспетчера отметить галочкой в колонке «Остановить если истина»:

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

0;D2>C2)’ >

Этим ячейкам будет присвоен зеленый цвет и жмем везде ОК.

Примечание. В формуле можно использовать любые ссылки для текущего листа. В версии Excel 2010 можно ссылаться и на другие листы. А в Excel 2007 к другим листам можно обращаться только через имена диапазонов. Мы рекомендуем во всех версиях Excel ссылаться на другие листы через имена, так как это позволяет избежать множество ошибок при создании пользовательских правил для условного форматирования.