Ёxcel - моксель!

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

noreply@blogger.com (Unknown) — Mon, 01 Apr 2013 10:35:00 +0000

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

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

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

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

Этим фактом и воспользуемся. Только в Excel удобней использовать не классический (построчный) вид треугольника Паскаля, а такой:

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

Перейдем к построению. Для нас достаточно выписывать не коэффициенты, а только их четность.

Для начала сделаем размер ячеек в Excel, к примеру 7 на 7 пикселей.

Станем в ячейку B2, затем выделим область B2:DY129 - для этого нажимаем Ctrl + G и в поле ссылка пишем B2:DY129.

Теперь в строке формул пишем =ЕСЛИ(ИЛИ(СТРОКА()=2;СТОЛБЕЦ()=2);1;ОСТАТ(A2+B1;2))
и нажимаем Ctrl + Enter, чтобы заполнить подобной формулой всю выделенную область.

Заходим Меню - Условное форматирование и для значения 1 указываем цвет ячейки.

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

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

Зафиксируем на плоскости 3 вершины треугольника и возьмем еще одну точку.
Первую точку получим как середину отрезка между случайно выбранной вершиной и точкой из п.1.
Вторую точку получим как середину отрезка между случайно выбранной вершиной и первой точкой.
...
Повторяем процесс много раз.

Можно ипользовать такой макрос:

Public Sub Макрос()

Dim arRange(1 To 3) As Range
Dim tekRow As Integer
Dim tekColumn As Integer
Dim i As Integer
Dim iT As Integer

tekRow = Int(1000 * Rnd) + 1
tekColumn = Int(200 * Rnd) + 1

Set arRange(1) = Cells(1, 1)
Set arRange(2) = Cells(50, 250)
Set arRange(3) = Cells(200, 20)

Cells.Clear

For i = 1 To 20000
iT = (Int(1000 * Rnd) Mod 3) + 1
tekRow = Int((tekRow + arRange(iT).Row) / 2)
tekColumn = Int((tekColumn + arRange(iT).Column) / 2)
Cells(tekRow, tekColumn).Interior.ColorIndex = 5
Next

End Sub

Подробнее об этом и некоторых других свойствах можно прочитать в статье "Треугольник Серпинского".

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

noreply@blogger.com (Unknown) — Thu, 28 Mar 2013 14:43:00 +0000

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

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

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

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

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

В нашем случае, используя формулу ОСТАТ(СТРОКА(A1:A14);5) получим такой массив:
1, 2, 3, 4, 0, 1, 2, 3, 4, 0, 1, 2, 3, 4

Использование формулы НЕ(ОСТАТ(СТРОКА(A1:A14);5) )*1 поможет получить такой массив:
0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0

В итоге окончательная формула для подсчета каждого пятого элемента выглядит так:

=СУММПРОИЗВ( A1:A14;НЕ(ОСТАТ(СТРОКА(A1:A14);5) )*1)

Эта формула посчитает сумму строк, кратных 5.

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

=СУММПРОИЗВ(A1:A14;НЕ(ОСТАТ(СТРОКА(ДВССЫЛ("1:" & ЧСТРОК(A1:A14)));5))*1)

Эта формула уже не зависит от номеров строк листа Excel, в которых расположены элементы.

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

noreply@blogger.com (Unknown) — Mon, 21 Jan 2013 14:19:00 +0000

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

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

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

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

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

noreply@blogger.com (Unknown) — Thu, 27 Sep 2012 11:53:00 +0000

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

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

Произведенные действия называют функцией Капрекара, названной в честь индийского математика открывшего интересное свойство числа 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.

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

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

noreply@blogger.com (Unknown) — Tue, 22 May 2012 11:30:00 +0000

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

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

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

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

noreply@blogger.com (Unknown) — Mon, 07 May 2012 08:59:00 +0000

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

Если оно четное, разделим его на 2, если нечетное - умножим на 3 и прибавим 1.
С полученным числом проделаем пункт 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.

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

noreply@blogger.com (Unknown) — Wed, 18 Jan 2012 14:36:00 +0000

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

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

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

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

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

Тогда при заполнении получаем такой ряд: П В Л Л Н Н П П П В В В Л Л Л Л Н Н Н Н П П П П П ...

Заметим, что следующее идентичное перемещение на 2 шага длиннее предыдущего (к примеру, вправо: сначала 1 раз, потом 3 раза, потом 5 и т.д.).

Это замечание и ляжет в основу алгоритма VBA.

Public Sub Ulam()
Dim i As Integer, tekR As Integer, tekC As Integer
Dim limit As Integer, tekNum As Long
Dim rCell As Range, iColor As Integer
Cells.Clear
Cells.ColumnWidth = 5.83
Cells.RowHeight = 30
tekR = 10
tekC = 10
limit = 1
tekNum = 1
Cells(tekR, tekC) = tekNum
Do While limit < 20
    For i = 1 To limit
        tekC = tekC + 1
        tekNum = tekNum + 1
        If tekC = 0 Or tekR = 0 Then Exit Do
        Cells(tekR, tekC) = tekNum
    Next i
    For i = 1 To limit
        tekR = tekR - 1
        tekNum = tekNum + 1
        If tekC = 0 Or tekR = 0 Then Exit Do
        Cells(tekR, tekC) = tekNum
    Next i
    For i = 1 To limit + 1
        tekC = tekC - 1
        tekNum = tekNum + 1
        If tekC = 0 Or tekR = 0 Then Exit Do
        Cells(tekR, tekC) = tekNum
    Next i
    For i = 1 To limit + 1
        tekR = tekR + 1
        tekNum = tekNum + 1
        If tekC = 0 Or tekR = 0 Then Exit Do
        Cells(tekR, tekC) = tekNum
    Next i
    limit = limit + 2
Loop
For Each rCell In ActiveSheet.UsedRange
    tekNum = rCell.Value
    limit = Int(Sqr(rCell.Value))
    iColor = 8
    For i = 2 To limit
        If tekNum Mod i = 0 Then iColor = 0
    Next i
    rCell.Interior.ColorIndex = iColor
    If tekNum = 1 Then rCell.Interior.ColorIndex = 3
Next
ActiveSheet.UsedRange.Select
ActiveWindow.Zoom = True
Range("A1").Select
End Sub

Немного пояснений по коду:

строки 2 - 3: i - счетчик цикла, tekR - номер строки и tekC - номер столбца ячейки (клетки) с которой работаем, limit - число шагов, tekNum - число, которое мы поместим в клетку с координатами tekR и tekC.

строки 5 - 7: удаляем всю информацию с текущего листа и делаем ячейки квадратными.

строки 8 - 12: присваиваем начальные значения.

строки 13 - 39: заполнение по спирали (стр. 14 - 19 двигаемся вправо, стр. 20 - 25 - вверх, стр. 26 - 31 - влево, 32 - 37 - вниз, после чего в стр. 38 увеличиваем шаг на 2 и пошли по новой).

строки 40 - 49: проверка простоты числа и форматирование клетки в зависимости от этого.

строки 50 - 52: выделяем нашу "скатерть" и ставим масштаб листа "по выделению".

В конечном итоге, получаем такую картину:

Такие параметры , как первое число (ведь это не обязательно 1) и размеры таблицы (параметр limit) стоит вводить через диалоговое окно.

Примечательным является то, что простые числа выстраиваются вдоль диагональных прямых.

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

noreply@blogger.com (Unknown) — Fri, 30 Dec 2011 09:21:00 +0000

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

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

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

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

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

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

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

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

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

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

noreply@blogger.com (Unknown) — Wed, 28 Dec 2011 07:34:00 +0000

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

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

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

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

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

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

noreply@blogger.com (Unknown) — Tue, 27 Dec 2011 10:00:00 +0000

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

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

К примеру, загружаем редактирование имени:

Обращаем внимание на строку состояния. У нас режим "ввод". Чтобы вручную отредактировать формулу, мне нужно сместиться влево. Нажатие стрелки влево приводит к такому "нехорошему" результату:

Это нам не нужно. Нажимаем Ctrl + Z, чтобы отменить это действие. Щелкаем один раз клавишу F2. Теперь у нас режим "правки" и можно свободно перемещаться по строке с формулой:

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

noreply@blogger.com (Unknown) — Tue, 27 Dec 2011 04:00:00 +0000

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

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

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

Функция МОДА

noreply@blogger.com (Unknown) — Mon, 26 Dec 2011 09:49:00 +0000

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

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

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

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

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

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

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

noreply@blogger.com (Unknown) — Mon, 24 Oct 2011 09:50:00 +0000

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

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

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

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

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

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

Ответ

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

noreply@blogger.com (Unknown) — Tue, 04 Oct 2011 09:47:00 +0000

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

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

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

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

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

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

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

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

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

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

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

noreply@blogger.com (Unknown) — Wed, 14 Sep 2011 12:18:00 +0000

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

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

Рассмотрим, например, простейший объект "Надпись".

Помещаем "надпись" на нашу таблицу с данными и форматируем ее.

Теперь надо привязать объект к ячейке.

Выделяем объект и в строке формул пишем адрес нужной ячейки.

В ячейке A18 у меня находится формула:
="Итого по складам - "&ТЕКСТ(СУММ(B2:B8);"### #")

Теперь если изменится значение ячейки, то изменится и надпись, которую я расположил на таблице.

На самом деле такая привязка возможна и с объектами WordArt и некоторыми элементами панели инструментов "Формы".

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

noreply@blogger.com (Unknown) — Wed, 14 Sep 2011 09:11:00 +0000

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

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

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

noreply@blogger.com (Unknown) — Tue, 06 Sep 2011 08:05:00 +0000

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

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

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

Первым делом добавляем флажок на лист. Он находится на панели инструментов "Формы".

Далее связываем его значение с какой-либо ячейкой - щелкаем правой кнопкой по флажку, выбираем в контекстном меню пункт "Формат объекта" и вкладку "Элемент управления".

Далее выделяем диапазон данных и заходим в меню "Формат" - "Условное форматирование" и пишем формулы

=И($G$1;A1=МИН($A$1:$E$17))
=И($G$3;A1=МАКС($A$1:$E$17))

Здесь $G$1 и $G$3 - значения наших флажков "минимум" и "максимум".

Теперь для "включения" нашего формата (минимальные или максимальные числа) достаточно поставить галочку соответствующего флажка.

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

Задачка

noreply@blogger.com (Unknown) — Tue, 30 Aug 2011 09:23:00 +0000

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

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

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

Ответ

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

noreply@blogger.com (Unknown) — Wed, 24 Aug 2011 12:15:00 +0000

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

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

Копируем адрес, вставляем в адресную строку Opera

Получаем список файлов с атрибутами

Копируем список (щелкаем Ctrl + A, чтобы выбрать все строки) и вставляем на лист Excel

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

noreply@blogger.com (Unknown) — Mon, 22 Aug 2011 08:44:00 +0000

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

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

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

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

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

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

Определяем простое ли число с помощью формул массива

noreply@blogger.com (Unknown) — Tue, 19 Jul 2011 13:11:00 +0000

Использование массивов позволяют упростить многие вычисления.
В частности все манипуляции, описанные в статье "Является ли число простым", можно заменить одной формулой.

Пусть в ячейке A1 находится число и нужно проверить простое ли оно. Достаточно в ячейку B1 ввести такую формулу:

=ЕСЛИ(МИН(ОСТАТ(A1;СТРОКА(ДВССЫЛ("2:"&ОКРУГЛ(КОРЕНЬ(A1);)))))=0;"составное";"простое")

Можно пойти еще дальше и записать в ячейке C1 формулу:

=СУММ(--(ОСТАТ(A1;СТРОКА(ДВССЫЛ("1:"&A1)))=0))

Эта формула покажет сколько у числа в ячейке A1 делителей.
Например, для числа 12 количество делителей будет 6, а именно 1, 2, 3, 4, 6 и само число 12.

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

Выделить пустые ячейки таблицы

noreply@blogger.com (Unknown) — Fri, 15 Jul 2011 13:38:00 +0000

Чтобы выделить пустые ячейки в Excel проще всего воспользоваться условным форматированием.
Заходим "Меню" - "Формат" - "Условное форматирование".
Дальше выбираем "значение" - "равно", пишем ="" и определяем нужный формат.

Получаем в итоге

Если таблица содержит только числа, то можно условие записать "значение" "равно" 0.

Как найти ячейки, содержащие нужное слово

noreply@blogger.com (Unknown) — Wed, 13 Jul 2011 09:44:00 +0000

Пусть есть список каких-либо данных (для примера список ФИО) и требуется посчитать количество ячеек, содержащих нужное слово (напр. имя Олег).

Заходим "Меню" - "Правка" - "Найти", либо используем сочетание клавиш CTRL + F. В поле "найти" пишем "Олег" и щелкаем кнопку "Найти все". Получаем список удовлетворяющих поиску ячеек.

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

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

Выборочное суммирование с использованием СУММЕСЛИ() и СУММПРОИЗВ()

noreply@blogger.com (Unknown) — Wed, 29 Jun 2011 12:05:00 +0000

Пусть есть таблица, содержащая объемы реализации складов в зависимости от даты.

Нужно найти сумму реализации "Склада 2" по датам, меньшим заданной даты, для примера по дням, меньшим 15.06.2011г.

Это можно сделать сделать с помощью специальной функции СУММЕСЛИ().

СУММЕСЛИ(диапазон; критерий; диапазон_суммирования)

Диапазон — диапазон вычисляемых ячеек. Эти ячейки будут проверяться на соответствие условию.

Критерий — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки.

Диапазон_суммирования — фактические ячейки для суммирования. Размер этого диапазона должен совпадать с размером диапазона, проверяемого согласно критерия.

Применив эту функцию, получим

Но если суммировать нужно по нескольким складам, например 1 и 2 складу, то формула запишется в виде суммы

=СУММЕСЛИ(A2:A13;"<15.06.2011";B2:B13)+СУММЕСЛИ(A2:A13;"<15.06.2011";C2:C13)

А если суммировать по 4 складам , то получим в формуле 4 слагаемых, что не очень удобно.

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

Можно использовать функцию СУММПРОИЗВ().

СУММПРОИЗВ(массив1; массив2; массив3; ...)

Массив1, массив2, массив3,... — от 2 до 30 массивов, чьи компоненты нужно перемножить, а затем сложить.

Только у нас будет сформирован 1 массив, элементы которого просуммируются

При несмежных столбцах формула будет такая (по складам 1, 3 и 6):

=СУММПРОИЗВ((A2:A13<ДАТА(2011;6;15))*(B2:B13+D2:D13+G2:G13))

либо такая

=СУММПРОИЗВ((A2:A13<ДАТА(2011;6;15))*1; B2:B13+D2:D13+G2:G13)

Является ли число простым

noreply@blogger.com (Unknown) — Mon, 11 Apr 2011 12:05:00 +0000

Для начала скопируем таблицу простых чисел, меньших 1 000 (например, отсюда).

Таким образом максимально возможное для проверки число не должно превышать 1 000 000.

Получилось так

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

Для этого выделяем таблицу (у меня это область B8:G35), заходим "Меню" - "Формат" - "Условное форматирование", пишем такую формулу:

=ОСТАТ($B$4;B8)=0

и определяем формат

Теперь вводим интересующее нас число. Получаем в результате

Правда такой способ не покажет простые делители исходного числа, которые больше 1000.

Но если выделенных чисел нет, то проверяемое число простое. Что и требовалось.

Другой способ описан в статье "Определяем простое ли число с помощью формул массива".