Как совместить ячейки в excel соответствующие одному значению в строке на разных листах

Как совместить ячейки в excel соответствующие одному значению в строке на разных листах

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

На втором листе создана ячейка со списком значений из строки "номер заказа" первого листа.

1) Как сделать, чтобы ниже (в других ячейках второго листа) выводились значения из соотв. заданных ячеек первого листа только из той колонки, в которой находится выбранный в ячейке "номер заказа" номер?
Т.е. на втором листе в ячейке со списком значений выбрали заказ 35 — и в соотв. ячейках отображается "дата поступления", "адрес", "сумма исходящих расходов" и т.п. только 35 заказа (значения берутся из соотв строк первого листа).

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

Что-то никак не получается приделать Вашу формулу ячеек к полной версии таблицы (больше ячеек с данными, в приложении). Думал получится понять систему и перенять по аналогии на большую таблицу, но похоже переоценил свои силы. Неудачная попытка — в приложении. Пытался сделать два листа — "Информация клиенту" и "Информация подрядчику", чтоб каждый лист состоял из текста и перенятых данных из избранных ячеек листа "Заказы", актуальных для клиента / подрядчика соотв (в табличном виде).
Пробовал взять данные без изменений из Вашего примера — тоже не сработало.
Выбор "Все заказы клиента"/"Все активные заказы клиента" (т.е. все заказы со значением "Нет" в поле "Закончен" в листе "Заказы") и выбор из номеров заказов выбранного клиента — тоже почему-то сбился.

Часть присвоения номеров колонкам с заказами — работает (цифры в первой строке — все верные , меняются в соответствии с правилом), проблема похоже в индексации.

В исходном примере (заказ1.xls) в листе "Данные" в ячейке C4, показывается верный номер первого заказа, подходящего под правила фильтрации, заданные в листе "Информация клиенту" (в ячейках A2 & B2) — формула:
=ИНДЕКС(Заказы!$3:$3;НАИМЕНЬШИЙ(ЕСЛИ(‘Информация клиенту’!$A$2=Заказы!$2:$2;СТОЛБЕЦ($B:$IV)-1);СТРОКА(1:1)))

B моей попытке перенять систему (ZakazyLite.xlsx) в ячейке С4 стоит формула:
=ИНДЕКС(Заказы!$9:$9;НАИМЕНЬШИЙ(ЕСЛИ(‘Инфо клиенту’!$A$2=Заказы!$8:$8;СТОЛБЕЦ($ B :$IV)-1);СТРОКА(1:1)))

и прописывается значение "4" вместо номера первого заказа с индексом [1] в первой строке его колонки(книга "Заказы") — т.е. "2" (заказ с о значением не= "Yes" в строке "Закончен" и выбранного клиента "А" в листе "Инфо клиенту" (А2)).

Т.е. первый шаг — присваивание в ячейках первой строки цифр колонкам заказов (в листе "Заказы") — настроил и он работает:
0-получают заказы выбранного клиента (лист "Инфо-клиенту" — A2) со значением "Да" в строке "Закончен" (лист "Заказы"), а номера с 1 — до . — получают все заказы выбранного клиента (в листе "Инфо-клиенту" — A2) с другим, чем "Да", значением в соотв. ячейке (т. е. пусто) в строке "Закончен".

Как объединить ячейки с одинаковым значением в Excel

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

Как выделить одинаковые ячейки группами

Допустим мы имеем список поставщиков:

Список поставщиков.

Перед тем как найти повторяющиеся ячейки в Excel, отсортируем поставщиков по их идентификатору. Переходим в ячейку A2 и выбираем на закладке «ДАННЫЕ» в разделе «Сортировка и фильтр» инструмент «Сортировка от А до Я».

Сортировка от А до Я.

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

  1. Выделите диапазон A2:A11 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:». Создать правило.
  2. В поле для ввода формулы вводим следующе значение: =$A2<>$A3 Формула.
  3. Щелкните на кнопку «Формат», на вкладке «Граница» следует задать оформление для нижних границ строк. И ОК.

Граница.

В результате получаем эффект как отображено на рисунке.

Готово.

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

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

Полезный совет! Если нужно разграничить данные не по первому столбцу таблицы, по любому другому, тогда соответственно отсортируйте и просто укажите адрес столбца. Например, разграничим по повторяющимся датам, а не по поставщикам. Для этого сначала сортируем данные по датам, а потом используем условное форматирование немного изменив формулу: =$C2<>$C3

По датам.

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

Ссылка на основную публикацию