вторник, 22 мая 2012 г.

Число уникальных и повторяющихся значений

Пусть в диапазоне A1:A20 находятся какие-либо значения, не обязательно числовые.

Тогда следующая формула даст количество ячеек, встречающихся ровно один раз, т.е. уникальных:
=СУММ(ЕСЛИ(СЧЁТЕСЛИ(A1:A20;A1:A20)=1;1;0))

Количество повторяющихся ячеек, т.е. встречающихся более одного раза, можно посчитать так:
=СУММ(ЕСЛИ(СЧЁТЕСЛИ(A1:A20;A1:A20)>1;1;0))

А чтобы определить сколько различных повторяющихся:
=СУММ(ЕСЛИ(СЧЁТЕСЛИ(A1:A20;A1:A20)>1;1/СЧЁТЕСЛИ(A1:A20;A1:A20);0))

Ну и чтобы определить сколько вообще в диапазоне различных значений:
=СУММ(1/СЧЁТЕСЛИ(A1:A20;A1:A20))

 Все приведенные формулы используют массивы, поэтому после окончания ввода нужно нажать CTRL+SHIFT+ENTER.

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

Чтобы определить наиболее часто встречающееся значение используем функцию МОДА().

понедельник, 7 мая 2012 г.

Сиракузская последовательность

Возьмем любое натуральное число.
  1. Если оно четное, разделим его на 2, если нечетное - умножим на 3 и прибавим 1.
  2. С полученным числом проделаем пункт 1.
Есть гипотеза, утверждающая, что через конечное число шагов мы придем к последовательности 1 - 4 - 2 - 1 ...

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

Пусть в ячейке A1 исходное нечетное число. В ячейку A2 запишем такую формулу:

=НАИМЕНЬШИЙ(ЕСЛИ(ОСТАТ((A1*3+1);2^СТРОКА(ДВССЫЛ("1:16")));3*A1+1;(A1*3+1)/2^СТРОКА(ДВССЫЛ("1:16")));1)
Это формула содержит массивы, поэтому после окончания ввода нужно нажать CTRL+SHIFT+ENTER.

Указанная формула избавляет число 3*A1+1 от максимальной степени двойки.

Например, если в ячейке A1 число 53, то 3*53+1 = 160 = 25 * 5, то после применения формулы в ячейке A2 получим число 5.

Теперь можно скопировать формулу до строки 20, например, и изменяя первоначальное число в ячейке A1, увидеть что в итоге приходим к числу 1.