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

Как быстро вставить уникальные порядковые номера в Excel?

Полезные функции для работы в Google Таблицах

Полезные функции для работы в Google Таблицах

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

Знакомимся с Google Docs

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

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

Все приведенные ниже примеры выдуманы.

Как сделать документ Google Таблиц быстрее и «легче»

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

  • Удалите неиспользуемые строки на каждой вкладке (по умолчанию создается тысяча строк — если у вас сейчас 200 строк на вкладке, удалите лишние 800 строк и при необходимости добавьте нужное вам количество) и столбцы (аналогично).
  • Оптимизируйте количество вкладок (если есть несколько вкладок с небольшими таблицами или списками — попробуйте объединить их в одну). VPR/VLOOKUP, INDEX/ИНДЕКС, SEARCH/MATCH и другие, попробуйте сохранить некоторые формулы как значения (если вам не нужно обновлять эти значения). Например, если вы извлекаете данные за несколько месяцев с помощью VLOOKUP, оставьте текущий месяц в формуле, а остальные данные сохраните как значения.
  • Не заполняйте строки/столбцы цветами (и вообще старайтесь избегать чрезмерного форматирования).
  • Проверьте, применяется ли условное форматирование к (чрезмерно) большому диапазону ячеек.
  • Не применяйте фильтр ко всем столбцам.
  • Уберите аннотации, если их много и они не нужны
  • Проверьте, проверяются ли данные в большом диапазоне ячеек.
Читайте так же:
Как вернуться к последнему активному листу в Excel?

Как выделить уникальные элементы из списка в Google Таблицах?

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

Подсчитать уникальные элементы в списке легко с помощью функции COUNTUNIQUE. Разница в том, что она возвращает только количество уникальных элементов. Как быть в ситуации, когда исходный список со временем изменяется (в него добавляются новые строки)? Формулы не могут изменяться постоянно. Есть решение. При обновлении исходного списка следует использовать функцию UNIQUE для автоматического обновления списка уникальных значений (и функцию COUNTUNIQUE для обновления количества). Для этого в качестве аргумента укажите диапазон A2:A14, а не A2:A.

Ставим ссылки автоматически. Функция HYPERLINK (ГИПЕРССЫЛКА)

источник

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

99-0-создание уникального списка лого

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

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

99-1-список сотрудников

Формула для массива будет выглядеть так:

=ЕСЛИОШИБКА(ИНДЕКС(Список;ПОИСКПОЗ(СУММ(СЧЁТЕСЛИ(B$1:B1; Список));СЧЁТЕСЛИ(Список;»<«& Список);0));»»)

В данном случае Список представляет собой именованный диапазон ячеек A2:A100.

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

Чтобы превратить формулу в формулу массива, скопируйте ее в ячейку B2, затем нажмите Ctrl+Shift+Enter. Перетащите формулу массива в ячейку B20.

Формула следующая:

  1. СЧЁТЕСЛИ(Список;»<«& Список) – возвращает массив (1) с количеством сотрудников, которые меньше сотрудника, находящегося в текущей ячейке. Т.е. массив будет иметь вид <5; 20; 19; 21; 12 …>, например, Дима Билан имеет значение 5, это значит, что перед ним имеется еще 4 сотрудника, которые расположены раньше по алфавиту, Филипп Киркоров = 20, значит перед ним имеется еще 19 сотрудников расположенных ближе к букве А. Таким образом, мы получаем что-то наподобие отсортированного листа.
  2. СЧЁТЕСЛИ(B$1:B…; Список) – возвращает массив (2) с единицами для тех позиций, которые уже имеются на отсортированном листе. Например, в ячейке B2 будет массив <0;0;0;0;0…>, а в ячейке B8 — <0;1;0;0;0; … ;0;1>– так как Валерий Леонтьев встречается два раза.
  3. СУММ – суммирует значения, которые уже были представлены на отсортированном списке.
  4. ПОИСКПОЗ – ищет сумму встречающихся значений массива (2) в массиве (1)
  5. ЕСЛИОШИБКА – скрывает #Н/А, когда расчеты достигают конца списка.

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

Загрузить файл примера можно здесь.

Вам также могут быть интересны следующие статьи

20 комментариев

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

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

Добрый день. Очень нужная формула, но она не работает с числами.

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

Формула не работает.

Все работает, кавычки замените!
Подскажите, что надо изменить что бы работала для горизонтальных строк данных, вместо вертикальных

Например, что если вы хотите создать список уникальных ячеек в диапазоне B2:H15?

формула массива — это фантастика. Большое спасибо!

Я уже очень долго ищу решение для поставленной задачи и только у вас наконец удалось найти подходящую формулу.
Правда я всё равно не понимаю как она работает. При вычислении кусков формулы через F9 она выдаёт ошибки.
Кроме того, мне на листе приходится использовать несколько таких формул. После того как я заполнил несколько столбцов оказалось, что значения в этих ячейках по непонятным причинам пересчитываются, то пропадают, то меняются местами. И видно, что эти вычисления занимают существенное время.
Может быть эту формулу можно упростить? И наверно как-то привязать к первому значению.

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

Формула не работает для меня. Во всех ячейках нули. Я заменил кавычки.

Согласен — 0 везде, при этом в файле демонстрационном как-то считает

Сценарий работает. Должен быть заголовок столбца, иначе он не работает

Перерыла много статей на эту тему, но эта статья лучшая! Формула работает. Да еще по алфавиту сортирует. (У кого не работает — сделайте пару действий:
1. СЧЁТЕСЛИ(B$1:B1; Список) — уберите лишний пробел перед именем диапазона Список;
2. Замените кавычки » на «;
ну и по мелочи… имя диапазона и ссылку на заголовок того столбца, где будет результат.
Я Нуб, поэтому так подробно)) А за помощь — ОГРОМНОЕ спасибо.

Он возвращает 0, если в списке есть пустые строки. Подскажите, как настроить формулу, чтобы она также читала массив с пустыми строками?

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