Выделение ячеек в Microsoft Excel

20 секретов Excel, которые помогут упростить работу

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

Выделение всех ячеек одним кликом

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

Открытие нескольких файлов одновременно

Вместо того чтобы открывать каждый файл Excel по отдельности, их можно открыть вместе. Для этого выделите файлы, которые нужно открыть, и нажмите Enter.

Перемещение по файлам Excel

Когда у вас открыто несколько книг в Excel, между ними можно легко перемещаться с помощью комбинации клавиш Ctrl + Tab. Эта функция также доступна по всей системе Windows, и ее можно использовать во многих приложениях. К примеру, для переключения вкладок в браузере.

Добавление новых кнопок на панель быстрого доступа

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

Перейдите в меню «Файл» ⇒ «Параметры» ⇒ «Панель быстрого доступа». Теперь можно выбрать любые кнопки, которые вам нужны.

Диагональная линия в ячейках

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

Добавление в таблицу пустых строк или столбцов

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

Скоростное копирование и перемещение информации

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

Быстрое удаление пустых ячеек

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

Расширенный поиск

Нажав Ctrl + F, мы попадаем в меню поиска, с помощью которого можно искать любые данные в Excel. Однако его функциональность можно расширить, используя символы «?» и «*». Знак вопроса отвечает за один неизвестный символ, а астериск — за несколько. Их стоит использовать, если вы не уверены, как выглядит искомый запрос.

Если же вам нужно найти вопросительный знак или астериск и вы не хотите, чтобы вместо них Excel искал неизвестный символ, то поставьте перед ними «~».

Копирование уникальных записей

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

Создание выборки

Если вы делаете опрос, в котором могут участвовать только мужчины от 19 до 60, вы легко можете создать подобную выборку с помощью Excel. Перейдите в пункт меню «Данные» ⇒ «Проверка данных» и выберите необходимый диапазон или другое условие. Вводя информацию, которая не подходит под это условие, пользователи будут получать сообщение, что информация неверна.

Быстрая навигация с помощью Ctrl и стрелки

Нажимая Ctrl + стрелка, можно перемещаться в крайние точки листа. К примеру, Ctrl + ⇓ перенесет курсор в нижнюю часть листа.

Транспонирование информации из столбца в строку

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

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

Как скрывать информацию в Excel

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

Объединение текста с помощью «&»

Если вам нужно объединить текст из нескольких ячеек в одну, необязательно использовать сложные формулы. Достаточно выбрать ячейку, в которой будет соединен текст, нажать «=» и последовательно выбрать ячейки, ставя перед каждой символ «&».

Изменение регистра букв

С помощью определенных формул можно менять регистр всей текстовой информации в Excel. Функция «ПРОПИСН» делает все буквы прописными, а «СТРОЧН» — строчными. «ПРОПНАЧ» делает прописной только первую букву в каждом слове.

Внесение информации с нулями в начале

Если вы введете в Excel число 000356, то программа автоматически превратит его в 356. Если вы хотите оставить нули в начале, поставьте перед числом апостроф «’».

Ускорение ввода сложных слов

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

Больше информации

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

Переименование листа с помощью двойного клика

Это самый простой способ переименовать лист. Просто кликните по нему два раза левой кнопкой мыши и введите новое название.

Источник: //Lifehacker.ru/20-sekretov-excel/

Выделение ячеек

Многие пользователи Excel полагают, что единственный способ выделить диапазон ячеек — обвести нужный участок с помощью мыши. Действительно, такое выделение — один из вариантов, но обычно он не самый эффективный. Гораздо лучше выделять диапазоны с помощью клавиатуры. [1]

Рис. 1. Диапазон ячеек

Скачать заметку в формате Word или pdf, примеры в формате Excel

Выделение диапазона с помощью Shift и клавиш управления курсором

Простейший способ выделить диапазон – нажать и удерживать клавишу Shift, а затем выделять ячейки с помощью стрелок. При выделении более крупных областей можно удерживать Shift и нажимать Page Up или Page Down, чтобы сразу охватывать большие диапазоны.

Кроме того, можно воспользоваться клавишей End, чтобы быстро расширить выделение до последней непустой ячейки в строке или столбце. Например, чтобы выделить диапазон ВЗ:В14 с клавиатуры (рис. 1), выберите ячейку ВЗ, а потом удерживая нажатой клавишу Shift, нажмите End и ↓.

Не отпуская Shift, продолжите выделение прямоугольного диапазона, нажмите End и →. Выделенным окажется диапазон В3:Е14.

Выделение текущей области

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

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

Выделение диапазона методом Shift+щелчок

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

Выделение несмежных диапазонов

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

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

Работая с клавиатурой, выделите первый диапазон так, как было описано выше (например, Ctrl+A). Затем нажмите Shift+F8. Вы перейдете в режим выделения (рис. 2). Продолжите выделять нужные диапазоны.

Закончив выделение, снова нажмите Shift+F8, чтобы перейти в обычный режим.

Рис. 2. Множественное выделение несмежных диапазонов

Выделение целых строк

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

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

Чтобы выделить несколько несмежных строк, удерживайте нажатой Ctrl и щелкайте на номерах строк, которые хотите включить в выборку.

Выделение целых столбцов

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

Можете также выделить любую ячейку в первом (или последнем) столбце и нажать Ctrl+Пробел для выделения всего этого столбца. После этого нажмите Shift и с помощью стрелок →← расширьте область выделения вправо или влево.

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

Выделение многолистовых диапазонов

Можно выделять не только «двухмерные» диапазоны на одном рабочем листе, но и диапазоны, охватывающие ячейки с нескольких листов. Такие диапазоны можно считать «трехмерными» (рис. 3; см. также Excel-файл).

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

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

1. Выделите диапазон на одном листе.
2. Выделите листы, которые хотите включить в диапазон.

Рис. 3. Выделение нескольких листов

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

Чтобы выделить отдельные листы, нажмите Ctrl и щелкните на названии каждого листа, который хотите выделить. При таком выделении фон названий листов, которые вы выбрали, становится белым, a Excel отображает в строке заголовка слово [Группа].

Закончив работу с многолистовым диапазоном, щелкните на названии любого листа, чтобы выйти из режима Группа.

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 161–164.

Источник: //baguzin.ru/wp/vydelenie-yacheek/

Выделение ячеек по заданным критериям

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

В качестве примерарассмотрим поиск ячеек, содержащихформулы.

Для этого выделитевесь рабочий лист и выберите команду Правка | Перейтиили нажмите клавишу F5,чтобы открыть диалоговое окно Переход.Щёлкните в этом диалоговом окне покнопке Выделить.В результате появится диалоговое окноВыделениегруппы ячеекдля указания критерия выделения ячеек(рис. 9.1).

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

Рис. 9.1. Диалоговоеокно, позволяющее задать критерийвыделения ячеек

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

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

Опции для уточнениякритерия поиска ячеек Таблица9

Опция	Назначение
Числа	Выделение ячеек, в которых в результате вычисления формул получены числовые значения
Текст	Выделение ячеек, в которых в результате вычисления формул получен текст
Логические	Выделение ячеек, в которых в результате вычисления формул получено логическое значение
Ошибки	Выделение ячеек, в которых в результате вычисления формул появились сообщения об ошибках

Редактирование содержимого ячеек

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

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

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

Соответственно можно ввести новыезначения с тем же самым форматом. Чтобыпосмотреть на все возможные способыочистки, выберите команду Правка| Очистить.

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

Перемещение и копирование ячеек

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

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

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

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

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

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

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

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

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

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

Источник: //StudFiles.net/preview/1874473/page:7/

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

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

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

Для начала, на ленте задач в главном меню найдите раздел Стили и нажмите на кнопку Условное форматирование,

При нажатии откроется меню, с разными вариантами этого редактирования. Как вы видите возможностей здесь действительно много.

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

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

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

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

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

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

Что делать если необходимо найти повторения по двум и более столбцам, например когда ФИО в разных столбцах? Сделайте еще один столбец и объедините значения формулой =СЦЕПИТЬ(), т.е.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Важно! Условное форматирование при неправильном использовании зачастую является причиной сильных тормозов Excel. Происходит это если вы много раз копируете ячейки с выделением цветом.

Тогда у вас может быть множество условий с цветом. Я сам видело более 3 тысяч условий — тормозил файл безбожно. Подробнее о тормозах Excel и их причинах читайте здесь.

Эта статья помогла не одной сотне людей 😉

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

Поделитесь нашей статьей в ваших соцсетях:

Источник: //excelworks.ru/2015/10/05/vyidelenie-tsvetom/

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Dim Flag As Boolean 'Глобальная переменная для опции Sub Coordinate_Selection_On() 'Включение опции отображения Flag = True End Sub Sub Coordinate_Selection_Off() 'Отключение опции отображения Flag = False End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub 'Если выделено более 1 ячейки – выходим из макроса If Flag = False Then Exit Sub 'Если опция отключена – выходим из макроса Application.ScreenUpdating = False Union(Target.EntireColumn, Target.EntireRow).Select 'Выделение диапазона Target.Activate Application.ScreenUpdating = True End Sub

Dim Flag As Boolean   'Глобальная переменная для опцииSub Coordinate_Selection_On()   'Включение опции отображенияSub Coordinate_Selection_Off()   'Отключение опции отображенияPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)    If Target.Cells.Count > 1 Then Exit Sub   'Если выделено более 1 ячейки – выходим из макроса    If Flag = False Then Exit Sub   'Если опция отключена – выходим из макроса    Application.ScreenUpdating = False     Union(Target.EntireColumn, Target.EntireRow).Select   'Выделение диапазона    Application.ScreenUpdating = True

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

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

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

Теперь перейдем к альтернативной реализации.

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

Следующий способ базируется на 2 основных принципах: условном форматировании (которое будет подсвечивать все ячейки в строке и столбце) и свойствах функции ЯЧЕЙКА (которая позволит нам составить правило для форматирования).

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

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

=ИЛИ(ЯЧЕЙКА(“строка”)=СТРОКА(B2);ЯЧЕЙКА(“столбец”)=СТОЛБЕЦ(B2))

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

Что нам дает функция ЯЧЕЙКА?

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

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

Источник: //tutorexcel.ru/priemy/koordinatnoe-vydelenie-yachejki-v-excel/

Координатное выделение

18176 11.10.2012 Скачать пример

У вас большой монитор, но таблицы, с которыми вы работаете – еще больше.

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

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

А если при движении активной ячейки по листу будет подсвечиваться текущая строка и столбец? Своего рода координатное выделение примерно такого вида:

Поудобнее, чем линейка, правда?

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

Способ 1. Очевидный. Макрос, выделяющий текущую строку и столбец

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

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

Это приводит нас к трем макросам (выделения, включения и выключения), которые нужно будет добавить в модуль листа.

Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:
Dim Coord_Selection As Boolean 'глобальная переменная для вкл/выкл выделения Sub Selection_On() 'макрос включения выделения Coord_Selection = True End Sub Sub Selection_Off() 'макрос выключения выделения Coord_Selection = False End Sub 'основная процедура, выполняющая выделение Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim WorkRange As Range If Target.Cells.Count > 1 Then Exit Sub 'если выделено больше 1 ячейки – выходим If Coord_Selection = False Then Exit Sub 'если выделение выключено – выходим Application.ScreenUpdating = False Set WorkRange = Range(“A6:N300”) 'адрес рабочего диапазона, в пределах которого видно выделение Intersect(WorkRange, Union(Target.EntireColumn, Target.EntireRow)).Select 'формируем крестообразный диапазон и выделяем Target.Activate End Sub

Измените адрес рабочего диапазона на свой – именно в пределах этого диапазона и будет работать наше выделение. Затем закройте редактор Visual Basic и вернитесь в Excel.

Нажмите сочетание клавиш ALT+F8, чтобы открыть окно со списком доступных макросов. Макрос Selection_On, как нетрудно догадаться, включает координатное выделение на текущем листе, а макрос Selection_Off – выключает его. В этом же окне, нажав кнопку Параметры (Options) можно назначить этим макросам сочетания клавиш для удобного запуска.

Плюсы этого способа:

• относительная простота реализации
• выделение – операция безобидная и никак не изменяет содержимое или форматирование ячеек листа, все остается как есть

Минусы этого способа:

• такое выделение некорректно работает в том случае, если на листе есть объединенные ячейки – выделяются сразу все строки и столбцы, входящие в объединение
• если случайно нажать клавишу Delete, то очистится не только активная ячейка, а вся выделенная область, т.е. удалятся данные из всей строки и столбца

Способ 2. Оригинальный. Функция ЯЧЕЙКА + Условное форматирование

Этот способ хотя и имеет пару недостатков, мне представляется весьма изящным. Реализовать что-либо, используя только встроенные средства Excel, минимально влезая в программирование на VBA – высший пилотаж 😉

Способ основан на использовании функции ЯЧЕЙКА (CELL), которая может выдавать массу различной информации по заданной ячейке – высоту, ширину, номер строки-столбца, числовой формат и т.д.. Эта функция имеет два аргумента:

• кодовое слово для параметра, например “столбец” или “строка”
• адрес ячейки, для которой мы хотим определить значение этого параметра

Хитрость в том, что второй аргумент не является обязательным. Если он не указан, то берется текущая активная ячейка.

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

1. Выделяем нашу таблицу, т.е. те ячейки, в которых в будущем должно отображаться координатное выделение.
2. В Excel 2003 и более старших версиях открываем меню Формат – Условное форматирование – Формула (Format – Conditional Formatting – Formula). В Excel 2007 и новее – жмем на вкладке (Home) кнопку Условное форматирование – Создать правило (Conditional Formatting – Create Rule) и выбираем тип правила Использовать формулу для определения форматируемых ячеек (Use formula)
3. Вводим формулу для нашего координатного выделения: =ИЛИ(ЯЧЕЙКА(“строка”)=СТРОКА(A2);ЯЧЕЙКА(“столбец”)=СТОЛБЕЦ(A2))

   =OR(CELL(“row”)=ROW(A1),CELL(“column”)=COLUMN(A1)) Эта формула проверяет, не совпадает ли номер столбца каждой ячейки в таблице с номером столбца текущей ячейки. Аналогично со столбцами.

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

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

4. Нажмите кнопку Формат (Format) и задайте цвет заливки.

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

Поэтому добавим в модуль листа простой макрос, который будет это делать. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic.

Скопируйте в него этот текст этого простого макроса:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveCell.Calculate End Sub

Теперь при изменении выделения будет запускаться процесс пересчета формулы с функцией ЯЧЕЙКА в условном форматировании и заливаться текущая строка и столбец.

Плюсы этого способа:

• Условное форматирование не нарушает пользовательское форматирование таблицы
• Этот вариант выделения корректно работает с объединенными ячейками.
• Нет риска удалить целую строку и столбец с данными при случайном нажатии Delete.
• Макросы используются минимально

Минусы этого способа:

• Формулу для условного форматирования надо вводить вручную.
• Нет быстрого способа включить-выключить такое форматирование – оно включено всегда, пока не будет удалено правило.

Способ 3. Оптимальный. Условное форматирование + макросы

Золотая середина. Используем механизм отслеживания выделения на листе при помощи макросов из способа-1 и добавим к нему безопасное выделение цветом с помощью условного форматирования из способа-2.

Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:

Dim Coord_Selection As Boolean Sub Selection_On() Coord_Selection = True End Sub Sub Selection_Off() Coord_Selection = False End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim WorkRange As Range, CrossRange As Range Set WorkRange = Range(“A7:N300″) 'адрес рабочего диапазона с таблицей If Target.Count > 1 Then Exit Sub If Coord_Selection = False Then WorkRange.FormatConditions.Delete Exit Sub End If Application.ScreenUpdating = False If Not Intersect(Target, WorkRange) Is Nothing Then Set CrossRange = Intersect(WorkRange, Union(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add Type:=xlExpression, Formula1:=”=1” CrossRange.FormatConditions(1).Interior.ColorIndex = 33 Target.FormatConditions.Delete End If End Sub

Не забудьте изменить адрес рабочего диапазона на адрес своей таблицы. Закройте редактор Visual Basic и вернитесь в Excel. Чтобы использовать добавленные макросы, нажмите сочетание клавиш ALT+F8  и действуйте аналогично способу 1. 

Способ 4. Красивый. Надстройка FollowCellPointer

Excel MVP Jan Karel Pieterse родом из Нидерландов раздает у себя на сайте бесплатную надстройку FollowCellPointer(36Кб), которая решает ту же задачу, отрисовывая с помощью макросов графические линии-стрелки для подсветки текущей строки и столбца:

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

• в Excel 2003 и старше – через меню Сервис – Надстройки – Обзор (Tools – Add-Ins – Browse)
• в Excel 2007 и новее – через Файл – Параметры – Надстройки – Перейти – Обзор (File – Excel Options – Add-Ins – Go to – Browse)

Ссылки по теме

• Что такое макросы, куда вставлять код макроса на Visual Basic

Источник: //www.planetaexcel.ru/techniques/9/58/

Работа с ячейками в Excel

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

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

Рабочая книга Excel

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

Изображение № 1. Основные обозначения.

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

Объединение ячеек в Эксель

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

Форматирование объединенных областей в Excel:

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

Изображение № 2. Объединение ячеек.

1. Поиск нужной кнопки. В меню навигации, на вкладке «», отыскать клавишу, помеченную на скриншоте № 2. и вывести выпадающий список. Выбрать компонент «Объединить и поместить в центре». Если соответствующая клавиша недоступна, то пользователям необходимо закрыть режим редактирования клавишей ВВОД (Enter).
2. Замечания. Для изменения положения текста в объеденном блоке используются свойства выравнивания, на вкладке «».

Разделение ячеек Excel

Процедура незатейлива и полностью повторяет предыдущий пункт:

1. Выбор объединенного блока (другие разделить не получится).
2. При выделении загорится клавиша объединения (помечена на скриншоте № 2), после нажатия на неё ячейки высвободятся и получат отдельный физический адрес. Столбцы и ячейки пересчитаются в автоматическом режиме.

Поиск ячейки Excel

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

Следуйте инструкции:

1. Вкладка «», область «Редактирование» (в самом правом углу). Клавиша «Найти и выделить».
2. При нажатии на клавишу «Найти» раскроется окно ввода информации.

   Но перед этим немного настроек – «Параметры», затем «Формат», переход к группе «Выравнивание» и установка галки напротив поиска объединенных ячеек.

3. Информация выведется в специальном окошке.
4. Замечание: Если область «Формат» скрыта, значит, не нажаты «Параметры».

   Для поиска всех объединенных ячеек применяется функций «Найти все». Подробнее на скриншоте № 3.

Изображение № 3. Поиск объединенных ячеек

Содержимое ячеек Эксель

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

1. Ввод. Выделение интересующей области. Внесение данных.
2. Удаление. Стандартный вариант – применение клавиш Delete и Backspace. Способ для опытных пользователей – клавиша ластика в панели «Редактирование», там, где расположена функция «Найти».
3. Копирование. Лучший способ работать с данными, вооружиться «горячими клавишами». Выделенные блоки легко размножать, сначала зажав сочетание CTRL+C (копирование), а затем CTRL + V (вставка). Метод быстрый, работает практически во всех текстовых редакторах и браузерах. При неправильных действиях можно воспользоваться CTRL + Z, вернувшись на шаг назад.
4. Вырезание. И снова выделение, затем CTRL + X, а после CTRL + V (уже знакомая вставка). Безотказная комбинация.
5. Формат. Выделенные ячейки легко изменять и внешне, и внутренне. Целый список возможных команд открывается при нажатии правой кнопкой мыши.

Арифметические операции в Excel

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

Прежде всего, стоит разобраться в обозначениях:

• «+» – сложение.
• «-» – вычитание.
• «*» – умножение.
• «/» – деление.
• «» – возведение числа в степень.
• «%» – процент.

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

Пример: =7+6

После нажатия клавиши ВВОД (Enter), данные подсчитываются, и выводится результат. Если число после проведения арифметической операции получилось не целым, а с громадным количеством цифр после запятой, то можно воспользоваться настройкой и уменьшить разрядность. Для этого достаточно на «Главной» в разделе «Число» найти указанную кнопку.

Формулы Excel

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

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

Для получения «Итого», к примеру, достаточно сложить данные по каждому наименованию за первую и вторую неделю. Вариант несложный, можно переписать несколько значений вручную. А как быть с полем «Сумма»? Тоже все руками? Как же систематизировать данные?

Изображение № 4. Работа с формулами

Все логично и невероятно удобно. Выбор поле для подсчета. Далее значок Fx (Изображение № 4, Пункт 2), определение подходящей для совершения арифметического действия формулы. (Все подписано и совершенно наглядно).

После выбора СУММ выделение диапазона значений. Подтверждение действия кнопкой ВВОД. Лучший способ разобраться во всех функциях и набить руку – попрактиковаться.

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

Ошибки в Excel

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

#Н/Д – данные не определены, нарушена последовательность ввода аргументов данных;

#ССЫЛКА! – адрес столбца или строки неверен;

#ПУСТО! – неправильное построение арифметической операции;

#ЧИСЛО! – чересчур маленькое или большое для вывода число;

#ЗНАЧ! – неподдерживаемый тип данных, если в одной ячейке текст, а в другой цифры;

#ДЕЛ/0! – недопустимая попытка деления на ноль;

#ИМЯ?  – нераспознаваемый адрес или название функции;

Горячие клавиши Excel

CTRL + (стрелка на клавиатуре) – выбор всех ячеек в столбце или строке;

CTRL + SHIFT + «+» – вставка текущего времени;

CTRL + «;» – указание текущей даты. Автоматически форматируется по правилам Excel;

CTRL + A – выделение документа целиком или текущей заполненной области.

И уже изученные – CTRL + C (копировать); CTRL + V (вставить); CTRL + Z (вернуться на шаг назад).

Источник: //pclegko.ru/microsoft-office/rabota-v-excel-s-yacheykami.html