Треугольник Серпинского

 Треугольник Серпинского представляет из себя множество точек плоскости, построенное следующим образом:

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

Выглядит процесс так:

Суммирование каждой n-й строки

Предположим в ячейках A1:A14 у нас находятся некоторые числа и мы хотим просуммировать, например, только нечетные строки. Тогда проще всего воспользоваться следующей формулой:

=СУММПРОИЗВ(A1:A14;ОСТАТ(СТРОКА(A1:A14);2))

а для суммирования только четных такой:

=СУММПРОИЗВ(A1:A14;ОСТАТ(СТРОКА(A1:A14)+1;2))

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

Сравнение случайностей

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

Потом он на 15 минут выходит из аудитории, а по возвращении находит на столе стопку ответов. И тогда профессор говорит студентам, что сразу найдет среди результатов настоящий – с записью реальных результатов подбрасывания монетки.

И каждый год это у него получается. В чем здесь секрет?

Функция Капрекара

Возьмем число в котором не все цифры одинаковые.

Составим два новых: наибольшее возможное из цифр исходного числа и наименьшее возможное. Вычтем из большего меньшее.

Произведенные действия называют функцией Капрекара, названной в честь индийского математика открывшего интересное свойство числа 6174.

Применив функцию Капрекара к числу 6174 мы вновь получим число 6174. Более того, если исходное число четырехзначное, то через конечное число шагов мы придем к числу 6174.

Чтобы в Excel применить функцию Капрекара составим в ячейке B1 такую формулу (в ячейке A1 у нас исходное число):

=СУММПРОИЗВ(НАИБОЛЬШИЙ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:" & ДЛСТР(A1)));1)*1;СТРОКА(ДВССЫЛ("1:" & ДЛСТР(A1))))-НАИМЕНЬШИЙ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:" & ДЛСТР(A1)));1)*1;СТРОКА(ДВССЫЛ("1:" & ДЛСТР(A1))));10^(ДЛСТР(A1)-СТРОКА(ДВССЫЛ("1:" & ДЛСТР(A1) ))))

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

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

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

Пусть в диапазоне 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.

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

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

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

Возьмем любое натуральное число.

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 = 2⁵ * 5, то после применения формулы в ячейке A2 получим число 5.

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

Скатерть Улама

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

Попробуем составить скатерь Улама в Excel. При этом воспользуемся VBA.

Располагая числа по спирали

обозначим перемещения таким образом:

• П - вправо
• В - вверх
• Л - влево
• Н - вниз

Уникальный идентификатор списка

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

Сделать это можно следующим образом.

Первому элементу присваиваем номер 1.

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

Чтобы проверить первое ли это появление элемента используем комбинацию функций ЕНД(ПОИСКПОЗ()).

Если это первое появление, то ПОИСКПОЗ() вернет #Н/Д, а функция ЕНД(#Н/Д) вернет ИСТИНА и мы смело присваиваем элементу номер МАКС() среди верхних идентификаторов плюс 1.

Если это не первое появление, то ЕНД(ПОИСКПОЗ()) возвращает ЛОЖЬ и мы через функцию ВПР() находим уже имеющийся у элемента идентификатор.

В конечном итоге получаем такую формулу (для второго элемента):
=ЕСЛИ(ЕНД(ПОИСКПОЗ(C3;C$2:C2;0));МАКС(D$2:D2)+1;ВПР(C3;C$2:D2;2;ЛОЖЬ))

Дальше просто копируем эту формулу.

Месяц, квартал, полугодие

Пусть в ячейке A1 находится дата в числовом формате.

Тогда чтобы определить месяц, воспользуемся следующей формулой:
=МЕСЯЦ(A1)

Чтобы определить квартал года по дате пишем:
=ОКРУГЛВВЕРХ(МЕСЯЦ(A1)/3;0)

Для определения полугодия:
=ОКРУГЛВВЕРХ(МЕСЯЦ(A1)/6;0)

В последних двух примерах использована функция ОКРУГЛВВЕРХ(), которая округляет число до ближайшего большего по модулю.

Редактирование формул

При редактировании формулы в окне "присвоение имени" или в строке формул возможны 2 состояния:
режим "правки" и режим "ввод".

При режиме "ввод" нажатие клавиш влево, вправо и прочее вызывает перемещение по активному листу книги, а при режиме "правка" - перемещение по самой строке формул.

Особенность функции СУММ

При использовании функции СУММ() пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются, а учитываются только числа.

Благодаря этому получаем следующее:

Во втором случае получаем ошибку.

Функция МОДА

Функция МОДА() возвращает наиболее часто встречающееся числовое значение указанного диапазона. Если несколько значений встречается наиболее часто и одинаковое количество раз, то вернется первое из этих значений. Не числовые значения игнорируются.

Удобнее эту функцию использовать для проверки диапазона на уникальность значений.

Т.е. у нас есть диапазон, в котором каждое число должно встречаться ровно один раз. Тогда функция МОДА(диапазон) вернет ошибку #Н/Д если значения уникальны.

В Excel 2010 функция МОДА() оставлена для совместимости с предыдущими версиями и добавлены две новые функции МОДА.НСК(), возвращающая массив наиболее часто встречающихся значений, и МОДА.ОДН(), возвращающая одно наиболее часто встречающееся число.

Если диапазон (к примеру  A1:A20) содержит не только числовые данные, то можно воспользоваться такой конструкцией:
=ИНДЕКС(A1:A20;ПОИСКПОЗ(МАКС(СЧЁТЕСЛИ(A1:A20;A1:A20));СЧЁТЕСЛИ(A1:A20;A1:A20);0))

Эта формула использует массивы, поэтому после ввода жмем CTRL+SHIFT+ENTER.

Задачка на стратегию

Наткнулся на интересную задачку про 100 узников.

Здесь приведу упрощенную вариацию.

Перед нами 10 пронумерованных коробок, про которые известно, что

1. В коробке находится один шар с номером от 1 до 10.
2. Все шары имеют разные номера.
3. Номера не менее 5-ти шаров совпадают с номером коробки, в которой находятся.

Разрешается открыть коробку, если в ней шар с номером 1, то задание выполнено, если нет, то разрешается совершить следующую попытку.

Вопрос: можно ли совершив 4 попытки  указать коробку в которой находится шар с номером 1?

Ответ

Можно. Открываем коробку с номером 1. Если повезло, то там шар с номером 1. Если другой, то открываем коробку с этим номером. Если за четыре попытки на шар с номером 1 не попали, то он в коробке с номером последнего шара.

С помощью гиперссылки открыть папку, содержащую активную книгу

Для того, чтобы получить путь к папке, содержащей активную книгу, используем функцию
ЯЧЕЙКА(тип_информации;ссылка).

Формула =ЯЧЕЙКА("имяфайла") показывает имя файла, включая полный путь.

С помощью функций ЛЕВСИМВ() и ПОИСК(), убираем название файла, оставляя только путь к нему:

=ЛЕВСИМВ(ЯЧЕЙКА("имяфайла");ПОИСК("[";ЯЧЕЙКА("имяфайла"))-1)

Теперь остается только создать саму гиперссылку.

В итоге получаем:

=ГИПЕРССЫЛКА(ЛЕВСИМВ(ЯЧЕЙКА("имяфайла");ПОИСК("[";ЯЧЕЙКА("имяфайла"))-1);"текущая папка")

Формула интересна тем, что не содержит никаких ссылок на ячейки книги.

Аналогичный результат можно получить используя функцию ИНФОРМ().

=ГИПЕРССЫЛКА(ИНФОРМ("каталог");"текущая папка")

Привязка текста объекта к ячейке

Известно, что объекты рисования можно располагать на странице где угодно. Это прямоугольники, эллипсы и прочее.

Текст, расположенный внутри такого объекта, набирается обычно вручную. Но можно привязать его к значению какой-либо ячейки и он будет изменяться вместе со значением этой ячейки.

Обзорные статьи

Общие сведения о работе с Microsoft Excel - Викиучебник.

Описание малоизвестных возможностей Excel - Поддержка Microsoft.

Об одном использовании флажка в Excel

Добавим немного интерактивности. Для этого используем флажок Excel.

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

В таком случае можно воспользоваться элементом формы "флажок" в совокупности с условным форматированием.

Задачка

Из книги Г. Гамов, М. Стерн "Занимательная математика".

В шляпе три карточки. У одной обе стороны красные, у второй обе стороны белые, а у третьей одна сторона красная, а другая - белая. Извлекается одна карточка. Вынутая карточка обращена к нам красной стороной.

Какова вероятность того, что обратная сторона у вынутой карточки красная?

Ответ

На самом деле "очевидный ответ" 1/2 будет неправильным. Нужно учесть, что если вынутая карточка полностью красная, то она может быть повернута к нам одной из двух сторон.

Правильный ответ 2/3.

Получение списка файлов из папки

Список файлов можно получить, используя браузер Opera.

Открываем в проводнике нужную папку

Полезная клавиша F4 в Excel

Чтобы повторить последнее действие, используется клавиша F4.

Так, если мы добавили несколько строк и хотим добавить еще, выделяем диапазон перед которым нужно вставить строки и щелкаем F4.

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

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

Аналогичное F4 действие выполняет сочетание CTRL + Y.

Также клавиша F4 используется для переключения видов ссылки на ячейку в формуле.