Ref-sgain.ru

Программы и сервисы
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как скопировать несколько выделений или диапазонов в Excel?

Координатное выделение ячейки в Excel

Примите мои приветствия, уважаемые читатели блога TutorExcel. Ru.

Как часто при работе с большими таблицами данных вы теряли из виду нужный столбец или строку?
Или, например, вы находите нужную ячейку и пытаетесь определить в каком столбце или строчке она находится?

Для примера возьмем достаточно большую таблицу, в которой пользователю будет «легко» потеряться:

Таблица с данными

Допустим нас интересует данные из Статья 8 в Период 6 (или любые другие).
Пытаемся глазами найти пересечение, но быстро с этим справиться сможет далеко не каждый, да и возрастает вероятность ошибиться и получить некорректные цифры.

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

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

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

Способ 1. Макрос

Давайте подумаем, как макрос должен сопоставить данные на листе.

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

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

Перейдем в редактор Visual Basic (быстрый переход с помощью комбинации клавиш Alt + F11).
Далее добавим в исходный код листа (в левой части панели выбираете нужный лист, правой кнопкой мышки щелкаете по нему и выбираете View Code) вставляем туда следующий код:

Вернемся в Excel. Запустите пересечение координат, включив опцию отображения в окне макроса (Alt + F8) и запустив Coordinate_Selection_On (чтобы отключить ее, запустите Coordinate_Selection_Off).

Читайте так же:
Как скопировать формулу ячейки только без форматирования в Excel?

Все готово (не забудьте сначала выполнить макрос Coordinate_Select_On):

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

Давайте перейдем к другому приложению.

Способ 2. Условное форматирование

Этот метод основан на двух основных принципах: условное форматирование (которое выделяет все ячейки в строке и столбце) и свойства функции CHASE (которая позволяет создавать правила форматирования).

Пойдем по порядку.
Выделим диапазон таблицы (в нашем примере это A1:Z35), для которого будем делать перекрестное выделение.
Далее в панели вкладок выбираем Главная ->Условное форматирование и нажимаем Создать правило:

Настройка формата отображения

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

После нажатия кнопки Формат вы можете настроить внешний вид выделенных данных (цвет заливки, шрифт).

Что нам дает функция MESSAGE?

Мы уже сталкивались с ней, когда, например, получали имя листа в виде формулы.
Данная функция возвращает различные свойства ссылки в зависимости от параметров введенных аргументов. Поэтому формулы ЯЧЕЙКА("строка") и ЯЧЕЙКА("столбец") вернут нам, соответственно, номер строки и столбца текущей ячейки.
Следовательно, введенная формула выделит все ячейки таблицы, где строка (или столбец) совпадают со строкой (или столбцом) текущей ячейки, в результате после форматирования и получится перекрестное выделение.

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

Какие в Excel 2010 есть способы выделения ячеек?

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

Чтобы выделить одну ячейку, щелкните по ней. Выделение прямоугольного диапазона ячеек (например, на рис. ) осуществляется щелчком мыши по верхней левой ячейке выделенной области (например, на рис. 1.5, а это ячейка А1). Затем, не отпуская кнопку мыши (указатель мыши должен быть в виде белого крестика), перетащите рамку в правую нижнюю ячейку, которую нужно выделить (на рис. 1.5 это ячейка C5). Отпустите кнопку мыши, чтобы зафиксировать рамку. Вы также можете создавать активные ячейки в разных местах таблицы, а не рядом друг с другом. Для этого щелкните по нужным ячейкам, удерживая клавишу Ctrl (рис. 1.5, б).

Читайте так же:
Как скрыть ноль процентов в Excel?

Рис. 1.5. Выделение прямоугольного диапазона ячеек (а) и ячеек в разных частях таблицы (б)

Рисунок 1.5. Выделение прямоугольного диапазона ячеек (a) и ячеек в разных частях таблицы (b)

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

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

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

Вы уже знаете, что каждая ячейка имеет свой собственный адрес. Каждый диапазон ячеек имеет свой адрес. Посмотрите, на рис. 1.5 выделен диапазон ячеек A1:C5 (левый верхний и правый нижний углы обозначены запятыми). Левая и правая ячейки строки будут указаны через двоеточие, например, A2:E2 (рис. 1.6, а). В диапазоне столбцов указываются верхняя и нижняя ячейки, например. B2:B6 (рис. 1.6, b). Зачем назначать адрес диапазону? Значения из диапазонов ячеек можно использовать в вычислениях. Формула будет основана на адресе диапазона.

Рис. 1.6. Выделенные диапазоны: ячейки строки (а) и ячейки столбца (б)

Рисунок 1.6: Выделенные отделения: ячейки в строке (a) и в столбце (b)

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

Теперь я хотел бы обратить ваше внимание на меню Файл (Рисунок 1.7). Рабочая книга Excel — это обычный файл. Как таковой, вы можете делать с этим файлом то же самое, что и с любым другим, а именно

  • Сохранить,
  • Напечатать,
  • Открыть,
  • Закрыть
Читайте так же:
Как скрыть ноль на оси диаграммы в Excel?

Рис. 1.7. Меню Файл

Рис. 1.7 : Меню Файл

Все эти возможности как раз и спрятаны в меню Файл. Подробнее мы рассмотрим его в разделе «Меню Файл».

Файл, созданный в Excel, в последних версиях Microsoft Office (2007 и 2010) хранится как файл XLSX. Значок такого файла в папке с файлами будет выглядеть так, как показано на рис. 1.8. Предыдущая версия Excel имела расширение XLS для своего программного файла.

Рис. 1.8. Значок файла Excel в папке компьютера

Рисунок 1.8. Значок файла Excel в папке компьютера

Итак. Старые файлы XLS можно открыть в Excel 2010. Не все функции в них работают, но их всегда можно открыть и прочитать. Но новые файлы (с расширением XLSX) не будут открываться в старых версиях Excel. Если вы собираетесь отправить кому-то свой файл, стоит поинтересоваться, откроется ли там файл XLSX. Чтобы открыть файл в любой версии программы, пересохраните файл. Формат файла XLS также можно просмотреть с помощью специальной программы, которую можно скачать с этой страницы.

Для этого в меню File, показанном на рис. 1.7, выберите Save As, а в появившемся окне Save Document в строке File Type укажите рабочую книгу Excel 97-2003 (рис. 1.9).

Рис. 1.9. Окно сохранения файла

Рисунок 1.9. Окно сохранения файла

В папке будет два файла: XLSX и XLS (рисунок 1.10).

Рис. 1.10. Файл слева совместим со старыми версиями Excel

Рисунок 1.10: Файл слева совместим со старыми версиями Excel.

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

Excel works!

menu

Excel выделение цветом ячеек по условиям. Условное форматирование

Нужно выделить повторяющиеся значения в столбце? Надо выбрать первые 5 максимальных ячеек? Необходимо сделать термальную шкалу для наглядности (цвет меняется в зависимости от увеличения/уменьшения значения ячеек)? В Excel выделение цветом ячеек по условиям можно сделать очень быстро и просто. За выделение цветом ячеек отвечает специальная функция «Условное форматирование». Настоятельно рекомендую! Подробнее читаем дальше:

Excel выделение цветом

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

Читайте так же:
Как сделать ячейку доступной только для чтения в Excel?

Условное форматирование, где найти?

Чтобы начать работу, найдите раздел Стили в главном меню ленты задач и выберите Условное форматирование.

Условное 1

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

Excel выделение цветом

Теперь подробнее о наиболее полезных:

Excel выделение цветом ячеек по условиям. Простые условия

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

Excel выделение цветом 0

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

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

Чтобы выделить все повторяющиеся значения, выберите соответствующее меню Recurring Values (повторяющиеся значения).

Excel выделение цветом

Снова появится окно форматирования. Настройте его по своему усмотрению. Например, вы можете выделить только то, что является уникальным. Значения и курсив (пользовательское форматирование)

В случаях, когда имена встречаются более чем в одном столбце, что делать, когда нужно найти повторяющиеся значения? Сделайте еще один столбец и объедините значения с помощью формулы =CREATE(), то есть у вас будет Иванов-Иванович в отдельной ячейке. Вы сможете легко определить повторяющиеся значения, если у вас есть такой столбец. Важно понимать, что если порядок слов отличается, Excel будет считать такие строки неповторяющимися (например, Автор этой статьи — Иванов Иван Иванович.

Excel. значения повторяются

Выделение цветом первых/последних значений. Опять же условное форматирование

Excel выделение цветом 1

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

Excel выделение цветом 2

Построение термальной диаграммы и гистограммы

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

Читайте так же:
Как синхронизировать все листы книги с тем же диапазоном, выбранным в Excel?

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

Рекомендую. Для презентаций и аналитики — гистограммы в ячейках и термальные диаграммы основа простой визуализации при помощи Excel.

Excel выделение цветом 3

Выделение цветом ячеек, содержащих определенный текст

Очень часто нужно найти ячейки, которые содержат определенный набор символов, можно конечно воспользоваться функцией = ПОИСК() , но проще и быстрее применить условное форматирование, пройдите — Правила отбора ячеек — Текст содержит

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

Excel выделение цветом. Фильтр по цвету

Помимо вышеперечисленных возможностей вы можете отфильтровать выделенные ячейки по цвету обычном фильтром. К моему удивлению об этом очень мало кто знает — видимо отголоски версии 2003 — там этой возможности не было.

Подробнее о фильтрах можно прочитать в этой статье.

Excel выделение цветом 4

Проверка условий форматирования

Чтобы проверить какие условные форматирования у Вас заданы, пройдите Главная — Условное форматирование — Управление правилами. Здесь вы сможете отредактировать уже заданные условия, диапазон применения, а также выбрать приоритет заданного форматирования (кто выше, тот главнее, изменить можно кнопками — стрелками).

Excel выделение цветом 5

Некорректный диапазон условного форматирования

Важно! Условное форматирование при неправильном использовании зачастую является причиной сильных тормозов Excel . Происходит задвоение форматирований, для примера если вы много раз копируете ячейки с выделением цветом. Тогда у вас появится множество условий с цветом. Я сам видел более 3 тысяч условий — тормозил файл безобразно. Также файл может тормозить, когда задан диапазон как на картинке выше, лучше, указывать A:A — для всего диапазона.

Чтобы узнать больше о тормозах Excel и их причинах, нажмите здесь. Эта статья помогла сотням людей 😉

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector