Избранное

Ася Казанцева. В интернете кто-то... Прививки вызывают аутизм, серьезные болезни лечатся гомеопатией, ВИЧ – это смертный приговор, употребление в...

Далее

Исаак Яглом, Акива Яглом. Вероятность... Информация не являлась научным понятием, пока ее не научились измерять. Единица информации – бит – это количество...

Далее

Джордж Акерлоф и Роберт Шиллер.... Со времен Адама Смита принято считать, что свободные рынки ведут к материальному благополучию. Но Акерлоф и Шиллер...

Далее

Лев Шильник. Разумное животное Разумное животное – это о человеке. Талантливый популяризатор науки Лев Шильник считает: сомневаться в том, что...

Далее

Виктор Светлов. Научный вывод:... На мой взгляд, на русском языке выходит недостаточно книг по байесовскому подходу, поэтому «я делаю стойку» при...

Далее

ВПР для поиска N-го вхождения и возврата из левого столбца

Рубрика: 7. Полезняшки Excel

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

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

Решение: ВПР не может решить эту проблему. Комбинация двух функций СМЕЩ и ПОИСКПОЗ могла бы это сделать, при условии, что таблица будет отсортирована по ключевому столбцу (подробнее см. Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня). Но если вам по каким-то соображения не удобно делать сортировку, можете воспользоваться пользовательской функцией VLOOKUPNTH (рис. 1). Вам нужно добавить эту функцию в проект VBA (если вы никогда не делали этого, см. Сумма по цвету ячеек в Excel). Чтобы получить доступ к коду в приложенном Excel-файле можно, например, кликнуть правой кнопкой мыши на ярлык листа и выбрать Исходный текст. Функция работает, как и ВПР, но вместо использования ЛОЖЬ/ИСТИНА в качестве четвертого аргумента, вы указываете, какое по порядку вхождение вы хотите получить для искомого значения.

Рис. 1. ВПР в столбце F, и VLOOKUPNTH в столбцах G-Х

Рис. 1. ВПР в столбце F, и VLOOKUPNTH в столбцах, начиная с G

Далее

Excel. Даты до 1900 года

Рубрика: 7. Полезняшки Excel

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: Excel хранит дату как число дней, прошедших с 1 января 1900 года. Это означает, что все функции даты не работают для дат XVIII в. и более ранних. Это проблема для историков и генеалогов (людей, занимающихся генеалогией).

Решение: предлагается формула, которая работает с любыми датами с 1 января 1000 г. Она основана на добавлении 1000 лет ко всем датам. Таким образом, допустимая дата, например, 23 января 2009, станет 23 января, 3009. При этом недопустимая дата, например, 17 февраля 1865, станет допустимой – 17 февраля 2865. Поскольку Excel может работать с датами вплоть до 9999 года, то с этой стороны проблем не предвидится.

Введите начальную и конечную даты в ячейки А4 в В4 (рис. 1). Используйте, например, формат 17/02/1865. Если ваша дата после 1900, Excel автоматически преобразует дату в порядковый номер. Если дата до 1900, Excel сохранит ее в виде текста. Я использовал условное форматирование для области А4:В14 на основе формулы =НЕ(ЕТЕКСТ(A4)), которое подкрасило ячейки с датами.

Рис. 1. Кажется, что Excel обрабатывает даты до 1900 года, когда вы используете эту формулу

Рис. 1. Кажется, что Excel обрабатывает даты до 1900 года, когда вы используете спецформулу

Далее

Сортировка с помощью формулы

Рубрика: 7. Полезняшки Excel

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: у вас есть числа в диапазоне D2:D11. Вам нужна формула, чтобы переставить числа по возрастанию или по убыванию.

Решение: вы можете использовать функции НАИМЕНЬШИЙ и НАИБОЛЬШИЙ (рис. 1). =НАИМЕНЬШИЙ($D$2:$D$11;1) возвращает наименьшее число в диапазоне, =НАИМЕНЬШИЙ($D$2:$D$11;2) возвращает второе минимальное число, и так далее.

Рис. 1. Сортировка с помощью формулы

Рис. 1. Сортировка с помощью формулы

Далее

Случайные числа без повторений

Рубрика: 7. Полезняшки Excel

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: вы хотите сгенерировать случайные числа между 1 и 100, но… без дубликатов. Excel стандартно предлагает функции СЛЧИС() и СЛУЧМЕЖДУ(), но обе они, генерируют совершенно случайные числа с морем дубликатов.

Предыстория: подобные задачи я обычно решаю с помощью трех дополнительных столбцов, но PGC01 (это ник участника форума) предложил немаленькую формулу, чтобы справиться с этой проблемой одним махом. Чтобы понять формулу, надо для начала познакомиться с работой функции НАИМЕНЬШИЙ(массив;k). Как правило, она возвращает k-е наименьшее значение массива. Например, =НАИМЕНЬШИЙ({69;9;12;27;42;55};3) возвращает 27, так как, 1-е наименьшее число в списке – 9, 2-е – 12, а 3-е – 27. Нечисловые значения в массиве игнорируются. Например, =НАИМЕНЬШИЙ({69;9;12;ЛОЖЬ;42;55};3) вернет 42.

Решение: сейчас мы разработаем формулу, которая будет формировать массив из неиспользованных чисел диапазона, а затем выбирать из этих чисел (рис. 1).

Рис. 1. Формула генерит наборы чисел, от 1 до 10, упорядоченные случайным образом

Рис. 1. Формула генерит наборы чисел, от 1 до 10, упорядоченные случайным образом

Далее

Совместная работа нескольких пользователей в одном файле Excel

Рубрика: 7. Полезняшки Excel

Заметка написана Андреем Макаренко

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

Далее

Ноам Хомский. Государство будущего

Рубрика: 06. Об экономике

Американский лингвист, публицист, философ Ноам Хомский считается одним из наиболее влиятельных из ныне живущих интеллектуалов. Ярый и последовательный критик политической тирании, анархист Хомский анализирует роль государства от его истоков до современности и обозначает векторы его будущего развития. Он считает одинаково регрессивными идеологии государственного социализма и государственного капитализма, а государство будущего связывает с развитием либертарианства как логического продолжения идей классического либерализма. В основе этой книги лежит выступление в Центре поэзии, Нью-Йорк, 16 февраля 1970 года. Ранее я читал Ноам Хомский. Как устроен мир.

Ноам Хомский. Государство будущего. – М.: Альпина нон-фикшн, 2012. – 104 с.

Ноам Хомский. Государство будущего. Обложка

Далее

Бертран Рассел. Человеческое познание, его сферы и границы

Рубрика: 03. О познании

Пожалуй, это наиболее известное произведение лорда Бертрана Артура Уильяма Рассела (1872–1970), оставившего яркий след в английской и мировой философии, логике, социологии, политической жизни. Вслед за Г.Фреге он вместе с А.Уайтхедом предпринял попытку логического обоснования математики (см. Принципы математики). Б.Рассел является основоположником английского неореализма, как разновидности неопозитивизма. Б.Рассел не признавал ни материализм, ни религию. Бертран Рассел очень широко цитируется, и когда я встретил не менее 10 ссылок в прочитанных мною книгах, я решил, что настал черед вгрызться в этот немаленький труд…

Бертран Рассел. Человеческое познание, его сферы и границы. – Киев: Ника-Центр, 2001. – 560 с. (На английском языке книга впервые вышла в 1948 г.)

Бертран Рассел. Человеческое познание, его сферы и границы. Обложка

Далее

Ссылки R1C1

Рубрика: 7. Полезняшки Excel

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: ссылки в стиле R1C1 полезны в нескольких ситуациях, в частности, при использовании VBA, функции ДВССЫЛ (см., например, последний раздел заметки Ссылка на другой лист с помощью ДВССЫЛ) и условного форматирования. Поэтому краткое знакомство с ними будет полезно.

До появления платформы IBM PC существовало несколько популярных приложений электронных таблиц. Это были и VisiCalc и Quattro Pro и Multiplan. Впервые стиль А1 именования ячеек был представлен в VisiCalc. Но этот продукт быстро проиграл конкурентную гонку. Multiplan выпускался компанией Microsoft до выхода Excel. В этом продукте для адресации ячеек использовался формат R1C1, который с тех пор доступен и в Excel. Но именно Лотус 1-2-3 вырвался на вершину славы сразу же после выхода в 1982 г. и стал доминировать на рынке приложений электронных таблиц для ПК. Не в последнюю очередь благодаря удобной системе именования ячеек А1. [1]

Во время войн электронных таблиц, Microsoft осознала, что большинство пользователей голосуют за стиль ссылок А1, и чтобы конкурировать, ей придется делать вид, что в Excel используются ссылки А1. Конечно, Excel только делает вид, что использует А1. В действительности «за кулисами» работают R1C1. Если вы не верите мне, пройдите по меню ФАЙЛ –> Параметры, перейдите на вкладку Формулы, и в области Работа с формулами поставьте галочку Стиль ссылок R1C1 (рис. 1).

Рис. 1. Вы всего в одном клике от стиля ссылок R1C1

Рис. 1. Вы всего в одном клике от стиля ссылок R1C1

Далее

Сумма видимых строк. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Рубрика: 7. Полезняшки Excel

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: функция СУММ суммирует все ячейки диапазона, являются ли они скрытыми или нет. Вы хотите суммировать только видимые строки.

Решение: вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ вместо СУММ. Формула будет немного отличаться, в зависимости от того, как вы спрятали строки. Если вы выделили строки, кликнули правой кнопкой мыши, и в контекстном меню выбрали скрыть, можно использовать: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109; диапазон) (рис. 1). Весьма необычно использовать для этих целей ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Как правило, эта функция нужна, чтобы Excel игнорировал другие подитоги внутри диапазона.

Рис. 1. Серия 100 в первом аргументе функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ используется для обработки видимых строк

Рис. 1. Серия 100 в первом аргументе функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ используется для обработки видимых строк

Далее

Использование ДВССЫЛ для получения данных из диапазона ячеек

Рубрика: 7. Полезняшки Excel

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel. В предыдущих главах вы познакомились с функцией ДВССЫЛ, узнали, как с ее помощью сослаться на другой лист, что делать, если имя листа содержит дату. Сегодня еще о двух интересных возможностях использования функции ДВССЫЛ.

Задача: как было показано ранее, ДВССЫЛ удобно использовать для получения значения ячейки, адрес которой вычисляется в другой ячейке. Можно использовать ДВССЫЛ для получения данных из диапазона ячеек, который затем использовался бы внутри функций ВПР или СУММЕСЛИ?

Решение: вы можете построить функцию ДВССЫЛ, которая ссылается на диапазон. Извлеченный диапазон далее может быть использован в качестве таблицы подстановки в ВПР или внутри СУММЕСЛИ, СЧЁТЕСЛИ.

Формула =СУММЕСЛИ(ДВССЫЛ(D$4&"!A2:A300");$A5;ДВССЫЛ(D$4&"!C2:C300")) в ячейке D5 (рис. 1) извлекает данные из рабочего листа Store3 (он указан в строке 4). Первый аргумент функции СУММЕСЛИ извлекает диапазон дат Store3!A2:A300. Второй аргумент – $A5 – осуществляет поиск записей, которые соответствуют определенной дате из столбца А. Третий аргумент – Store3!С2:С300 – находит количество в столбце С, соответствующее дате, и возвращает его.

Рис. 1. Каждая функция ДВССЫЛ ссылается на прямоугольный диапазон на другом листе

Рис. 1. Каждая функция ДВССЫЛ ссылается на прямоугольный диапазон на другом листе

Далее