В экселе выделить все: Выделение всех ячеек на листе

Выделение дубликатов цветом

— Простите, вы не видели тут моего близнеца?
— Вы уже спрашивали.

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

Способ 1. Повторяющиеся ячейки

Выделяем все ячейки с данными и на вкладке Главная (Home) жмем кнопку Условное форматирование (Conditional Formatting), затем выбираем Правила выделения ячеек — Повторяющиеся значения (Highlight Cell Rules — Duplicate Values):

В появившемся затем окне можно задать желаемое форматирование (заливку, цвет шрифта и т.д.)

Способ 2. Выделение всей строки

Если хочется выделить цветом не одиночные ячейки, а сразу строки целиком, то придется создавать правило условного форматирования с формулой. Для этого выделяем все данные в таблице и выбираем Главная — Условное форматирование — Создать правило — Использовать формулу для выделения форматируемых ячеек (Home — Conditional formatting — Create rule — Use a formula to determine which cells to format), а затем вводим формулу:

=СЧЁТЕСЛИ($A$2:$A$20;$A2)>1

=COUNTIF($A$2:$A$20;$A2)>1

где

• $A$2:$A$20 — столбец в данных, в котором мы проверяем уникальность
  
• $A2 — ссылка на первую ячейку столбца
  

Способ 3. Нет ключевого столбца

Усложним задачу. Допустим, нам нужно искать и подсвечивать повторы не по одному столбцу, а по нескольким. Например, имеется вот такая таблица с ФИО в трех колонках:

Задача все та же — подсветить совпадающие ФИО, имея ввиду совпадение сразу по всем трем столбцам — имени, фамилии и отчества одновременно.

Самым простым решением будет, конечно, добавить дополнительный служебный столбец (его потом можно скрыть) с текстовой функцией СЦЕПИТЬ (CONCATENATE), чтобы собрать ФИО в одну ячейку:

Имея такой столбец мы, фактически, сводим задачу к предыдущему способу.

Если же хочется всё решить без дополнительного столбца, то формула для условного форматирования будет посложнее:

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

 

Горячие клавиши MS Excel — Блог: Управление проектами

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

Сочетания клавиш с использованием клавиши CTRL

Клавиша	Описание
CTRL+PAGE DOWN	Переключение между вкладками листа слева направо.	Применение экспоненциального числового формата с двумя десятичными знаками.
CTRL+SHIFT+#	Применение формата даты с указанием дня, месяца и года.
CTRL+SHIFT+@	Применение формата времени с отображением часов и минут и индексами AM или PM.
CTRL+SHIFT+!	Применение числового формата с двумя десятичными знаками, разделителем разрядов и знаком минус (-) для отрицательных значений.
CTRL+SHIFT+*	Выделение текущей области вокруг активной ячейки (область данных, ограниченная пустыми строками и пустыми столбцами).В сводной таблице выделяется весь отчет сводной таблицы.
CTRL+SHIFT+:	Вставка текущего времени.
CTRL+SHIFT+”	Копирование содержимого верхней ячейки в текущую ячейку или в строку формул.
CTRL+SHIFT+знак плюс (+)	Отображение диалогового окна Добавление ячеек для вставки пустых ячеек.
CTRL+знак минус (-)	Отображение диалогового окна Удаление ячеек для удаления выделенных ячеек.
CTRL+;	Вставка текущей даты.
CTRL+`	Переключение между выводом в листе значений ячеек и формул.
CTRL+’	Копирование формулы верхней ячейки в текущую ячейку или в строку формул.
CTRL+1	Отображение диалогового окна Формат ячеек.
CTRL+2	Применение или удаление полужирного начертания.
CTRL+3	Применение или удаление курсивного начертания.
CTRL+4	Применение или удаление подчеркивания.
CTRL+5	Зачеркивание текста или удаление зачеркивания.
CTRL+6	Переключение между отображением и скрытием объектов.
CTRL+8	Отображение или скрытие знаков структуры.
CTRL+9	Скрытие выделенных строк.
CTRL+0	Скрытие выделенных столбцов.
CTRL+A	Выделение листа целиком.Если лист содержит данные, сочетание клавиш CTRL+A выделяет текущую область. Повторное нажатие клавиш CTRL+A приведет к выделению всего листа.Если курсор расположен справа от имени функции в формуле, отобразится диалоговое окно Аргументы функции.Если курсор расположен справа от имени функции в формуле, сочетание клавиш CTRL+SHIFT+A вставляет имена аргументов и скобки.
CTRL+B	Применение или удаление полужирного начертания.
CTRL+C	Копирование выделенных ячеек.
CTRL+D	Использование команды Заполнить вниз , чтобы копировать содержимое и формат верхней ячейки выделенного диапазона во все нижние ячейки.
CTRL+E	Вызов функции мгновенное заполнение для автоматического определения узора в соседних столбцах и заливки текущего столбца.
CTRL+F	Отображение диалогового окна Найти и заменить с выбранной вкладкой Найти.Сочетание клавиш SHIFT+F5 также выводит на экран эту вкладку, а SHIFT+F4 повторяет последнее действие на вкладке Найти.Сочетание клавиш CTRL+SHIFT+F выводит на экран диалоговое окно Формат ячеек с выбранной вкладкой Шрифт.
CTRL+G	Отображение диалогового окна Переход.Клавиша F5 также выводит на экран это диалоговое окно.
CTRL+H	Отображение диалогового окна Найти и заменить с выбранной вкладкой Заменить.
CTRL+I	Применение или удаление курсивного начертания.
CTRL+K	Отображение диалогового окна Вставка гиперссылки для новых гиперссылок или Изменение гиперссылки для существующей выбранной гиперссылки.
CTRL+L	Отображение диалогового окна Создание таблицы.
CTRL+N	Создание новой пустой книги.
CTRL+O	Отображение диалогового окна Открытие документа для открытия или поиска файла.Сочетание клавиш CTRL+SHIFT+O выделяет все ячейки, содержащие комментарии.
CTRL+P	Отображение вкладки Печать в Представление Microsoft Office Backstage.Сочетание клавиш CTRL+SHIFT+F выводит на экран диалоговое окно Формат ячеек с выбранной вкладкой Шрифт.
CTRL+Q	Отображение параметров экспресс-анализа для данных, когда имеются ячейки, в которых выделены эти данные.
CTRL+R	Использование команды Заполнить вправо, чтобы копировать содержимое и формат крайней левой ячейки выделенного диапазона во все расположенные правее ячейки.
CTRL+S	Сохранение активного файла с текущим именем в текущем расположении и в существующем формате.
CTRL+T	Отображение диалогового окна Создание таблицы.
CTRL+U	Применение или удаление подчеркивания.Сочетание клавиш CTRL+SHIFT+U разворачивает и сворачивает строку формул.
CTRL+V	Вставка содержимого буфера обмена в точке вставки и замена выделенного фрагмента. Функционирует только при наличии в буфере обмена объекта, текста или содержимого ячеек.При нажатии клавиш CTRL+ALT+V открывается диалоговое окно Специальная вставка. Оно доступно только после копирования или вырезания объекта, текста или содержимого ячейки на листе или в другой программе.
CTRL+W	Закрытие окна выбранной книги.
CTRL+X	Удаление содержимого выделенных ячеек.
CTRL+Y	Повторение последней команды или действия, если это возможно.
CTRL+Z	Использование команды Отменить для отмены последней команды или удаления последней введенной записи.

Совет: Сочетаниям клавиш CTRL+J и CTRL+M в данный момент не назначены какие-либо действия.

Функциональные клавиши

Клавиша	Описание
F1	Отображение области задач Справка Excel.Сочетание клавиш CTRL+F1 отображает или скрывает ленту.Сочетание клавиш ALT+F1 создает встроенную диаграмму на основе данных из текущей области.Сочетание клавиш ALT+SHIFT+F1 добавляет в книгу новый лист.
F2	Изменение активной ячейки и помещение курсора в конец ее содержимого. Нажатие этой клавиши также перемещает курсор в строку формул, если режим редактирования в ячейке выключен.Сочетание клавиш SHIFT+F2 добавляет или изменяет комментарии к ячейке.Сочетание клавиш CTRL+F2 отображает область предварительного просмотра на вкладке  Печать в Представление Backstage.
F3	Отображение диалогового окна Вставка имени. Доступно, только если в книге определены имена (вкладка Формулы, группа Определенные имена, Присвоить имя).Сочетание клавиш SHIFT+F3 выводит на экран диалоговое окно Вставка функции.
F4	Повторение последней команды или действия, если это возможно.Если в формуле выделена ссылка на ячейку или диапазон, при нажатии F4 выполняется перебор всех комбинаций абсолютных и относительных ссылок.Сочетание клавиш CTRL+F4 закрывает окно выбранной книги.Сочетание клавиш ALT+F4 закрывает Excel.
F5	Отображение диалогового окна Переход.Сочетание клавиш CTRL+F5 восстанавливает размер выбранного окна книги.
F6	Переключение точки ввода между листом, лентой, областью задач и элементами управления масштабом. В листах, которые были разделены (меню Вид, Управление этим окном, Закрепить области, команда Разделить окно), при переходе между областью ленты и другими областями с помощью клавиши F6 в переключении участвуют и разделенные области.Сочетание клавиш SHIFT+F6 переключает точку ввода между листом, элементами управления масштабом, областью задач и лентой.Если открыто несколько книг, сочетание клавиш CTRL+F6 переключает точку ввода в окно следующей книги.
F7	Отображение диалогового окна Орфография для проверки орфографии в активном листе или выделенном диапазоне.Если окно книги не развернуто, сочетание клавиш CTRL+F7 выполняет команду Переместить. С помощью клавиш перемещения курсора передвиньте окно и нажмите клавишу ВВОД, а для отмены — клавишу ESC.
F8	Переход в режим выделения и выход из него. Если режим выделения включен, в строке состояния отображается надпись Расширяемое выделение, а клавиши со стрелками расширяют область выделения.Сочетание клавиш SHIFT+F8 позволяет при помощи стрелок добавить к выделению несмежные ячейки или диапазон.Сочетание клавиш CTRL+F8 выполняет команду Размер (в меню Элемент управленияокна книги), если окно не развернуто.Сочетание клавиш ALT+F8 выводит на экран диалоговое окно Макрос, позволяющее создавать, запускать, изменять и удалять макросы.
F9	Вычисление всех листов всех открытых книг.Сочетание клавиш SHIFT+F9 вычисляет активный лист.Сочетание клавиш CTRL+ALT+F9 вычисляет все листы всех открытых книг, независимо от того, вносились ли в них изменения с момента последнего вычисления.Сочетание клавиш CTRL+ALT+SHIFT+F9 проверяет зависимые формулы, а затем заново вычисляет ячейки во всех открытых книгах, включая ячейки, не помеченные для вычисления.Сочетание клавиш CTRL+F9 сворачивает окно книги в значок.
F10	Включение или отключение подсказок клавиш. (То же действие выполняется клавишей ALT.)Сочетание клавиш SHIFT+F10 отображает контекстное меню для выбранного элемента.Сочетание клавиш ALT+SHIFT+F10 отображает меню или сообщение для кнопки «Поиск ошибок».Сочетание клавиш CTRL+F10 разворачивает или восстанавливает исходный размер выбранного окна книги.
F11	Создание диаграммы данных в текущем диапазоне на отдельном листе диаграммы.Сочетание клавиш SHIFT+F11 вставляет в книгу новый лист.Сочетание клавиш ALT+F11 открывает редактор Microsoft Visual Basic для приложений, в котором можно создать макрос на языке Visual Basic для приложений (VBA).
F12	Отображение диалогового окна Сохранить как.

Другие полезные сочетания клавиш

Клавиша	Описание
ALT	Отображение подсказок клавиш (новых сочетаний клавиш) на ленте.Например:Клавиши ALT, О, З включают на листе режим разметки страницы.Клавиши ALT, О, Ы включают на листе обычный режим.Клавиши ALT, W, I включают на листе страничный режим.
Клавиши со стрелками	Переход по листу на одну ячейку вверх, вниз, влево или вправо.Сочетание CTRL+клавиша со стрелкой осуществляет переход на границу текущей область данных листа.Сочетание SHIFT+клавиша со стрелкой расширяет выделенную область на одну ячейку.Сочетание CTRL+SHIFT+клавиша со стрелкой расширяет выделенную область ячеек до последней непустой ячейки в той же строке или в том же столбце, что и активная ячейка, или, если следующая ячейка пуста, до следующей непустой ячейки.Клавиши СТРЕЛКА ВЛЕВО и СТРЕЛКА ВПРАВО при выделенной ленте позволяют выбрать вкладку слева или справа. При выбранном или открытом подменю эти клавиши со стрелками позволяют перейти от главного меню к подменю и обратно. Если выбрана вкладка ленты, эти клавиши позволяют перемещаться по вкладкам.Клавиши СТРЕЛКА ВНИЗ и СТРЕЛКА ВВЕРХ при открытом меню или подменю позволяют перейти к предыдущей или следующей команде. Если выбрана вкладка ленты, эти клавиши позволяют переходить вверх и вниз по группам вкладок.В диалоговом окне клавиши со стрелками вызывают переход к следующему или предыдущему параметру в выбранном раскрывающемся списке или в группе параметров.Клавиша СТРЕЛКА ВНИЗ или сочетание клавиш ALT+СТРЕЛКА ВНИЗ открывает выбранный раскрывающийся список.
BACKSPACE	Удаляет один символ слева в строке формул.Также удаляет содержимое активной ячейки.В режиме редактирования ячеек удаляет символ слева от места вставки.
DELETE	Удаляет содержимое ячейки (данные и формулы) в выбранной ячейке, не затрагивая форматирование ячейки или комментарии.В режиме редактирования ячеек удаляет символ справа от курсора.
END	Включает и выключает режим перехода в конец. В этом режиме с помощью клавиш со стрелками можно перемещаться к следующей непустой ячейке этой же строки или столбца как к активной ячейке. Режим перехода в конец выключается автоматически после нажатия клавиши со стрелкой. Необходимо снова нажимать клавишу END перед нажатием следующей клавиши со стрелкой. Когда этот режим включен, он отображается в строке состояния.Если ячейки пустые, последовательное нажатие клавиши END и клавиш со стрелками приводит к перемещению к последней ячейке в строке или столбце.Кроме того, если на экране отображается меню или подменю, нажатие клавиши END приводит к выбору последней команды из меню.Сочетание клавиш CTRL+END осуществляет переход в последнюю ячейку на листе, расположенную в самой нижней используемой строке крайнего правого используемого столбца. Если курсор находится в строке формул, сочетание клавиш CTRL+END перемещает его в конец текста.Сочетание клавиш CTRL+SHIFT+END расширяет выбранный диапазон ячеек до последней используемой ячейки листа (нижний правый угол). Если курсор находится в строке формул, сочетание клавиш CTRL+SHIFT+END выделяет весь текст в строке формул от позиции курсора до конца строки (это не оказывает влияния на высоту строки формул).
ВВОД	Завершение ввода значения в ячейку в строке формул и выбор ячейки ниже (по умолчанию).В форме для ввода данных осуществляется переход к первому полю следующей записи.Открытие выбранного меню (для активации строки меню нажмите F10) или выполнение выбранной команды.В диалоговом окне выполняется действие, назначенное выбранной по умолчанию кнопке в диалоговом окне (эта кнопка выделена толстой рамкой, часто — кнопка ОК).Сочетание клавиш ALT+ВВОД начинает новую строку в текущей ячейке.Сочетание клавиш CTRL+ВВОД заполняет выделенные ячейки текущим значением.Сочетание клавиш SHIFT+ВВОД завершает ввод в ячейку и перемещает точку ввода в ячейку выше.
ESC	Отмена ввода в ячейку или строку формул.Закрытие меню или подменю, диалогового окна или окна сообщения.Кроме того, нажатие этой клавиши закрывает полноэкранный режим (если он был активен) и восстанавливает обычный режим, в котором отображаются лента и строка состояния.
HOME	Переход в начало строки или листа.При включенном режиме SCROLL LOCK осуществляется переход к ячейке в левом верхнем углу окна.Кроме того, если на экране отображается меню или подменю, осуществляется выбор первой команды из меню.Сочетание клавиш CTRL+HOME осуществляет переход к ячейке в начале листа.Сочетание клавиш CTRL+SHIFT+HOME расширяет выбранный диапазон ячеек до начала листа.
PAGE DOWN	Осуществляет перемещение на один экран вниз по листу.Сочетание клавиш ALT+PAGE DOWN осуществляет перемещение на один экран вправо по листу.Сочетание клавиш CTRL+PAGE DOWN осуществляет переход к следующему листу книги.Сочетание клавиш CTRL+SHIFT+PAGE DOWN приводит к выбору текущего и следующего листов книги.
PAGE UP	Осуществляет перемещение на один экран вверх по листу.Сочетание клавиш ALT+PAGE UP осуществляет перемещение на один экран влево по листу.Сочетание клавиш CTRL+PAGE UP осуществляет переход к предыдущему листу книги.Сочетание клавиш CTRL+SHIFT+PAGE UP приводит к выбору текущего и предыдущего листов книги.
ПРОБЕЛ	В диалоговом окне осуществляет нажатие выбранной кнопки или устанавливает и снимает флажок.Сочетание клавиш CTRL+ПРОБЕЛ выделяет столбец листа.Сочетание клавиш SHIFT+ПРОБЕЛ выделяет всю строку листа.Сочетание клавиш CTRL+SHIFT+ПРОБЕЛ выделяет весь лист.Если лист содержит данные, сочетание клавиш CTRL+SHIFT+ПРОБЕЛ выделяет текущую область. Повторное нажатие CTRL+SHIFT+ПРОБЕЛ выделяет текущую область и ее итоговые строки. При третьем нажатии CTRL+SHIFT+ПРОБЕЛ выделяется весь лист.Если выбран объект, сочетание клавиш CTRL+SHIFT+ПРОБЕЛ выделяет все объекты листа.Сочетание клавиш ALT+ПРОБЕЛ отображает меню Элемент управления окна Excel.
TAB	Перемещение на одну ячейку вправо.Переход между незащищенными ячейками в защищенном листе.Переход к следующему параметру или группе параметров в диалоговом окне.Сочетание клавиш SHIFT+TAB осуществляет переход к предыдущей ячейке листа или предыдущему параметру в диалоговом окне.Сочетание клавиш CTRL+TAB осуществляет переход к следующей вкладке диалогового окна.Сочетание клавиш CTRL+SHIFT+TAB осуществляет переход к предыдущей вкладке диалогового окна.

Ctrl+N — Создание нового документа

Ctrl+S, Shift+F12 — Сохранение документа

F12 — Сохранение документа под другим именем (в другом каталоге)

Ctrl+O, Ctrl+F12 — Открытие документа

Ctrl+F4, Ctrl+W — Закрытие документа

Alt+F4 — Закрытие Excel

Ctrl+F6, Ctrl+Shitt+F6, Ctrl+Tab, Ctrl+Shift+Tab — Переход между окнами документов

Alt+Tab — Переключение окон документов (если переключатель окна на панели задач включен)

Ctrl+F10 — Перевод всех окон документов из нормального состояния в развернутое, и обратно

Ctrl+F5 — Перевод всех окон документов в нормальный вид

F6, Shift+F6 — Переход между частями таблицы после разбивки

Shift+F10 — Вызов контекстного меню объекта

F7 — Проверка орфографии

Ctrl+P, Ctrl+Shift+F12 — Печать документа

Shift+F11 ,Alt+Shitt+F1 — Создание листа таблицы

F11, Alt+F1 — Создание листа диаграммы

Ctrl+Page Down — Следующий лист

Ctrl+Page Up — Предыдущий лист

Ctrl+Shift+Page Down — Выделение следующего листа

Ctrl+Shift+Page Up — Выделение предыдущего листа

Alt+Enter — Ввод текста в несколько строк

Ctrl+Enter — Ввод одинаковых данных в ячейки выделенного блока

Ctrl+R (Ctrl+D) — Ввод одинаковых данных в ячейки справа (снизу)

Ctrl+; — Вставка текущей даты

Ctrl+Shift+; — Вставка текущего времени

Shitt+F3 — Вызов мастера функций

Ctrl+Sbitt+A — Вызов мастера функций после набора имени функции

Ctrl+A — Ввод имен аргументов функции после набора имени функции

Alt+= — Автосуммирование

Ctrl+Z, Alt+Back Space — Отмена последней операции

F4, Ctrl+Y — Возвращение отмененного действия (или повтор последнего действия)

F4 (при редактировании) — Смена относительных/абсолютных адресов

Ctrl+1 — Вызов диалогового окна Формат ячеек

Ctrl+B, Ctrl+2 — Шрифт полужирный

Ctrl+I, Ctrl+3 — Шрифт курсив

Ctrl+U, Ctrl+4 — Шрифт подчеркнутый

Ctrl+5 — Шрифт перечеркнутый

Ctrl+Shift+P — Активизация инструмента Размер

Ctrl+Shift+F — Активизация инструмента Шрифт

Ctrl+Shift+- — Формат Обычный (по умолчанию)

Ctrl+Shift+1 — Формат числа 0.00 с разделителем групп разрядов

Ctrl+Shift+2 — Формат времени ч.мм

Ctrl+Shift+3 — Формат даты ДД.МММ.ГГ

Ctrl+Shift+4 — Формат числа Денежный

Ctrl+Shift+5 — Формат числа Процентный

Ctrl+Shift+6 — Формат числа О.ООЕ+00

Ctrl+Shift+7 — Обрамление внешнего контура тонкой сплошной линией

Ctrl+Shift+- — Отмена обрамления всех линий

Alt+’ — Вызов окна Стиль

Shift+Space — Выделение строки

Ctrl+Space — Выделение столбца

Ctrl+A — Выделение всей таблицы

Ctrl+Shift+8, Ctrl+* (цифр.клав.) — Выделение области заполненных ячеек

Shift+клавиши перемещения, F8 и клавиши перемещения — Выделение одиночного блока

Shift+F8 & F8 и клавиши перемещения — Выделение кусочного блока

Ctrl+C, Ctrl+lnsert — Копирование в буфер обмена

Ctrl+X, Shitt+Delete — Вырезание в буфер обмена

Ctrl+V, Shift+lnsert — Вставка из буфера обмена

Ctrl+- — Удаление ячеек

Ctrl+Shift+= — Вставка новых ячеек

Ctrl+9, Ctrl+Shift+9 — Скрытие (показ) строк

Ctrl+0, Ctrl+Shift+0 — Скрытие (показ) столбцов

Alt+; — Отмена выделения скрытых ячеек (выделение видимых)

Ctrl+6 — Скрытие/показ графических объектов

Ctrl+G, F5 — Переход и выделение ячеек

Ctrl+F, Shift+F5 — Поиск

Shift+F4 — Повторный поиск

Ctrl+H — Поиск и замена

Ctrl+F3 — Присвоение имени

Ctrl+Shift+F3 — Создание имен по существующим заголовкам

F3 — Вставка имени в формулу

Shift+F2 — Добавление/редактирование примечания ячейки

Ctrl+Shift+O — Выделение всех ячеек с примечаниями

F9, Ctrl+= — Ручное вычисление формул во всех открытых книгах

Shift+F9 — Ручное вычисление формул в текущей таблице

Ctrl+’ — Включение/выключение режима показа формул

Ctrl+[, Ctrl+] — Выделение влияющих (зависимых) ячеек

Ctrl+Shift+[, Ctrl+Shift+] — Выделение косвенно влияющих (зависимых) ячеек

Ctrl+Shift+C — Копирование (запоминание) параметров графического объекта

Ctrl+Shift+V — Вставка (присвоение) параметров графического объекта

Ctrl+Shift+Space — Выделение всех графических объектов на листе

Клавиатурные комбинации редактирования содержимого ячеек

Ноmе — В начало строки

End — В конец строки

Ctrl+Стрелка вправо — На одно слово вправо

Ctrl+Стрелка влево — На одно слово влево

Ctrl+’ — Копирование в текущую ячейку всех данных из верхней ячейки

Ctrl+Shift+’ — Копирование в текущую ячейку из верхней ячейки всех значений, введенных или вычисленных по формуле

Delete (Back Space) — Удаление символа справа (слева) от курсора

Ctrl+Oelete — Удаление до конца строки

Клавиатурные комбинации перемещения по таблице

Tab — На ячейку вправо

Shift+Tab — На ячейку влево

Ctrl+Home — В первую первая ячейку таблицы (А1)

Ctrl+End, End и Home — В правый нижний угол активной области

Alt+Page Up — На экран влево

Alt+Page Down — На экран вправо

Ctrl+Back Space — Возврат к текущей ячейке (если она не видна)

Ctrl+ Стрелка вправо, End и Стрелка вправо — Вправо по строке к границе заполненной области

Ctrl+ Стрелка влево, End и Стрелка влево — Влево по строке к границе заполненной области

Ctrl+ Стрелка вниз, End и Стрелка вниз — Вниз по столбцу к границе заполненной области

Ctrl+ Стрелка вверх, End и Стрелка вверх — Вверх по столбцу к границе заполненной области

Клавиатурные комбинации перемещения по выделенному блоку

Enter — Сверху вниз слева направо

Shift+Enter — Снизу вверх справа налево

Tab — Слева направо сверху вниз

Shift+Tab — Справа налево снизу вверх

Ctrl+Alt+ Стрелка вправо — К следующей части кусочного блока

Ctrl+Alt+ Стрелка влево — К предыдущей части кусочного блока

Ctrl+ . — В углы выделенного блока по часовой стрелке

Клавиатурные комбинации перемещения по базе данных

Стрелка вниз, Enter — К следующей записи

Стрелка вврех, Shift+Enter — К предыдущей записи

Page Up — На 10 записей вперед

Page Down — На 10 записей назад

Ctrl+ Стрелка вниз, Ctrl+Page Down -К последней пустой записи

Ctrl+ Стрелка вверх, Ctrl+Page Up — К первой записи

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

Как сделать «красиво в Excel»? Основные уловки Ищем пропажу. В Excel пропали листы или лента, панель команд?

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

Основные возможности я описал в начале статьи, но на самом деле их масса. Подробнее о самых полезных

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Подробнее о тормозах Excel и их причинах читайте здесь. Эта статья помогла не одной сотне людей 😉

Надеюсь был полезен, не прощаюсь!

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

Похожие статьи

Как сделать «красиво в Excel»? Основные уловки Ищем пропажу. В Excel пропали листы или лента, панель команд?

Как сделать таблицу в Excel

 

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

Столбцы, строки и ячейки

Откройте программу Эксель: Пуск — Microsoft Office — Microsoft Office Excel.

В верхней части расположена панель редактирования. Вот как она выглядят в Microsoft Excel 2007-2019:

Под ней находится рабочая (основная) часть программы.

Обратите внимание верхнюю строку с буквами A, B, C, D и так далее. Это названия столбцов.

Также обратите внимание на небольшие прямоугольники с цифрами 1, 2, 3, 4 и т.д. в левой части. Это названия строк.

При помощи них формируется название ячеек. Например, первая верхняя ячейка слева называется A1, потому что она находится в столбике A и в строке 1.

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

Попробуем напечатать несколько цифр в B2. Для этого нужно щелкнуть по ней и на клавиатуре набрать цифры.

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

 

Кнопки оформления

Рассмотрим кнопки оформления в верхней части программы, в разделе «Главная». Кстати, они же есть и в Word.

— шрифт. То, каким стилем будет написан текст.

— размер букв.

— начертание (полужирный, курсив, подчеркнутый).

— выравнивание текста: по левому краю, по центру, по правому краю.

— отмена последнего действия (возврат на шаг назад).

— изменение цвета текста.

— заполнение цветом (заливка).

Как создать таблицу

Посмотрите на уже составленную в Excel небольшую таблицу:

Верхняя ее часть — это шапка.

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

За шапкой следует содержание:

А сейчас на практике мы попробует составить в программе Excel такую таблицу.

В нашем примере шапка — это верхняя (первая) строка. Обычно она именно там и находится.

Щелкните по ячейке А1 и напечатайте первый пункт «Наименование». Затем кликните по В1 и напечатайте следующий пункт — «Количество».

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

Заполните С1 и D1.

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

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

То же самое можно проделать и со строками.

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

Расширьте столбцы, в которых не поместился текст. Затем немного увеличьте шапку. Для этого наведите курсор на линию между строкой 1 и 2. Когда он поменяет вид, нажмите левую кнопку и, не отпуская ее, расширьте первую строку.

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

Нажмите на A1. Этим простым действием Вы ее выделите, то есть «скажете» программе Excel, что собираетесь что-то изменить в данной ячейке. А теперь нажмите на кнопку в верхней части программы. Текст станет толще и чернее (полужирный).

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

Есть несколько способов выделения.

---

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

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

Выделение столбца или строки. Для этого нужно нажать по названию нужного столбца или строки.

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

---

А теперь попробуем изменить шапку нашей таблицы. Для этого выделите ее. Я предлагаю выделить строку целиком, то есть нажать на цифру 1.

После этого сделаем буквы толще и чернее. Для этого нажимаем кнопку .

Также в таблице, которую нам нужно сделать, слова в шапке расположены по центру. Для этого нажмите кнопку .

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

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

Самое сложное мы сделали. Осталось заполнить таблицу. Сделайте это самостоятельно.

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

Предлагаю выделить таблицу целиком. Для этого нажмите кнопку .

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

Из появившегося списка выберите какой-нибудь шрифт. Например, Arial.

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

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

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

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

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

Из списка выберите пункт «Все границы».

Если Вы все сделали правильно, то получится вот такая таблица.

Автор: Илья Кривошеев

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

 

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

Рассмотрим несколько способов заливки цветом нужных ячеек в программе Excel.

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

Инструмент заливки ячеек расположен на панели быстрого доступа во вкладке «Главная» в разделе «Шрифт» . Чтобы закрасить ячейку, следует ее выделить левой кнопкой мыши и выбрать нужный цвет на панели в разделе «Шрифт» > «Цвет заливки».

Второй способ подходит тем, кто по каким-то причинам удалил указанный выше инструмент из панели быстрого доступа.

Чтобы окрасить ячейки в нужный цвет следует выполнить следующие действия:

1. Выделить ячейки левой кнопкой мыши;
2. Кликнуть по выделению правой кнопкой мыши;
3. В появившемся контекстном меню кликнуть по строчке «Формат ячеек…»;
4. В появившемся окне формы настроек следует выбрать вкладку «Заливка»;
5. На вкладке заливка Вы можете выбрать понравившийся Вам оттенок из предложенных или выбрать индивидуальный нажав на кнопку «Другие цвета…» ;
6. После нажатия кнопки «ОК» ячейки приобретут нужную заливку.

Третий способ – заливка, зависящая от наполнения ячеек (условная).

Заливка по условиям наполнения ячейки выполняется при помощи опции «Условное форматирование». Кнопка условного форматирования находится на вкладке «Главная».

Рассмотрим условное форматирование по следующим критериям:

Ячейки, имеющие значение меньше «5» залить зеленым цветом, выше «5» – красным.

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

1. Выделить нужные ячейки левой кнопкой мыши;
2. Выбрать и нажать кнопку «Условное форматирование» во вкладке «Главная» панели быстрого доступа;
3. Выбрать правило форматирования — указать условие и цвет заливки. (для нашего примера следует создать два правила: 1) меньше пяти – зеленая заливка; 2) больше пяти — красная)
4. Нажать «Enter».

 

Выбрать содержимое ячейки в Excel

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

Примечание: Если лист был защищен, вы не сможете выбрать ячейки или их содержимое на листе.

Выберите одну или несколько ячеек

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

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

   Или используйте Shift + клавиши со стрелками для выбора диапазона.

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

Выберите одну или несколько строк и столбцов

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

2. Выберите номер строки, чтобы выделить всю строку. Или щелкните любую ячейку в строке и затем нажмите Shift + Пробел.

3. Чтобы выбрать несмежные строки или столбцы, удерживайте Ctrl и выберите номера строки или столбца.

Выберите таблицу, список или рабочий лист

1. Чтобы выбрать список или таблицу, выберите ячейку в списке или таблице и нажмите Ctrl + A.

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

Нужна дополнительная помощь?

Вы всегда можете спросить эксперта в техническом сообществе Excel, получить поддержку в сообществе Answers или предложить новую функцию или улучшение в Excel User Voice.

См. Также

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

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

Перемещение или копирование строк и столбцов

Транспонировать (вращать) данные из строк в столбцы или наоборот

Закрепить панели, чтобы заблокировать строки и столбцы

Блокировать или разблокировать определенные области защищенного листа

Получить список уникальных значений в Excel и извлечь уникальные строки

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

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

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

Как получить уникальные значения в Excel

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

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

Формула уникальных значений массива (завершается нажатием Ctrl + Shift + Enter):

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10) + (СЧЁТЕСЛИ ($ A $ 2: $ A $ 10, $ A $ 2 : $ A $ 10) <> 1), 0)), "")

Обычная формула уникальных значений (заполняется нажатием Enter):

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0, ИНДЕКС (СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10) + (СЧЁТЕСЛИ ($ A $ 2: $ A $ 10, $ A $ 2: $ A $ 10) <> 1), 0,0), 0)), "")

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

• A2: A10 — исходный список.
• B1 — верхняя ячейка уникального списка минус 1. В этом примере мы начинаем уникальный список в B2, и поэтому мы передаем B1 в формулу (B2-1 = B1). Если ваш уникальный список начинается, скажем, с ячейки C3, измените $ B $ 1: B1 на $ C $ 2: C2.

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

В этом примере мы извлекаем уникальные имена из столбца A (точнее из диапазона A2: A20), и на следующем снимке экрана показана формула массива в действии:

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

• Настройте одну из формул в соответствии с вашим набором данных.
• Введите формулу в первую ячейку уникального списка (в данном примере B2).
• Если вы используете формулу массива, нажмите Ctrl + Shift + Enter. Если вы выбрали обычную формулу, как обычно, нажмите клавишу Enter.
• Скопируйте формулу до упора вниз, перетащив маркер заполнения. Поскольку обе формулы уникальных значений инкапсулированы в функцию ЕСЛИОШИБКА, вы можете скопировать формулу до конца таблицы, и она не будет загромождать ваши данные какими-либо ошибками, независимо от того, сколько уникальных значений было извлечено.

Как получить различные значения в Excel (уникальные + 1 ^st повторяющихся вхождений)

Как вы, возможно, уже догадались из заголовка этого раздела, различных значений в Excel — это разные значения в списке, т.е.е. уникальные значения и первые экземпляры повторяющихся значений. Например:

Чтобы получить отдельный список в Excel, используйте следующие формулы.

Массив отдельная формула (необходимо нажать Ctrl + Shift + Enter):

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10), 0)), "")

Обычный отличная формула:

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0, ИНДЕКС (СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10), 0, 0), 0)), "")

Где:

• A2: A10 — исходный список.
• B1 — это ячейка над первой ячейкой отдельного списка. В этом примере отдельный список начинается с ячейки B2 (это первая ячейка, в которую вы вводите формулу), поэтому вы ссылаетесь на B1.

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

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

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10 & "") + ЕСЛИ ($ A $ 2: $ A $ 10 = " ", 1,0), 0))," ")

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

.

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10 & "") + IF (ISTEXT ($ A $ 2: $ A $ 10 ) = ЛОЖЬ, 1,0), 0)), "")

Напоминаем, что в приведенных выше формулах A2: A10 — это исходный список, а B1 — это ячейка прямо над первой ячейкой отдельного списка.

На следующем снимке экрана показан результат обеих формул:

При работе с данными, чувствительными к регистру, такими как пароли, имена пользователей или имена файлов, вам может потребоваться список отдельных значений, чувствительных к регистру. Для этого используйте следующую формулу массива, где A2: A10 — это исходный список, а B1 — это ячейка над первой ячейкой отдельного списка:

Формула массива для получения отдельных значений с учетом регистра (требуется нажатие Ctrl + Shift + Enter)

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0; ЧАСТОТА (ЕСЛИ (ТОЧНО ($ A $ 2: $ A $ 10, ТРАНСПОЗИРОВАТЬ ($ B $ 1: B1))), MATCH (ROW ($ A $ 2 : $ A $ 10), ROW ($ A $ 2: $ A $ 10)), ""), MATCH (ROW ($ A $ 2: $ A $ 10), ROW ($ A $ 2: $ A $ 10))), 0) ), "")

Как работает уникальная формула

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

Само собой разумеется, что формулы для извлечения уникальных и различных значений в Excel не являются ни тривиальными, ни простыми. Но при более внимательном рассмотрении вы можете заметить, что все формулы основаны на одном подходе — с использованием ИНДЕКС / ПОИСКПОЗ в сочетании с функциями СЧЁТЕСЛИ или СЧЁТЕСЛИ + ЕСЛИ.

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

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10), 0)), "")

Для начала давайте отбросим очевидную функцию ЕСЛИОШИБКА, которая используется с единственной целью для устранения ошибок # Н / Д, когда количество ячеек, в которые вы скопировали формулу, превышает количество различных значений в исходном списке.

А теперь давайте разберем основную часть нашей четкой формулы:

1. СЧЁТЕСЛИ (диапазон, критерий) возвращает количество ячеек в диапазоне, которые соответствуют указанному условию.

   В этом примере COUNTIF ($ B $ 1: B1, $ A $ 2: $ A $ 10) возвращает массив из единиц и нулей в зависимости от того, присутствует ли где-либо какое-либо из значений исходного списка ($ A $ 2: $ A $ 10). в отдельном списке ($ B $ 1: B1). Если значение найдено, формула возвращает 1, иначе — 0.

   В частности, в ячейке B2 СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10) становится:

   COUNTIF («Distinct», {«Ронни»; «Дэвид»; «Салли»; «Джереми»; «Роберт»; «Дэвид»; «Роберт»; «Том»; «Салли»})

   и возвращает:

   {0; 0; 0; 0; 0; 0; 0; 0; 0}

   , потому что ни один из элементов исходного списка ( критерий ) не появляется в диапазоне , где функция ищет совпадение.В этом случае диапазон ($ B $ 1: B1) состоит из одного элемента — «Отдельный».

2. MATCH (lookup_value, lookup_array, [match_type]) возвращает относительную позицию значения поиска в массиве.
   В этом примере lookup_value равно 0, и, следовательно,:

   ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10), 0)

   превращается в:

   ПОИСКПОЗ (0, { 0 ; 0; 0; 0; 0; 0; 0; 0; 0}, 0)

   и возвращает 1

   , потому что наша функция ПОИСКПОЗ получает первое значение, в точности равное значению поиска (как вы помните, значение поиска равно 0).

3. INDEX (array, row_num, [column_num]) возвращает значение в массиве на основе указанной строки и (необязательно) номеров столбцов.

   В этом примере INDEX ($ A $ 2: $ A $ 10, 1)

   становится:

   ИНДЕКС ({"Ронни"; "Дэвид"; "Салли"; "Джереми"; "Роберт"; "Дэвид"; "Роберт"; "Том"; "Салли"}, 1)

   и возвращает «Ронни».

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

   Итак, при копировании в ячейку B3, СЧЁТЕСЛИ ($ B $ 1: B1 , $ A $ 2: $ A $ 10) изменяется на СЧЁТЕСЛИ ($ B $ 1: B2 , $ A $ 2: $ A $ 10) и становится :

   COUNTIF ({"Distinct"; "Ronnie"}, {"Ronnie"; "David"; "Sally"; "Jeremy"; "Robert"; "David"; "Robert"; "Tom"; "Sally" }), 0)), "")

   и возвращает:

   {1; 0; 0; 0; 0; 0; 0; 0; 0}

   , потому что один «Ронни» находится в диапазоне $ B $ 1: B2.

   И затем MATCH (0, {1; 0 ; 0; 0; 0; 0; 0; 0; 0}, 0) возвращает 2, потому что 2 — это относительное положение первого 0 в массиве.

   И, наконец, ИНДЕКС ($ A $ 2: $ A $ 10, 2) возвращает значение из 2 ^{-й строки} , то есть «Дэвид».

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

Если у вас все еще возникают трудности с вычислением формулы, вы можете ознакомиться со следующим руководством, в котором подробно объясняется, как работает связь ИНДЕКС / ПОИСКПОЗ: ИНДЕКС и ПОИСКПОЗ как лучшая альтернатива ВПР в Excel.

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

Формула уникальных значений — содержит еще одну функцию СЧЁТЕСЛИ, которая исключает из уникального списка все элементы, которые появляются в исходном списке более одного раза: СЧЁТЕСЛИ ($ A $ 2: $ A $ 10, $ A $ 2: $ A $ 10) <> 1 .

Формула различных значений, игнорирующая пробелы — здесь вы добавляете функцию ЕСЛИ, которая предотвращает добавление пустых ячеек в отдельный список: IF ($ A $ 2: $ A $ 13 = "", 1,0) .

Формула различных текстовых значений, игнорирующая числа — вы используете функцию ISTEXT, чтобы проверить, является ли значение текстом, и функцию IF, чтобы отклонить все другие типы значений, включая пустые ячейки: IF (ISTEXT ($ A $ 2: $ A $ 13) = ЛОЖЬ, 1,0) .

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

1. Выберите столбец данных, из которого вы хотите извлечь отдельные значения.
2. Перейдите на вкладку Data > Sort & Filter и нажмите кнопку Advanced :
   
3. В диалоговом окне Advanced Filter выберите следующие параметры:
   • Проверить Копировать в другое место радиокнопка.
   • В поле Диапазон списка убедитесь, что исходный диапазон отображается правильно.
   • В поле Копировать в введите самую верхнюю ячейку целевого диапазона.Помните, что вы можете скопировать отфильтрованные данные только на активный лист .
   • Выберите Только уникальные записи

4. Наконец, нажмите кнопку OK и проверьте результат:
   

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

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

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

А теперь давайте посмотрим, как работает инструмент Duplicate Remover.

Предположим, у вас есть сводная таблица, созданная путем объединения данных из нескольких других таблиц. Очевидно, что эта сводная таблица содержит много повторяющихся строк, и ваша задача состоит в том, чтобы извлечь уникальные строки, которые появляются в таблице только один раз, или отдельные строки, включая уникальные и 1 ^st повторяющихся вхождений. В любом случае, с надстройкой Duplicate Remover работа выполняется за 5 быстрых шагов.

1. Выберите любую ячейку в исходной таблице и нажмите кнопку Duplicate Remover на вкладке Ablebits Data в группе Dedupe .
   

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

2. Выберите тип значения, которое вы хотите найти, и нажмите Далее :
   • Уникальный
   • Уникальные +1 ^st экземпляров (отдельные)

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

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

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

   В этом примере мы хотим найти уникальные строки на основе значений во всех 3 столбцах ( Номер заказа , Имя и Фамилия ), поэтому мы выбираем все.
   

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

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

   В этом примере давайте выберем новый лист:
   

5. Нажмите кнопку Готово , и все готово!
   

Понравился этот быстрый и простой способ получить список уникальных значений или строк в Excel? Если это так, я рекомендую вам загрузить ознакомительную версию ниже и попробовать. Duplicate Remover, а также все другие инструменты для экономии времени, которые у нас есть, включены в Ultimate Suite for Excel.

Если вы довольны результатами и решили купить лицензию, мы рады предложить вам особую возможность:

Промокод для Ultimate Suite — эксклюзивное предложение для читателей нашего блога!

Доступные загрузки

Найти уникальные значения в Excel — образец книги (.xlsx)
Ultimate Suite — ознакомительная версия (файл .zip)

Вас также может заинтересовать

Добавление границ к ячейкам (Microsoft Excel)

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

1. Выделите ячейку или диапазон ячеек, которые нужно ограничить.
2. Выберите параметр «Ячейки» в меню «Формат».Вы увидите диалоговое окно «Формат ячеек».
3. Щелкните вкладку Граница. (См. Рисунок 1.)

Рисунок 1. Вкладка «Граница» диалогового окна «Формат ячеек».

4. В разделе «Граница» диалогового окна выберите место, где вы хотите применить границу. (Контур будет окружать всю ячейку или диапазон ячеек.)
5. Выберите тип линии в области «Стиль».
6. Щелкните ОК.

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

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

ExcelTips — ваш источник экономичного обучения работе с Microsoft Excel. Этот совет (2663) применим к Microsoft Excel 97, 2000, 2002 и 2003.

Автор Биография

Аллен Вятт

Аллен Вятт — всемирно признанный автор, автор более чем 50 научно-популярных книг и многочисленных журнальных статей.Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнать больше о Allen …

Сохранение документов только для чтения по умолчанию

При сохранении документов вы можете указать, что они должны быть сохранены в формате «только для чтения», чтобы их нельзя было изменить …

Узнайте больше

Изменение языковых настроек для всего текста документа

Word поддерживает несколько языков для создания документов.Если вам необходимо регулярно менять используемый язык …

Узнайте больше

Преобразование абзацев в комментарии

Хотите вытащить текст из группы абзацев и вставить этот текст в комментарии? Это легко сделать с помощью представленного макроса …

Узнайте больше

Автоматические линии для разделения списков

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

Узнайте больше

Границы чертежа

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

Узнайте больше

Закругленные углы ячеек

При форматировании листа Excel позволяет легко добавлять границы к ячейкам. Добавление закругленных углов к ячейкам — это …

Узнайте больше

Быстро изменить регистр текста во всех выбранных ячейках сразу

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

Экономьте 5 минут в день с помощью ASAP Utilities, чтобы быстро изменить регистр текста.

Ваш текст быстро переводится в верхний, нижний регистр и т. Д.?

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

1. Текст »Заменить на ЗАГЛАВНЫЙ регистр
2. Текст »Изменить на нижний регистр
3. Текст »Первое слово начинать с прописных
4. Текст »Сделать первый символ прописным, остальные строчные
5. Текст »Каждое слово начинать с верхнего регистра

Просто выберите ячейки и запустите один из текстовых инструментов:

Например, быстро изменить текст в выбранных ячейках на верхний регистр:

Как в MS Word

В MS Word вы можете использовать клавиши Shift + F3, чтобы изменить регистр выделенного текста.
Но в Excel нет такого ярлыка или параметра в меню / ленте (даже в новом Excel 2013. Я не знаю, почему Microsoft не добавляет его, многие люди просили об этом. Даже это открытый исходный код в качестве альтернативы в LibreOffice Calc есть «Изменить регистр» в меню.)
Единственный способ изменить регистр в Excel — использовать дополнительный (вспомогательный) столбец с формулами и значениями для копирования / вставки или с помощью макросов.
К счастью, вы также можете использовать надстройку, такую ​​как ASAP Utilities, чтобы быстро изменить регистр.Вы даже можете назначить свои собственные ярлыки, если часто используете инструменты изменения регистра.

Быстрое решение

Просто выберите ячейки и затем выберите один из следующих инструментов в меню Excel:
ASAP Utilities »

1. Текст »Заменить на ЗАГЛАВНЫЙ регистр
2. Текст »Изменить на нижний регистр
3. Текст »Первое слово начинать с прописных
4. Текст »Сделать первый символ прописным, остальные строчные
5. Текст »Каждое слово начинать с верхнего регистра

Это более простая и быстрая альтернатива методу по умолчанию, который предлагает Excel и описывает Microsoft.

Сколько времени это сэкономит?

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

Скачать

Если у вас еще нет ASAP Utilities, вы можете загрузить бесплатную версию для дома и учебы (для личного использования, студентов и благотворительных организаций) или полнофункциональную 90-дневную пробную версию для бизнеса по адресу:
// www.asap-utilities.com/download-asap-utilities.php

Удалить форматирование ячеек в Excel — Инструкции

Удаление форматирования ячеек в Excel: обзор

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

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

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

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

1. Чтобы удалить форматирование ячеек в Excel , выберите ячейки, из которых вы хотите удалить все форматирование.
2. Затем щелкните вкладку «Главная» на ленте.
3. Затем нажмите кнопку «Очистить» в группе кнопок «Редактирование».
4. Наконец, выберите команду «Очистить форматы» из раскрывающегося меню.

Удаление форматирования ячеек в Excel: видеоурок

В следующем видеоуроке, озаглавленном «Удаление всего форматирования из ячеек», показано, как удалить форматирование ячеек в Excel. Этот видео-урок о том, как очистить форматирование ячеек в Excel, взят из нашего полного руководства по Excel под названием «Освоение Excel Made Easy v.2019 и 365 ».

Возврат значений множественных совпадений в Excel — Xelplus

Этот метод будет использовать функцию ИНДЕКС с функцией АГРЕГАТ для поиска связанных приложений для выбранного отдела и компиляции результатов в новый список. Мы также интегрируем тест IF, чтобы визуально подавить любые ошибки, которые могут появиться, когда возвращенные элементы не полностью заполняют область списка результатов.

Для начала выберите ячейку G4 и войдите в раздел «Игра».

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

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

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

ВПР в Excel: основы ВПР и ГПР объяснены на примерах

Проблема с использованием VLOOKUP в этом сценарии заключается в том, что VLOOKUP всегда останавливается на первом обнаруженном совпадающем элементе в списке поиска. Если мы ищем «Игра», ВПР всегда останавливается в ячейке A7 .

.

Мы хотим создать список, в котором 1 ^st вхождение приложения «Game» помещается в ячейку G5 ; вхождение 2 ^nd помещается в ячейку G6 ; 3 ^ряд в ячейке G7 ; и т.п.

Мы будем использовать функции ИНДЕКС и АГРЕГАТ для создания этого списка. Если вам требуется дополнительная информация об использовании INDEX (и MATCH), щелкните ссылку ниже.

Как использовать Excel INDEX MATCH (правильный способ)

Выберите ячейку G5 и начните с создания функции ИНДЕКС.

 = ИНДЕКС (массив; номер_строки; [номер_столбца]) 

Функция ИНДЕКС имеет следующие параметры:

• Массив = ячейки, из которых будут извлечены элементы и возвращены в качестве ответов.
• Row_num = позиция «вверх и вниз» в списке для перемещения для извлечения данных.
• Column_num = позиция в списке «слева направо» для перемещения для извлечения данных.

Мы хотим извлечь имена приложений из ячеек B5: B14 .

 = ИНДЕКС (5 млрд долларов: 14 

млрд долларов

Мы хотим вернуть приложение с именем «Fightrr» из позиции 3 ^rd в списке приложений, поэтому в качестве теста мы жестко закодируем номер « 3 ».

 = ИНДЕКС (5 бразильских долларов: 14 бразильских долларов , 3)  

Если мы введем формулу вниз по ячейкам в столбце « G », приложение с именем «Fightrr» будет появляться неоднократно, как и предыдущие результаты ВПР.Нам нужно найти способ изменить возвращаемое значение row_num с «3» на «4», с «5» на «7». Мы не можем просто увеличивать значение параметра row-num на 1 каждый раз, когда повторяем формулу; параметр необходимо изменить в зависимости от положения соответствующего подразделения в столбце « A ».

Мы будем использовать функцию AGGREGATE для создания списка строк (то есть позиций), в которых обнаруживается выбранный Дивизион («Игра»). Преимущество использования функции AGGREGATE заключается в том, что она может принимать несколько ответов без использования CTRL-Shift-Enter.

Функция АГРЕГАТ имеет следующие параметры:

• Function_num = число, соответствующее функции в списке AGGREGATE.

Мы будем использовать функцию МАЛЕНЬКИЙ (номер 15) .

• Параметры = число, соответствующее поведению при обработке ошибок, скрытых данных и других функций ОБЩИЙ И ПОДГОТОВИТЕЛЬНЫЙ ИТОГ при смешивании с данными.

Мы будем использовать вариант № 3, чтобы игнорировать все другие проблемы .

• Массив = значения для агрегирования.
  Выберем ячейки A5: A14 .
• [k] = необязательное значение при использовании функций выбора, таких как МАЛЕНЬКИЙ или БОЛЬШОЙ.
  Мы сохраним этот параметр на потом .

СОВЕТ: Чтобы сосредоточиться на одной проблеме за раз, мы построим функцию СОВМЕСТНЫЕ в столбце «H». Как только функция AGGREGATE заработает к нашему удовлетворению, мы свернем логику AGGREGATE в функцию INDEX.

Выберите ячейку H5 и введите следующую формулу:

 = АГРЕГАТ (15,3; 5 австралийских долларов: 14 австралийских долларов) 

Если мы выделим параметр массива и нажмем F9 , мы увидим, что предоставленный массив возвращает слова, расположенные в ячейках A5: A14.

.

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

 = АГРЕГАТ (15,3; (5 австралийских долларов: 14 австралийских долларов = 4 доллара США)) 

Если выделить параметр массива и нажать F9 , то мы увидим следующие результаты теста.

.

Проблема здесь в том, что Excel интерпретирует ЛОЖНЫЙ ответ как 0 (ноль), а ИСТИННЫЙ ответ как 1 (один). Если мы используем функцию МАЛЕНЬКИЙ для обнаружения, сначала будет выбран 0. Мы хотим преобразовать ЛОЖНЫЕ ответы в ошибки и ИСТИННЫЕ ответы в номера позиций в списке (т.е.е. 3, 4, 5, 7). Для этого мы разделим массив на себя.

 = АГРЕГАТ (15,3,  ( (5 австралийских долларов: 14 австралийских долларов = 4 доллара США)  / (5 австралийских долларов: 14 австралийских долларов = 4 доллара США)) ) 

Поскольку 0 ÷ 0 = ОШИБКА и 1 ÷ 1 = 1, мы получаем следующий список ответов.

.

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

 = АГРЕГАТ (15,3, ((5 австралийских долларов: 14 австралийских долларов = 4 доллара США) / (5 австралийских долларов: 14 австралийских долларов = 4 доллара США)  * СТРОКА (5 австралийских долларов: 14 австралийских долларов) ) ) 

Это даст следующие ответы.

.

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

 = АГРЕГАТ (15,3, ((5 австралийских долларов: 14 австралийских долларов = 4 доллара США) / (5 австралийских долларов: 14 австралийских долларов = 4 доллара США) * СТРОКА (5 австралийских долларов: 14 австралийских долларов)) СТРОКА (4 австралийских доллара) ) 

Это даст следующие ответы.

.

Теперь пора вернуться назад и использовать параметр [k] (параметр 4 ^th ) функции AGGREGATE.Мы хотим, чтобы функция SMALL увеличивалась на единицу для каждой копии функции AGGREGATE. Это будет достигнуто с помощью хитрого метода подсчета ячеек в постоянно увеличивающемся диапазоне выбора.

 = АГРЕГАТ (15,3, (($ A $ 5: $ A $ 14 = $ G $ 4) / ($ A $ 5: $ A $ 14 = $ G $ 4) * СТРОКА ($ A $ 5: $ A $ 14)) - СТРОКА (4 австралийских доллара),  N ) 

В этом случае «N» будет следующей функцией.

 РЯД ($ F $ 5: F5) 

При использовании в первой функции AGGREGATE результат будет 1. Если привязать начало диапазона к ячейке $ F $ 5 в качестве абсолютной ссылки и оставить конец диапазона F5 в качестве относительной ссылки, диапазон изменит свой размер. по мере того, как конец диапазона перемещается дальше от начала диапазона.

Ячейка	Функция	Диапазон высоты (в ячейках)
H5	РЯДА (5 $ F $: F5)	1
H6	РЯД ($ F $ 5: F6)	2
H7	РЯД (5 $ F $: F7)	3
H8	РЯДА (5 $ F $: F8)	4

Формула с интеллектуальным счетчиком строк должна выглядеть следующим образом.

 = АГРЕГАТ (15,3, (($ A $ 5: $ A $ 14 = $ G $ 4) / ($ A $ 5: $ A $ 14 = $ G $ 4) * СТРОКА ($ A $ 5: $ A $ 14)) - СТРОКА (4 австралийских доллара),  ряда (5 австралийских долларов: F5) ) 

Теперь скопируйте формулу (без знака равенства) из ячейки H5 и вставьте ее в параметр row_num функции ИНДЕКС в ячейке G5. Обновленная формула должна выглядеть следующим образом.

Исходная формула

 = ИНДЕКС (5 млрд долларов: 14 млрд долларов,  3 ) 

Обновленная формула

 = ИНДЕКС ($ B $ 5: $ B $ 14,  AGGREGATE (15,3, (($ A $ 5: $ A $ 14 = $ G $ 4) / ($ A $ 5: $ A $ 14 = $ G $ 4) * ROW ( $ A $ 5: $ A $ 14)) - СТРОКА (4 $ A $), СТРОКА (5 $ F $: F5)) ) 

Заполните формулу до ячеек G5: G14.Теперь у нас есть список приложений, связанных с выбранным отделом, а также ошибки, когда наш список возврата короче, чем ожидаемая максимальная длина списка.

Мы хотим скрыть ошибки.

Самый простой (читай «ленивый») способ скрыть ошибки — это вложить всю функцию ИНДЕКС / АГРЕГАТ в функцию ЕСЛИОШИБКА, как показано ниже.

 =  ЕСЛИОШИБКА  (ИНДЕКС (5 $ B $: 14 $ B $, АГРЕГАТ (15,3, ((5 $ A $: 14 $ A $ = 4 $ G) / (5 $ A $: 14 $ A $ = 4 $ G)) * СТРОКА (5 долларов США: 14 австралийских долларов)) - СТРОКА (4 доллара США), СТРОКА (5 долларов США: F5))) , ”” ) 

Проблема в том, что ЕСЛИОШИБКА должна полностью обработать функцию ИНДЕКС / АГРЕГАТ, чтобы определить, возникла ли ошибка.Это может привести к потере циклов ЦП при создании длинных списков связанных приложений.

Лучшая тактика — использовать оператор IF для подсчета количества раз, когда выбранный раздел появляется в списке подразделений, а затем сравнивать это с длиной текущего списка приложений. Если длина списка приложений превышает количество раз, которое выбранное деление появляется в исходном списке делений, функция ЕСЛИ не будет выполнять формулу ИНДЕКС / АГРЕГАТ.

Чтобы дать функции ЕСЛИ что-то для сравнения, выберите ячейку F4 и введите следующую «вспомогательную» формулу.

  = СЧЁТЕСЛИ (A5: A14, G4)  

Обновленная функция IF выполнит следующий тест.

  = ЕСЛИ (СТРОКИ ($ F $ 5: F5) <= $ F $ 4,   ИНДЕКС / АГРЕГАТ  , "")  

Эта формула проверяет постоянно расширяющийся диапазон, который начинается в ячейке F5, чтобы определить, превышает ли высота диапазона значение, предоставленное вспомогательной ячейкой F4. Обновленная формула будет выглядеть следующим образом.

 =  IF (ROWS ($ F $ 5: F5) <= $ F $ 4,  INDEX ($ B $ 5: $ B $ 14, AGGREGATE (15,3, ((A $ 5: $ A $ 14 = $ G $ 4)) / ($ A $ 5: $ A $ 14 = $ G $ 4) * ROW ($ A $ 5: $ A $ 14)) - ROW ($ A $ 4), ROWS ($ F $ 5: F5))) , "" ) 

Поскольку «Игра» имеет 4 записи в столбце «А», когда мы перейдем к 5-й итерации ^-го , функция СТРОКА в функции ЕСЛИ будет генерировать 5.Тест принимает вид «5 <= 4», что генерирует условие FALSE, тем самым отображая пустой текст, заключенный в две двойные кавычки.

БОНУС:

Если вам не нужна «вспомогательная» формула в ячейке F4, вы можете свернуть логику СЧЁТЕСЛИ в функцию ЕСЛИ. Обновленная формула будет выглядеть следующим образом.

 = ЕСЛИ (СТРОКИ ($ F $ 5: F5) <=  СЧЁТЕСЛИ ($ A $ 5: $ A $ 15, $ G $ 4) , ИНДЕКС ($ B $ 5: $ B $ 14, AGGREGATE (15,3, (($ A $ 5: $ A $ 14 = $ G $ 4) / ($ A $ 5: $ A $ 14 = $ G $ 4) * ROW ($ A $ 5: $ A $ 14)) - ROW ($ A $ 4), ROWS ($ F $ 5 : F5))), "") 

ПРИМЕЧАНИЕ. Когда СЧЁТЕСЛИ было во «вспомогательной» ячейке, мы не делали ни одну из ссылок абсолютными, потому что мы не повторяли формулу в любых других ячейках.Если мы сворачиваем логику СЧЁТЕСЛИ в функцию ЕСЛИ, нам нужно сделать ссылки абсолютными из-за повторяющегося характера формулы в столбце «G».

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