пятница, 30 декабря 2011 г.

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

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

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

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

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

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

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

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

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

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


среда, 28 декабря 2011 г.

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

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

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

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

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

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

вторник, 27 декабря 2011 г.

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

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

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

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

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

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


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

понедельник, 26 декабря 2011 г.

Функция МОДА

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

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

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

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

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

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


понедельник, 24 октября 2011 г.

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

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

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

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

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

Ответ

вторник, 4 октября 2011 г.

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

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

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

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

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

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

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

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

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

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

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

среда, 14 сентября 2011 г.

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

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

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

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

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

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

вторник, 6 сентября 2011 г.

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

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

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

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

вторник, 30 августа 2011 г.

Задачка

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

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

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

Ответ

среда, 24 августа 2011 г.

понедельник, 22 августа 2011 г.

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

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

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

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

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

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

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

вторник, 19 июля 2011 г.

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

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

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

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

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

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

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


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

пятница, 15 июля 2011 г.

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

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



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



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

среда, 13 июля 2011 г.

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

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

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


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


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

среда, 29 июня 2011 г.

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

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



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

понедельник, 11 апреля 2011 г.

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

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

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

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



пятница, 8 апреля 2011 г.

Пример использования текущей даты

Для использования текущей даты в Excel предусмотрены две функции: ТДАТА() и СЕГОДНЯ().
Обе эти функции не содержат аргументов и меняют значения при пересчете ячейки, в которой находятся.

Функция ТДАТА() возвращает дату и время (к примеру, 07.04.2011 16:38:58),
а функция СЕГОДНЯ() возвращает только дату (к примеру, 07.04.2011).

четверг, 7 апреля 2011 г.

Модуль числа

Для вычисления модуля числа в Excel предусмотрена специальная функция ABS(число).

Пример.
Формула =ABS(55-62) вернет число 7

Для вычисления модуля комплексного числа используется инженерная функция МНИМ.ABS(комплексное число). Комплексное число записывается в виде "x+yi" (кавычки обязательно).

Пример.
Формула =МНИМ.ABS("3+4i") вернет число 5


вторник, 5 апреля 2011 г.

Как вставить или удалить строки в Excel

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


Чтобы удалить строки, выбираем в контекстном меню пункт "удалить".

Также можно использовать "горячие клавиши" "Ctrl" + "-" (удалить) и "Ctrl" + "+" (добавить) строки.

понедельник, 14 марта 2011 г.

Абсолютные и относительные ссылки

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

Относительная ссылка имеет вид A1.
Абсолютная ссылка имеет вид $A$1.
Смешанная ссылка имеет вид $A1 (фиксируем столбец) или A$1 (фиксируем строку).

среда, 9 марта 2011 г.

Использование специальной вставки в Excel

Команда "Меню" - "Правка" - "Специальная вставка" (либо через контекстное меню, вызываемой правой кнопкой мыши) позволяет расширить возможности обычного копирования.


Рассмотрим несколько примеров.

пятница, 25 февраля 2011 г.

Очистить формат ячейки

При использовании кнопки "Del" удаляется только содержимое ячейки.
Если необходимо удалить формат, а значение оставить, то заходим "Меню" - "Правка" - "Очистить" и выбираем "Форматы".




среда, 2 февраля 2011 г.

Пример макроса. Форматируем таблицу.

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

Итак, есть таблица



и мы хотим ее отформатировать так:
  • шапка и последняя (итоговая) строки - жирным шрифтом, текст шапки - по центру
  • граница всей таблицы - сплошной жирной линией
  • границы ячеек внутри - пунктирной линией

вторник, 1 февраля 2011 г.

Связать ячейки

Иногда приходится заполнять несколько таблиц, у которых шапки совпадают.

Например у меня есть 4 таблицы в которые заносятся данные с разбивкой по неделям. При наступлении следующего месяца шапки приходится перебивать заново.

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



Форматируем вставленные данные как нам нужно.

Подобное связывание объекта с ячейкой рассмотрено в заметке "Привязка текста объекта к ячейке".


среда, 26 января 2011 г.

Немного об автозаполнении

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


понедельник, 24 января 2011 г.

Копируем формулу двумя кликами



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

Также можно скопировать формулу, потянув за этот маркер.


Как посчитать строки в которых содержится нужное слово

Рассматривать будем на примере статистики ключевых запросов.

У меня есть таблица значений в первом столбце - поисковый запрос, во втором - число показов в месяц.



четверг, 20 января 2011 г.

Игра со спичками (вариант 2)

Изменим немного правила этой игры.

Пусть на столе 25 спичек. Можно за один раз брать 1, 3 или 6 спичек. Выигрывает забравший последнюю.

Рассуждаем:
  1. Если на столе 1, 3 или 6 спичек понятно, что выигрывает игрок, который ходит.
  2. Если 2 или 4, то игрок берет 1 или 3 спички, остается 3 или одна соответственно, значит он в проигрыше в соответствии с п.1.
  3. Если 5 спичек, то взяв 1 или 3 спички, передадим ход другому игроку с 4 или 2 спичками на столе  соответственно. И из п.2 следует, что ход первому игроку вернется с 1 или 3 спичками и он выиграет.
  4. Итого если на столе 1, 3, 5 или 6 спичек, то позиция выигрышная для игрока, который ходит, если 2 или 4, то - проигрышная.

среда, 19 января 2011 г.

Автозаполнение текстовых ячеек

Если необходимо внести в ячейку текстовое значение, совпадающее с каким-либо из значений, введенных выше, можно вызвать контекстное меню, щелкнув правой кнопкой мыши по редактируемому полю. Появится список всех текстовых значений, расположенных в связном диапазоне над данной ячейкой. Теперь можно выбрать нужное значение из списка.



То же самое можно сделать нажав комбинацию ALT + "стрелка вниз".

Несколько слов о функции ТЕКСТ()

При внесении данных Excel по умолчанию использует общий числовой формат. В некоторых случаях способ отображения можно обозначить при вводе, например:
  • если ввести 12/08/11, то программа автоматически присвоит формат "Дата"
  • если ввести после числа пробел и р., то - денежный формат 
  • если отделить три знака, например 12345 678, то автоматически применит разделитель групп разрядов и т.п.

понедельник, 17 января 2011 г.

Функции ЛЕВСИМВ() и ПРАВСИМВ()

Функция ЛЕВСИМВ(текст; количество_знаков) возвращает указанное количество_знаков с начала строки.

Функция ПРАВСИМВ(текст; количество_знаков) возвращает последние количество_знаков строки.

Если количество_знаков опущено, то предполагается, что оно равно 1.

Пример.
Формула =ЛЕВСИМВ("№1251 от 12.03.2011г.";5) вернет в качестве результата №1251
Формула =ПРАВСИМВ("№1251 от 12.03.2011";10) вернет текст 12.03.2011

четверг, 13 января 2011 г.

Функции ВПР() и ГПР() в Excel

Функции ВПР() и ГПР() часто используются для поиска данных в диапазонах, содержащих справочную информацию.

Например, у нас есть таблица, содержащая в первом столбце наименование изделия, а во втором - его цену. Тогда с помощью функции ВПР(), задав наименование можно найти строку, содержащую наименование изделия и в качестве результата получить цену.

среда, 12 января 2011 г.

Функции СТРОКА() и СТОЛБЕЦ()

Функция СТРОКА(ссылка) возвращает номер строки для указанной ссылки.
Ссылка представляет из себя ячейку или диапазон ячеек, если она опущена, то функция вернет номер строки на которой она расположена

Функция СТОЛБЕЦ(ссылка) возвращает номер столбца для указанной ссылки.

понедельник, 10 января 2011 г.

Как посчитать проценты

Один процент представляет из себя сотую часть числа.
Чтобы получить 1% от 550 нужно 550 разделить на 100 или, что то же самое, 550 умножить на 0.01.

В общем виде, чтобы найти r% от числа S нужно  

S умножить на r и умножить на 0.01.

Как возвести число в степень в Excel

Для того, чтобы возвести число в какую-либо степень в Excel предусмотрена специальная функция
СТЕПЕНЬ(число;степень)

Пусть в ячейке A1 находится какое-либо число, а в ячейке B1 - степень, в которую это число нужно возвести.

четверг, 6 января 2011 г.

Игра "Жизнь" Конвея

Сначала правила:
"Каждая клетка на плоскости может находиться в двух состояниях: быть живой или быть мёртвой. Клетка имеет восемь соседей. Распределение живых клеток в начале игры называется первым поколением. Каждое следующее поколение рассчитывается на основе предыдущего по таким правилам:
  • пустая (мёртвая) клетка, рядом с которой ровно три живые клетки, оживает;
  • если у живой клетки есть две или три живые соседки, то эта клетка продолжает жить; в противном случае (если соседей меньше двух или больше трёх) клетка умирает (от «одиночества» или от «перенаселённости»).

среда, 5 января 2011 г.

Максимум, минимум и автосумма

Кнопка автосумма  часто используется для суммирования выделенного диапазона в Excel, но у этой кнопки есть еще применения.

Об одном из них я сейчас расскажу.

понедельник, 3 января 2011 г.

Игра Ним

Не столь давно в салунах американского Среднего Запада можно было наблюдать любопытную игру. Начиналась она так. Бармен подзывал к себе подвыпившего ковбоя, выкладывал на стойку семь монет по одному доллару в две кучки - четыре и три доллара - и предлагал парню их выиграть: "Ставишь пять монет - получаешь двенадцать". Ковбой, отсчитав пять монет, клал их на стойку. Получалась третья кучка монет. Игра заключалась в том, что игроки по очереди берут монеты из этих кучек. Разрешается брать за один ход любое количество монет, но только из одной кучки. Забравший последнюю монету забирал и все остальные.