6 методов создать список неповторимых значений в Excel

Здравствуй почетаемый юзер!

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

Список неповторимых значений может быть создать 6-ю методами:

1. При помощи специальной функции;
2. При помощи расширенного фильтра;
3. При помощи формул;
4. При помощи сводных таблиц;
5. При помощи условного форматирования;
6. При помощи способностей макроса.

Создать список неповторимых значений при помощи специальной функции

Это весьма обычной метод для хозяев Excel выше 2007 версии как произвести отбор неповторимых значений. Для вас необходимо на вкладке «Данные», в разделе «Работа с данными», применять специальную команду «Удалить дубликаты».

В показавшемся диалоговом окне «Удалить дубликаты», вы выделяете те столбики, где нужно произвести отсев неповторимых значений и нажимаете «Ок». В случае, когда в выделенном спектре располагается и заголовок таблицы, то поставьте галочку на пт «Мои данные содержат заглавия», что бы вы случаем не удалили данные. Внимание! Когда вы будете создавать отсев неповторимых значений в таблице, где столбиков больше 2 и они взаимосвязаны информацией, Excel предложит для вас расширить спектр выбора, с чем вы должны, согласится, по другому будет нарушена логическая связь с иными столбиками.

Создать список неповторимых значений при помощи расширенного фильтра

Это также не непростой метод произвести отбор неповторимых значений в таблице. Употреблять этот инструмент может быть на вкладке «Данные», позже избрать «Фильтр», и в конце концов «Расширенный фильтр», этот путь подступает для Excel 2003, а вот обладатели наиболее молодых версий, от 2007 и выше стоит пройти по пути: «Данные» — «Сортировка и фильтр» — «Добавочно». Большой плюс этого метода в том, что вы сможете создать новейший список неповторимых значений в другом месте. Опосля возникновения диалогового окна «Расширенный фильтр», устанавливаем галочку напротив пт «Скопировать итог в другое пространство», позже указываем спектр с вашими данными в поле «Начальный спектр», по мере необходимости указываем аспект отбора, но для общего отсева поле оставляем пустым «Спектр критериев», в 3-ем поле «Поместить итог в спектр» указываем первую ячейку куда будут помещаться наши данные, отмечаем галочкой пункт «Лишь неповторимые записи» и жмем «Ок». Если же для вас не надо никуда переносить ваши данные, то просто установите флаг для пт «Фильтровать список на месте», данные не пострадают, произойдет наложение обычного фильтра.

Внимание! Если программка воспрещает для вас переносить отфильтрованные данные на иной лист, вы просто запустите «Расширенный фильтр» на том листе, куда для вас нужно перенести отобранные неповторимые значения.

Создать список неповторимых значений при помощи формул

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

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

=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»«)

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

=ЕСЛИ(МАКС(A1:A100)<СТРОКА(1:1);»»;ВПР(СТРОКА(1:1); A1:B100;2))

Эта формула перебирает весь спектр сверху вниз по столбику с номерами и все позиции значений с номерами переносит в, другую таблицу. Пример 2. Для вас необходимо создать неповторимый список значений, при всем этом, начальные, значение не меняются, а если изменять исходные данные, то и конечные данные будут изменены. Допустим у вас список данных в спектре А1:А9, где А1— это заголовок. А вот приобретенные данные будут выводиться в столбик С, а формула для этого будет таковая:

Видите ли, что тут была применена разметка формул массива, о этом свидетельствуют фигурные скобки, и таковой тип формул вводится в ячейку жарким сочетанием кнопок Ctrl+Shift+Enter. Опосля протяжки формулы, если вы увидите значение ошибки #ЧИСЛО, это свидетельствует о том, что неповторимые значения закончились. Для избегания данной нам ошибки есть возможность поменять и усовершенствовать формулу, сделав ее последующего вида для Excel 2007 и выше:

В этом случае, если у вас и выходит ошибка #ЧИСЛО, то система будет ставить пустые ячейки.

Создать список неповторимых значений при помощи сводных таблиц

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

• для вас необходимо выделить один либо парочку столбцов в таблице, и выбираете вкладку «Вставка», группа «Таблица», иконка «Сводная таблица»;
• в показавшемся диалоговом окне «Создание сводной таблицы» проводим перепроверку, верно ли указан спектр выделенных значений, ну либо устанавливаете иной источник данных;
• последующим шагом вы указываете пространство, куда необходимо расположить, вашу сводную таблицу, вероятны два варианты: на новейший листок либо уже на имеющийся.
• крайним шагом необходимо подтвердить создание сводной таблицы, нажав клавишу «Ок».

Далее уже сводная таблица отбирает все неповторимые значения в полном объеме либо уже по выставленным условиям.

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

Этот метод я тоже не буду причислять к суровому либо сложному. Для использования условного форматирования для отбора неповторимых значений для вас необходимо перейти на вкладку «Основная», далее в группе «Стили» избрать клавишу меню «Условное форматирование» в предложенном перечне нам нужен пункт «Создать правило». В открывшемся диалоговом окне «Создание правила форматирования» активируем пункт «Употреблять формулу для определения форматируемых ячеек» и в поле «Поменять описание правила» вводим нашу формулу-условие:

=ПОИСКПОЗ(D2;$D$2:$D$9;0)=СТРОКА(D2) -СТРОКА($D$1)

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

Вот и всё, если условия выполнены вы и получите нужные для вас значения.

Создать список неповторимых значений при помощи способностей макроса

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

А на этом у меня всё! Я весьма надеюсь, что сейчас вы умеете создавать собственный неповторимый список. Буду весьма признателен за оставленные комменты, потому что это показатель читаемости и побуждает на написание новейших статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить создателя!

С ростом богатства вырастают и заботы. Гораций

Автозаполнение ячеек в Excel

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

Автозаполнение ячеек в Excel

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

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

Пишем в ячейке А1 слово « Январь » (естественно без кавычек и можно с строчный буковкы) и щелкаем мышкой по кнопочке « Enter» (либо галочка на панели инструментов в Excel).

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

Указатель мыши должен поменяться с белоснежного креста на небольшой темный крестик. Как это произойдет, нажмите на левую кнопку мыши и, не отпуская её, переместите указатель на ячейку А12 . У вас получиться вот таковая рамочка.

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

Сейчас попробуем создать список данных в Excel по дате.

Напишем в ячейке В1 дату – 12.01.2012 – и, установив указатель мыши на маркер наполнения , выделим спектр ячеек В1:В12 .

Как пронумеровать строчки в Excel

Если вы желаете просто пронумеровать строчки в колонке, то напишите в ячейке цифру 1 , и выделите нужный спектр ячеек с помощью маркера наполнения и нажатой клавиши Ctrl (на клавиатуре). Нажимая кнопку Ctrl , вы даете команду наращивать цифру на единицу.

Таковым же образом вы сможете пронумеровать столбцы в Excel.

Как задать список нечетных либо четных чисел

• Для этого напишите в ячейке D3 число (допустим – 1 ), а в ячейке D4 – число – 3 .
• Выделите ячейки D3 и D4 .
• Позже установите указатель мыши на маркер наполнения (небольшой темный квадратик в нижнем правом углу).
• Удерживая нажатой кнопку Ctrl , опускаем выделение до ячейки D12 .

• В итоге у вас возник список нечетных чисел.

Как создать собственный список автозаполнения

Чтоб создать свой список автозаполнения, нужно создать последующее:

Для Excel 2003

• Войдите в меню – Сервис – Характеристики – Списки –
  1. в большенном правом поле напишите новейший список, нажимая на кнопку Enter опосля ввода всякого пт;
  2. щелкните по кнопочке – Добавить — (Add) ;
  3. закройте окно клавишей – ОК—.

Для Excel 2010

• Изберите на ленте – Файл – Характеристики – Добавочно – клавиша Поменять списки…
  

• в большенном правом поле напишите новейший список, нажимая на кнопку Enter опосля ввода всякого пт и закройте окно клавишей – ОК -.

Сейчас вы сможете с помощью автозаполнения ячеек ввести собственный список.

Совет

Если у вас нет маркера автозаполнения (малеханького темного квадратика в правом нижнем углу на рамке выделения), то:

В Excel 2003

Войдите в меню – Сервис – Характеристики – перейдите на вкладку наверху – Характеристики – и установите флаг на Перетаскивание ячеек .

В Excel 2010

Изберите на ленте – Файл – Характеристики – Добавочно – и установите флаг на Разрешить маркеры наполнения и перетаскивание ячеек .

Создаем список дел в MS Excel

Сейчас мы научимся создавать в MS Excel “чеклист” либо, если по-русски, список дел. Список дел в Excel – с сиим вы наверное уже сталкивались в попытках организовать работу. Обычно люди выделяют цветом изготовленные/несделанные дела, “зачеркивают” пункты перечня… но все это очень длительно, ну и просто неловко.

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

Расставляем флажки в MS Excel

Поначалу сделаем простейшую таблицу со перечнем дел. Потом перейдем на вкладку “Разраб” и в группе “Элементы управления” активируем инструмент “Вставка” и выберем пункт “Флаг”. Как флаг окажется помещенным на листе, сотрите ненадобную подпись и поставьте его в избранную ячейку.

Внедрение “флагов” в Excel

Сейчас протяните ячейку с флагом за уголок до конца “перечня дел”, и такие же “флажки” покажутся во всех других ячейках столбца. Итак шаблончик грядущего перечня дел готов – можно даже щелкать по флажкам уже на данный момент, расставляя галочки… лишь вот наслаждения и полезности от этого никакого, правильно? Давайте её добавим!

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

Заготовка перечня дел в MS Excel

Список дел с зависимостями в MS Excel

Щелкаем правой клавишей мыши по первому элементу-флажку, и избираем самый нижний пункт из выпадающего меню: “Формат объекта”.

Формат ячеек в MS Excel

В показавшемся окне “Форматирование объекта” щелкните в поле “Связь с ячейкой”, а потом, щелкните на соседнюю ячейку справа от текущей. Этот же фокус необходимо сделать для всех других флагов, создав пары с примыкающей ячейкой.

Создаем связь с ячейкой в MS Excel

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

Сейчас активация флажков-галочек меняет состояние в связанных с ними ячейках таблицы

Функция СЧЕТЕСЛИ

Ну а сейчас немого функций и арифметики. В “список дел” я добавляю ещё 2 строчки: число задач совершенно, и число задач, которые уже выполнены. Обе строи заполняются данными автоматом, а помогает им в этом интегрированная Excel-функция СЧЕТЕСЛИ. Работает она просто:

=СЧЁТЕСЛИ( диапазон_ячеек ; критерий_работы )

Как посчитать все выполненные задачки? Задать в качестве аспекта работы функции положение “ИСТИНА” в третьей колонке (как вы помните, положение зависит от того – стоит галочка либо нет).

Как посчитать все задачки (и выполненные и невыполненные) сходу? Поначалу посчитать по отдельности количество значений “ЛОЖЬ” и “ИСТИНА”, а потом сложить итог.

Считаем задачки в нашем excel-списке дел

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

Итоговый список задач в MS Excel

Сперва скройте столбец “С” (правда/ересь), чтоб он не загромождал вид таблицы. А во вторых, давайте добавим “общий аспект эффективности”, другими словами ещё одно поле – “можно ли перебегать к последующему плану?”. Ёжику понятно, что перебегать к последующему плану можно лишь опосля того, как выполнены все задания сегодняшнего. Либо, перефразируя с языка логики на язык арифметики: когда пункты “всего задач” и “задач выполнено” нашего перечня, равны друг дружке!

Воспользуемся обычной функцией условием =ЕСЛИ() и зададим начальные данные:

Расшифровывается это просто: если значения равны, то вывести в ячейке слово “ДА”, если не равны, “НЕТ”. А итог будет смотреться как у меня на примере ниже.

Готовый список задач изготовленный в MS Excel. Практически вполне автоматический и от того ещё наиболее увлекательный

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

12 обычных приёмов для ускоренной работы в Excel

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

2. Секундное наполнение (Flash Fill)

Представим, у вас есть список полных ФИО (Иванов Иван Иванович), которые для вас нужно перевоплотить в сокращённые (Иванов И. И.). Чтоб создать это, необходимо просто начать писать хотимый текст в примыкающем столбце вручную. На 2-ой либо третьей строке Excel попробует предвидеть наши деяния и выполнит последующую обработку автоматом. Остается лишь надавить кнопку Enter для доказательства, и все имена будут преобразованы одномоментно. Схожим образом можно извлекать имена из email, склеивать ФИО из фрагментов и так дальше.

3. Копирование без нарушения форматов

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

Если избрать опцию «Копировать лишь значения» (Fill Without Formatting), то Excel скопирует вашу формулу без формата и не будет портить оформление.

4. Отображение данных из таблицы Excel на карте

В Excel можно стремительно показать на интерактивной карте ваши геоданные, к примеру реализации по городкам. Для этого необходимо перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин «Карты Bing» (Bing Maps). Это можно создать и по прямой ссылке с веб-сайта, нажав клавишу Get It Now.

В Telegram-канале «Лайфхакер» лишь наилучшие тексты о разработках, отношениях, спорте, кино и многом другом. Подписывайтесь!

В нашем Pinterest лишь наилучшие тексты о отношениях, спорте, кино, здоровье и многом другом. Подписывайтесь!

Опосля прибавления модуля его можно избрать в выпадающем перечне «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Остается выделить ваши ячейки с данными и надавить на клавишу Show Locations в модуле карты, чтоб узреть наши данные на ней. При желании в настройках плагина можно избрать тип диаграммы и цвета для отображения.

5. Резвый переход к подходящему листу

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

6. Преобразование строк в столбцы и назад

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

1. Выделите спектр.
2. Скопируйте его (Ctrl + C) либо, нажав на правую клавишу мыши, изберите «Копировать» (Copy).
3. Щёлкните правой клавишей мыши по ячейке, куда желаете вставить данные, и изберите в контекстном меню один из вариантов специальной вставки — значок «Транспонировать» (Transpose). В старенькых версиях Excel нет такового значка, но можно решить делему при помощи специальной вставки (Ctrl + Alt + V) и выбора функции «Транспонировать» (Transpose).

7. Выпадающий список в ячейке

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

1. Выделите ячейку (либо спектр ячеек), в которых обязано быть такое ограничение.
2. Нажмите клавишу «Проверка данных» на вкладке «Данные» (Data → Validation).
3. В выпадающем перечне «Тип» (Allow) изберите вариант «Список» (List).
4. В поле «Источник» (Source) задайте спектр, содержащий эталонные варианты частей, которые и будут потом выпадать при вводе.

8. Умная таблица

Если выделить спектр с данными и на вкладке «Основная» надавить «Форматировать как таблицу» (Home → Format as Table), то наш список будет преобразован в умную таблицу, которая умеет много полезного:

1. Автоматом растягивается при дописывании к ней новейших строк либо столбцов.
2. Введённые формулы автоматом будут копироваться на весь столбец.
3. Шапка таковой таблицы автоматом закрепляется при прокрутке, и в ней врубаются клавиши фильтра для отбора и сортировки.
4. На показавшейся вкладке «Конструктор» (Design) в такую таблицу можно добавить строчку итогов с автоматическим вычислением.

9. Спарклайны

Спарклайны — это нарисованные прямо в ячейках маленькие диаграммы, наглядно отображающие динамику наших данных. Чтоб их создать, нажмите клавишу «График» (Line) либо «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите спектр с начальными числовыми данными и ячейки, куда вы желаете вывести спарклайны.

Опосля нажатия на клавишу «ОК» Microsoft Excel создаст их в обозначенных ячейках. На показавшейся вкладке «Конструктор» (Design) можно добавочно настроить их цвет, тип, включить отображение малых и наибольших значений и так дальше.

10. Восстановление несохранённых файлов

Представьте: вы закрываете отчёт, с которым возились последнюю половину денька, и в показавшемся диалоговом окне «Сохранить конфигурации в файле?» вдруг для чего-то жмёте «Нет». Кабинет озвучивает ваш истошный крик, но уже поздно: несколько крайних часов работы пошли псу под хвост.

По сути есть шанс поправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» → «Крайние» (File → Recent) и найдите в правом нижнем углу экрана клавишу «Вернуть несохранённые книжки» (Recover Unsaved Workbooks).

В Excel 2013 путь мало иной: «Файл» → «Сведения» → «Управление версиями» → «Вернуть несохранённые книжки» (File — Properties — Recover Unsaved Workbooks).

В следующих версиях Excel следует открывать «Файл» → «Сведения» → «Управление книжкой».

Раскроется особая папка из недр Microsoft Office, куда на таковой вариант сохраняются временные копии всех сделанных либо изменённых, но несохранённых книжек.

11. Сопоставление 2-ух диапазонов на отличия и совпадения

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

1. Выделите оба сравниваемых столбца (удерживая кнопку Ctrl).
2. Изберите на вкладке «Основная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения» (Home → Conditional formatting → Highlight Cell Rules → Duplicate Values).
3. Изберите вариант «Неповторимые» (Unique) в раскрывающемся перечне.

12. Подбор (подгонка) результатов расчёта под нужные значения

Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтоб получить на выходе подходящий итог? В такие моменты ощущаешь себя матёрым артиллеристом: всего-то пара 10-ов итераций «недолёт — перелёт» — и вот оно, давно ожидаемое попадание!

Microsoft Excel сумеет создать такую подгонку за вас, причём резвее и поточнее. Для этого нажмите на вкладке «Данные» клавишу «Анализ „что если“» и изберите команду «Подбор параметра» (Insert → What If Analysis → Goal Seek). В показавшемся окне задайте ячейку, где желаете подобрать необходимое значение, хотимый итог и входную ячейку, которая обязана поменяться. Опосля нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтоб подобрать требуемый вами результат с точностью до 0,001.

Если этот обзор охватил не все полезные фишки MS Excel, о которых вы понимаете, делитесь ими в комментах!