rss
  •  
Обучение Microsoft Excel: от основ до PowerBI

Добавление столбца с автоматической нумерацией в таблицу

| Категория: Приемы и советы, Работа с табличными массивами |

3

Рано или поздно возникает необходимость иметь в таблице автоматическую нумерацию записей (строк). В Word эта проблема решается просто – путем добавления нумерации любого вида: римскими или арабскими цифрами. В Excel нет таких возможностей. Однако использование структурированных таблиц и функции СТРОКА [ROW] из категории “Ссылки и массивы”, позволяет легко решить эту задачу.

1). Обычный диапазон ячеек должен быть преобразован в табличный вид, например, используя комбинацию клавиш Ctrl+T.

2) Для удобства работы таблицу можно переименовать – для этого на вкладке Конструктор [Design] в группе Свойства [Properties] в поле Имя таблицы [Table Name] ввести имя (без пробела) и нажать Enter. Например, ТаблицаСтраховки.

3) В 1-ю ячейку поля № ввести формулу:

=СТРОКА()-СТРОКА(ТаблицаСтраховки)+1 или

=ROW()-ROW(ТаблицаСтраховки)+1 для англоязычной версии.

Формула автоматически будет скопирована по всему полю.

Нумерация готова!

Если необходимо нумерацию сделать римскими цифрами, то следует в начало формулы добавить функцию РИМСКОЕ [ROMAN]:

=РИМСКОЕ(СТРОКА()-СТРОКА(ТаблицаСтраховки)+1) или

=ROMAN(ROW()-ROW(ТаблицаСтраховки)+1) для англоязычной версии.

 

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

| Категория: Приемы и советы, Формулы и функции |

8

Для определение количества уникальных значений возможно по следующему сценарию: удалить дубликаты в копии диапазона, а затем воспользоваться функцией для подсчета количества значений с помощью функции СЧЁТЗ [COUNTA]. Однако, если исходные данные постоянно изменяются, то подобный сценарий придется повторять снова и снова.

И один из вариантов – это использовать формулы.

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

ЧАСТОТА(массив_данных;массив_интервалов)
Массив_данных — массив или ссылка на множество значений, для которых вычисляются частоты.
Массив_интервалов – массив или ссылка на множество интервалов, в которые группируются значения аргумента “массив_данных”.

{=СУММ(ЕСЛИ(ЧАСТОТА(A:A;A:A)>0;1))} или  {=SUM(IF(FREQUENCY(A:A;A:A)>0;1))}

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

Примечание: функция ЧАСТОТА пропускает пустые ячейки и текст.

 

 

Почему даты в фильтре не группируются?

| Категория: Приемы и советы, Работа с табличными массивами |

8

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

Однако, начиная с 2007 версии, в полях с типом данных дата, происходит автоматическая группировка в годы, месяцы, дни. Так настроена программа изначально. Однако, если настройку изменить, то будет снова “обычная” дата. Как это изменить?

Необходимо выбрать:

1) Файл [File] – Параметры [Options].

2) Выбрать Дополнительно [Advanced] и в разделе Параметры отображения книги [Display options for this workbook] поставить флажок Группировать даты в меню автофильтра [Group dates in the AutoFilter menu]

3) ОК.