rss
  •  

Как найти на листе объединенные ячейки?

| Категория: Приемы и советы, Работа с данными ячеек |

5

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

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

Один из вариантов решения:

1) Нажать клавиши Ctrl+F.

2) Проверить, что в поле Найти [Find what] нет содержимого.

3) Нажать кнопку Параметры [Options>>], чтобы открыть дополнительные опции поиска.

4) Нажать кнопку Формат [Format] и на вкладке Выравнивание [Alignment] в группе Отображение [Text control] установить флажок объединение ячеек [Merge cells], нажать ОК.

5) В окне Найти и заменить [Find and Replace] нажать кнопку Найти все [Find All]. В окне будут показаны все объединенные ячейки на листе.

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

Создание зависимых списков с изменяемым источником

| Категория: Приемы и советы, Работа с данными ячеек, Формулы и функции |

14

С помощью Проверки данных [Data Validation] можно организовать ввод данных путем выбора из предлагаемого списка, значения которого зависят от другого списка. Причем, используя функцию СМЕЩ [OFFSET] можно создать вариант, когда добавленные исходные значения будут отображаться в списках для выбора нужных значений. Если значения списка зависят от выбранного значения из другого списка, то можно создать связанный (зависимый) список нужных значений. Это позволит в значительной степени избежать не корректных комбинаций вводимых значений.

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

Последовательность создания:

  • Выделить ячейку F2, где будет выбираться Европа.
    На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation] и на вкладке Параметры [Option], задать Условие проверки [Validation criteria] – Список [List], в качестве источника выделить ячейки B2 и C2

dv2.png

  • Ячейкам значений стран (данные в столбцах B и C) необходимо присвоить имена – Западная и Восточная, с возможностью автоматического определения диапазона ячеек по мере изменения количества значений в соответствующих столбцах:
    На вкладке Формулы [Formulas] выбрать Диспетчер имен [Name Manager] или нажать клавиши Ctrl+F3.
    Создать имена с использованием функции СМЕЩ:

dv3.png

  • Выделить ячейку F3, где будет выбираться Страна.
    На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation] и на вкладке Параметры [Option], задать Условие проверки [Validation criteria] – Список [List], в качестве источника ввести формулу:
    =ЕСЛИ($F$2=»Западная»;Западная;Восточная)
    [=IF ($F$2=»Западная»;Западная;Восточная)], где F2 – ячейка, которая содержит значение, выбираемого из первого списка.

dv4.png

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

dv5.png

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

Быстрая замена данных во всей книге

| Категория: Приемы и советы, Работа с данными ячеек |

6

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

Делать такие замены вручную слишком долго. Используя диалог Найти и Заменить – это можно сделать быстро:

  1. На вкладке Главная [Home] в группе Редактирование [Edit] раскрыть список на кнопке Найти и выделить [Find & Select] и выбрать команду Заменить [Replace] или нажать на клавиатуре клавиши Ctrl+H.
  2. В поле Найти [Find what] ввести данные, которую необходимо найти.
  3. В поле Заменить на [Replace with] ввести данные, которыми надо заменить искомые значения.
  4. Нажать кнопку Параметры>> [Options>>], чтобы задать дополнительные параметры поиска:replace2.png
    Искать [Within] – на листе [Sheet] или в книге [Workbook].
    Просматривать [Search] – по строкам [By Rows] или по столбцам [By Columns].
    Область поиска [Look in] – формулы [Formulas], значения [Values] или примечания [Comments].
    Учитывать регистр [Match case] – если флажок не установлен, то происходит поиск указанных данных без учета регистра.
    Ячейка целиком [Match entire cell contents] – точность поиска введенных данных, если флажок не установлен, но идет поиск типа «содержит».
    Формат [Format] – уточнение формата искомой информации, например, параметры шрифта или цветовое оформление.

  5. Выполнить замену данных:
    Заменить все [Replace All] – замена всех найденных данных
    Вручную: сперва нажать кнопку Найти далее [Find Next] – будет выделена ячейка с указанными данными для поиска. Чтобы произвести замену – нажать кнопку Заменить [Replace], чтобы перейти к поиску следующего нужного значения нажать кнопку Найти далее [Find Next] и т.д.
  6. Нажать кнопку Закрыть [Close] для закрытия окна поиска или клавишу Esc.