Найти совпадения в столбцах в Excel

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

Прием №1 — «Горячие клавиши»

Изучите «горячие клавиши»! Умелое применение «горячих клавиш» позволит Вам сэкономить значительное время при работе с таблицами Excel. Скачайте любой список «горячих клавиш» из 3-х вариантов, представленных ниже.

Вариант 1 ~ Вариант 2 ~ Вариант 3

Прием №2 — «Скрытие листов книги Excel»

Чтобы скрыть от посторонних глаз один из листов книги Excel существует два способа решения этого вопроса. Вариант 1: На ленте Главная в разделе Ячейки выберите Формат → Видимость: Скрыть или отобразить → Скрыть лист

Вариант 2: Щелкните правой кнопкой мыши на ярлыке нужного листа и выберите Скрыть.

Прием №3 — «Выравнивание фигур и объектов»

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

Прием №4 — «Отключение отображения ошибок в формулах»

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

Прием №5 — «Сохранение настроек фильтра»

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

Прием №6 — «Поиск формул в ячейках»

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

Прием №7 — «Быстрая очистка форматов»

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

Прием №8 — «Перемещение диаграммы»

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

Прием №9 — «Создаем динамический микро-график»

Чтобы создать динамический (изменяющийся совместно с источником) микро-график из вашего обычного графика, используйте инструмент Камера. Чтобы добавить камеру на панель быстрого доступа выполните Файл → Параметры → Панель быстрого доступа. Выберите Все команды → Камера и добавьте инструмент на панель.

Прием №10 — «Объединение типов диаграмм»

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

Прием №11 — «Изменение порядка элементов»

Чтобы изменить порядок элементов в диаграмме, просто щелкните мышью на Y-оси, нажмите Ctrl+1, и установите флажки Обратный порядок категорий и Вертикальная ось пересекает в максимальной категории.

Прием №12 — «Изменяем символ маркера на диаграмме»

Для изменения символа маркера или «пузырей» в диаграмме на вашу любую форму или картинку, просто нарисуйте в любом месте листа с помощью панели инструментов Рисование форму или вставьте картинку, а затем скопируйте её, нажав Ctrl+С, затем перейдите к диаграмме, выберите маркеры (или пузырьки) и нажмите Ctrl+V.

Прием №13 — «Решаем проблемы с визуализацией данных»

Чтобы уберечь себя от различных проблем с визуализацией данных, старайтесь избегать таких диаграмм: объемные графики с плоскостями (без накопления), лепестковые диаграммы, 3D линии, 3D гистограммы с несколькими рядами данных, кольцевые графики с более чем двумя рядами данных.

Прием №14 — «Улучшаем внешний вид диаграммы»

Выполните следующие 6 шагов для улучшения внешнего вида вашей диаграммы.

1. Удалите все вертикальные линии сетки;
2. Измените цвет горизонтальной линии сетки от черного до очень светлого оттенка;
3. Настройте цвета рядов диаграммы, чтобы получить лучшую контрастность;
4. Подберите походящий размер шрифта;
5. Добавить подписи данных и удалите все оси (или подписи осей), если это необходимо;
6. Удалите цвет фона диаграммы.

Прием №15 — «Получаем имя»

Чтобы получить имя из полного имени, отчества и фамилии («Иван» из «Иван Иванович Иванов») используйте формулу — =ЛЕВСИМВ("Ф.И.О.";НАЙТИ(" ";"Ф.И.О.")-1).

Прием №16 — «Рассчитываем выплаты по кредиту»

Чтобы рассчитать выплаты по кредиту: ПЛТ(процентная_ставка;количество_выплат;сумма_кредита).

Прием №17 — «Ищем наибольшее число»

Чтобы получить N-ное наибольшее число в диапазоне: НАИБОЛЬШИЙ(диапазон;N).

Прием №18 — «Ищем наименьшее число»

Чтобы получить N-ное наименьшее число в диапазоне: =НАИМЕНЬШИЙ(диапазон;N).

Прием №19 — «Считаем количество слов»

Подсчитать количество слов в ячейке: =ДЛСТР(СЖПРОБЕЛЫ(текст))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(текст);" ";""))+1.

Прием №20 — «Считаем положительные значения»

Для подсчета количества положительных значений в диапазоне: =СЧЁТЕСЛИ(диапазон;">0").

Прием №21 — «Удаляем пробелы»

Для удаления лишних пробелов в тексте: =СЖПРОБЕЛЫ(текст).

Прием №22 — «Считаем возраст»

Чтобы узнать возраст человека на основе даты рождения, например в формате 39 л., 6 мес., 25 дн., воспользуйтесь недокументированной функцией РАЗНДАТ(): =РАЗНДАТ(дата_рождения;СЕГОДНЯ();"y")&"л.," & РАЗНДАТ(дата_рождения;СЕГОДНЯ();"ym")&" мес., " & РАЗНДАТ(дата_рождения;СЕГОДНЯ();"md")&" дн.".

Прием №23 — «Получаем обычные дроби»

Чтобы получить обычную дробь от числа (например, 1/8 из 0,125): =ТЕКСТ(десятичная_дробь;"?/?").

Прием №24 — «Находим частичные совпадения»

Чтобы найти частичные совпадения с помощью функции ВПР: =ВПР(«абв*»; диапазон_просмотра; искомый столбец).

Прием №25 — Делаем начальные буквы прописными»

Чтобы сделать все начальные буквы прописными в тексте (например, получить «Иван Иванович Иванов» из «иван иванович иванов» или из «ИВАН ИВАНОВИЧ ИВАНОВ»): =ПРОПНАЧ(текст).

Прием №26 — «Отладка формул»

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

Прием №27 — «Сохраняем нули при вводе данных»

Если вам нужно сохранить начальные нули при вводе данных (например, если вы используете артикул товара в виде 000324 и при вводе артикул превращается в 324), примените к ячейке формат «Текстовый».

Прием №28 — «Формат для ввода номера телефона»

Для отображения в ячейке телефонного номера в международном формате (например, (+7)-123-456-78-90) используйте пользовательский формат вида «(+0)-000-000-00-00».

Прием №29 — «Строим последовательност дат»

Если вам нужно построить последовательность дат или дней недели воспользуйтесь диалоговым окном Прогрессия. Введите начало прогрессии (январь, янв, понедельник, пн и т.п.) и выполните: Главная → Заполнить (группа Редактирование) → Прогрессия → Автозаполнение.

Прием №30 — «Ввод данных в несколько листов одновременно»

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

Прием №31 — «Перенос текста в ячейке по строкам»

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

Прием №32 — «Используем автозамену»

Если часто приходится вводить длинные названия организаций и т.п. (например, «Московский центр подготовки, переподготовки и повышения квалификации специалистов водного транспорта»), с помощью настройки параметров автозамены можно сохранить значительное количество времени. Выполните: Файл → Параметры → Правописание → Параметры автозамены. Введите в поле «Заменять:» — москвацентр, в поле «на:» — полное наименование. После ввода в ячейку «москвацентр» Excel автоматически изменит текст на полное наименование из нашего примера (для себя вы, конечно же, выберите другие значения).

Прием №33 — «Используем Буфер обмена»

Если вы часто копируете данные из одной книги в другую (или даже в другое приложение) воспользуйтесь «Буфером обмена». Буфер может хранить до 24 объектов, и доступен во всех приложениях Microsoft Office. Вызвать буфер обмена можно на ленте Главная.

Прием №34 — «Расположение файлов по умолчанию»

Чтобы изменить папку, в которую Excel предлагает сохранять файлы (обычно по умолчанию это папка «Мои документы») выполните Файл → Параметры → Сохранение и укажите нужную папку в поле Расположение файлов по умолчанию.

Прием №36 — «Перемещаемся по ячейкам»

Чтобы переместиться к последней непустой ячейке столбца нажмите Ctrl+↓, к последней непустой ячейке строки – Ctrl+→, к первой непустой ячейке столбца Ctrl+↑, в строке — Ctrl+←.

Прием №37 — «Сужаем область поиска»

Чтобы сузить область поиска значений (только в определенном диапазоне) выделите необходимый диапазон и нажмите Ctrl+F.

Прием №38 — «Контролируем ввод данных»

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

Прием №39 — «Проверяем введенные данные»

Чтобы выделить данные в ячейках не соответствующие какому-либо критерию, создайте проверку данных (вкладка Данные → Проверка данных (группа Работа с данными) и выберите Обвести неверные данные.

Прием №40 — «Форматируем часть ячейки»

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

Прием №41 — «Быстро копируем данные в ячейки»

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

Прием №42 — «Используем пользовательское представление данных»

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

Прием №43 — «Вставляем документ Word»

Для вставки на лист Excel документа Word выполните: лента Вставка → Объект (группа Текст) → вкладка Из файла и выберите нужный документ Word. Для того чтобы объект автоматически изменялся при изменении документа источника установите галочку Связь с файлом. Для редактирования объекта непосредственно в Excel дважды щелкните на объекте.

Прием №44 — «Копируем ячейки без прорессии»

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

Прием №45 — «Присваиваем имя диапазонам и ячейкам»

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

Прием №46 — «Форматируем несколько ячеек одинаковым форматом»

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

Прием №47 — «Легкий способ посмотреть формулы в таблице»

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

Прием №48 — «Используем однотипное форматирование»

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

Прием №49 — «Открываем книгу Excel автоматически»

Если вы работаете преимущественно с одной книгой Excel, вы можете настроить программу так, что при запуске Excel эта книга будет отрываться автоматически. Выполните Файл → Параметры → Дополнительно → Общие. Укажите в поле Каталог автозагрузки нужную папку и поместите книгу туда. Имейте в виду, если в указанной папке хранится несколько файлов, Excel будет пытаться открыть их все. Если вы используете книгу как шаблон, имеет смысл присвоить книге атрибут Только для чтения. Рис 49

Прием №50 — «Создаем шаблон Excel»

Если вы создаете много книг имеющих одинаковую базовую структуру, создайте на её основе шаблон. При сохранении книги выберите в окне Сохранение документа тип файла Шаблон Excel. Убедитесь, что в верхней части окна указана папка Шаблоны. Сохраните шаблон под нужным вам именем. Теперь при выполнении команды Файл → Создать, вы можете выбрать ваш шаблон.