• Архив

    «   Март 2024   »
    Пн Вт Ср Чт Пт Сб Вс
            1 2 3
    4 5 6 7 8 9 10
    11 12 13 14 15 16 17
    18 19 20 21 22 23 24
    25 26 27 28 29 30 31
                 

Новая статья: Решение системы уравнений в Excel

Всем школьникам и студентам (и не только) посвящается :) Как решить систему уравнений в Microsoft Excel. Разбираем два принципиально разных подхода - с помощью обратной матрицы Крамера функциями МОБР и МУМНОЖ (для систем линейных уравнений) и подбором в надстройке Поиск решения (Solver).(для любых, в т.ч. и нелинейных).




Читать статью полностью

С Днем Рождения, Excel!


30 лет назад, 30 сентября 1985 года вышла первая официальная версия Microsoft Excel.
В день рождения принято вспоминать былые заслуги и творческий путь именинника. Давайте попробуем вкратце это сделать.

Excel 1.0


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

Excel 2.0

Вторая версия Excel была предназначена уже для Windows

Excel 3.0

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

Excel 4.0

Четвертая версия стала продолжением идей предыдущей: добавились возможности настройки панелей инструментов, новые сочетания клавиш, впервые появился маркер автозаполнения (тонкий черный крестик для копирования)

Excel 5.0

В этой версии появились листы и поддержка VBA.

Excel 7.0 или Excel 95

Шестой номер версии Microsoft пропустили, чтобы подровнять номера версий и сделать их одинаковыми для всех приложений пакета Office. "Семерка" стала первой 32-битной версией Excel. Не особо изменившись внешне, она, тем не менее, получила много внутренних улучшений и работала более быстро и стабильно.

Excel 8.0 или Excel 97

В этой версии в Excel добавили проверку данных (выпадающие списки и т.д.) и знаменитого помощника-скрепку. Также был усовершенствован интерфейс разработки Visual Basic. Интерфейс стал более плоским и современным.

Excel 9.0 или Excel 2000

В этой версии впервые был представлен многоместный Буфер Обмена.

Excel 10.0 или Excel 2002

Эта версия была частью пакета Office XP. В ней Excel научился фоновой проверке формул (зеленый уголок на ячейке) и автоматическому восстановлению книг в случае непредвиденного завершения работы. Также в этой версии впервые появились боковые области задач (task panes).

Excel 11.0 или Excel 2003

Эта версия входила в пакет Office 2003 и получила поддержку XML, списков (предшественник "умных таблиц";) и смарт-теги.

Excel 12.0 или Excel 2007

В 12-й версии, вышедшей в 2007 году впервые был представлен новый ленточный интерфейс и новые форматы файлов (xlsx и др.). Также были сильно расширены возможности условного форматирования (гистограммы, цветовые шкалы, иконки и др.), средства удаления дубликатов и "умные таблицы".

Excel 14.0 или Excel 2010

Пропустив, на всякий случай, несчастливый номер, Microsoft выпустила в 2010 году улучшенный вариант предыдущей версии в составе пакета Office 2010. В этой версии Excel получил улучшенные инструменты редактирования изображений, минидиаграммы-спарклайны, улучшения в работе со сводными таблицами (срезы) и возможность редактирования ленты интерфейса. Также в дополнение к этой версии впервые появилась бесплатная надстройка Power Pivot для анализа данных и построения сводных таблиц из различных источников данных.

Excel 15.0 или Excel 2013

Эта версия получила модный плоский дизайн интерфейса, более 50 новых функций и целую пачку надстроек для анализа и визуализации данных, таких как Power View, Power Map и др. Также в этой версии был впервые добавлен интеллектуальный заполнитель - функция FlashFill.

Excel для iOS и Android

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


Excel 16.0 или Excel 2016

Эта версия вышла совсем недавно - 23 сентября 2015 г. В ней в Excel впервые добавлены 6 новых типов диаграмм и несколько новых статистических функций для прогнозирования. Надстройки Power Query, Power Pivot и Power Map для импорта, обработки и анализа данных (BI) в этой версии встроены по умолчанию.

Славный путь! :)

Новый знак рубля ₽ в Excel

Насколько я понимаю, поданная еще этим летом заявка нашего ЦБ в Unicode Consortium о добавлении нового символа рубля (перечеркнутая горизонтальной линией буква ₽ или символ Лебедева-Тарабеева) была принята и большая тройка Microsoft-Google-Apple начала внедрение этого знака в свои программы. Он уже появился в последней iOS 8, а с крайним обновлением Office 365 и в Excel прилетела возможность ставить этот символ в ячейках через стандартное окно Формат ячейки (Format Cells):



Если нужно просто ввести новый символ рубля с клавиатуры, то в Excel или Word это можно сделать сочетанием клавиш Правый Alt + 8 в русской раскладке.

Мелочь, а приятно. Помню, как раньше в одном из проектов мне для этого пришлось шаманить с установкой сторонних шрифтов и пользовательскими форматами. Хотя, конечно, "руб" и "р." по инерции будут писать еще долго. Ждем шквала вопросов в почту и на форум "Как в этом дурацком икселе сделать нормальные рубли???" :)

Управление Excel текстовыми командами

На сайтеMSFT Kitchen (что-то типа официальной "курилки Microsoft") выложено примечательное видео, демонстрирующее возможности нового проекта Analyze для Microsoft Excel. Суть технологии в том, что пользователь может вводить текстовые команды в достаточно свободной форме, а "искусственный интеллект" Excel их распознает и выполняет требуемые действия. В выложенном видео, в частности, демонстрируется создание вычисляемых столбцов, итогов, подсветка максимальных значений в таблице цветом и т.д.

Посмотрите сами (на английском, но все ясно по видео - самое веселье начинается со 2-й минуты):



Безусловно, это продуманная и отрепетированная демонстрация и в реальной жизни все будет не так радужно. Но, в любом случае, весьма примечательная функция. Особенно если к ней прикрутить голосовое управление (а к этому явно идет).

Так что скоро, возможно, будете на работе, попивая кофеек, говорить что-нибудь типа "Excel, сделай-ка мне квартальный отчет!" - - и он будет делать! ;)

Chart Chooser

Нашел отличный сайт, помогающий выбрать подходящий тип диаграммы для отображения той или иной идеи - Chart Chooser от Juice Labs:



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

В коллекции 17 шаблонов Excel-диаграмм и 13 шаблонов диаграмм для Power Point. Все бесплатно.
В избранное.

Игрушки на макросах в Excel

Наткнулся в сети на неплохую реализацию судоку на макросах в Excel:



Причем код всех макросов открыт, так что можно совместить полезное с приятным - и поиграться, и поизучать VBA.
Скачать можно отсюда - http://www.psawyer.de/VBA/SudokuExcel.zip

Кстати, игр в Excel через поисковики находится много - есть даже Doomи SuperMario. Но подавляющее большинство из них -  совершенно стандартные игрушки на flash, просто вставленные на лист. Неспортивно. А вот настоящих, написанных на VBA, да еще и с открытыми кодами макросов - по пальцам пересчитать.

Update: Вот еще простенький морской бой нашел - http://www.gamesexcel.com/download/battleships.rar
 

Pacman на макросах в Excel

Нашел приятное - старый добрый Pacman, реализованный на VBA в Excel:

6548a8b6501fd10e53b7fff1c2bdf442.png

Сделал David Sobrinski.
Хороший способ вспомнить молодость, убить время или попрактиковать программирование макросов на VBA в Excel (коды открыты - изучайте на здоровье).
Скачать можно тут

Подбор паролей Excel с помощью Accent Office Password Recovery

Периодически в почту и на сайт сыпятся вопросы из серии: "Я забыл пароль на файл (книгу, лист). Помогите, что делать?" Приходится доступными фразами наставлять на путь истинный :) А недавно по счастливому совпадению разработчики из AccentSoft любезно предложили потестировать их продукт для решения подобных проблем - Office Password Recovery.

"Фишкой" этой программы, по словам авторов, по сравнению с кучей остальных программ подобного рода является способность подключать к перебору паролей графический процессор видеокарты (GPU). Даже для недорогих игровых видеокарт, GPU содержит несколько десятков параллельных конвейеров вычислений, что здорово ускоряет процесс.
Взялся за тесты. Меня, естественно, больше всего интересовала надежность защиты Excel-евских файлов, где пароли могут стоять:
  1. на листе (защита от редактирования данных в ячейках)
  2. на книге (защита листов от удаления-переименования-копирования и т.д.)
  3. на проектах VBA (защита от просмотра и редактирования макросов)
  4. на файле (защита доступа - пароли на открытие и/или изменение всего файла)
Сразу могу сказать, что первых два уровня защиты во всех версиях Excel откровенно слабые (в силу выбранного для них Microsoft'ом алгоритма защиты) и парольный хеш для них подбирался почти мгновенно. Нюанс, правда, в том, что подобранный пароль никогда не соответствовал исходному паролю (т.е. оригинальный пароль, который придумал автор, вы не узнаете), но защиту с листа и книги снимал на раз. Защита макросов тоже вскрывается практически мгновенно - программа просто убирает пароль с проекта VBA, даже не подбирая его, позволяя просмотреть и изменить код всех макросов:

7eb54eade7eb05e400556e05f5704df2.png

А вот с защитой файлов все сложнее. Дело в том, что защита файлов в Excel - это, по-сути, шифрование документа. Причем, в Excel 2007/2010 и Excel 2003 для этого используются разные криптоалгоритмы: в Excel 2000-2003 это алгоритм потокового шифрования RC4 с хешированием MD5 искуственно пониженным со 128 до 40 бит по-умолчанию, а в Excel 2007/2010 - симметричный алгоритм блочного шифрования AES с ключом 128 бит. Пароль на открытие для старого формата файлов Excel 2003 подбирался на моем домашнем ПК (Intel Core i5, 4Gb, Nvidia 8800GT) около 8 минут по стандартному сценарию атаки, мощно загружая при этом все ядра процессора и GPU:

7718e2a0bf7af6df622210f91e905457.png

Фактически, было перебрано 2.5 млрд. паролей по 3.5 млн. паролей в секунду. Простой 6-значный буквенно-цифровой пароль (nick77) подобран один-в-один. Неплохо.

Дальше - хуже, ибо тот же старый файл Excel 2003, но с чуть более сложным паролем со спецсимволами (Nick0la$) программа подобрать уже не смогла:

e8237fc14f5c07d300ee0c678de44935.png

А вот 128-битный AES в файлах нового формата Excel 2007/2010 оказался совсем стойким (обратите внимание на скорость подбора и ожидаемую длительность процесса - 1 день и 9 часов по стандартному сценарию):

421485460f061173420d80d804811296.png

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

b7c821ddaa6b12bf1649df4aa7b53944.png

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

Выводы

Выводы получились вполне ожидаемые:
  • Защитить информацию на листах и код макросов VBA невозможно.
  • Работайте в последних версиях Excel 2007/2010 - у них защита лучше.
  • В старом Excel 2003 в меню Сервис - Параметры - Безопасность - Дополнительно (Tools - Options - Security - Advanced) выставляйте не стандартный 40-битный, а продвинутый 128-битный алгоритм защиты.
  • Не делайте паролей только из цифр, буквы разного регистра и спецсимволы здорово добавляют надежности (даже для старых версий Excel)
И будет вам счастье, благолепие и полная безопасность. Ну, почти. А вообще, паролей лучше не забывать, а записывать их на специальную бумажку, которую клеить на монитор :)

Великолепная семерка

Выдал очередную статью для сайта Microsoft Small & Medium Business - "7 лучших быстрых приемов для ускорения работы в Microsoft Excel на каждый день". Простые, но мощные трюки для повседневной жизни любого офисного работника. Освободите руки и мозг для творческой работы, оставьте рутину Excel.

Мерзкий глюк с выпадающими списками

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

Если при создании списка в качестве источника (Source) задавался не диапазон ячеек, а текст, то его длина (оказывается!) ограничена 8192 (2 в 13-й степени) символами. Причем при превышении этого значения файл (или макрос, если это делалось в нем) вылетает с ошибкой, а после перезапуска - умирают все выпадающие списки во всех ячейках листа, где было превышение. Нифига себе подарочек.
Вот простой макрос для желающих испытать на себе. Вылетает четко на 8192 символе любого списка и рушит все другие списки листа:
Sub Test_Dropdown_Limit()   
   For i = 1 To 10000   
      With [a1].Validation   
         l = l & "A,"  
         Debug.Print i & " " & Len(l)   
        .Delete   
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Operator:=xlBetween, Formula1:=l   
       End With  
   Next i   
End Sub  

Игры для Microsoft Excel 2010

В любой работе есть место празднику. Классика жанра - игры Missle Command и Tower Defense для Excel 2010 на макросах от разработчиков Excel из команды Microsoft:



Скачать игрушки можно здесь (150 Кб)

Надстройка Chart Advisor для Excel 2007

Случалось ли вам при построении диаграммы в Excel, задумываться над выбором наиболее подходящего типа диаграммы для ваших данных?
Вопрос, по сути, риторический. Правильно выбранный тип может очень здорово помочь и добавить наглядности диаграмме, а неправильно выбранный - похоронить диаграмму напрочь. Самый лучший алгоритм выбора типа диаграммы под данные, который я видел - был в книжке Джина Желязны "Говори на языке диаграмм" и занимал несколько страниц с примерами. Авторы бесплатной надстройки Chart Advisor от Microsoft Office Labs попытались уложить все в пару-тройку шагов мастера и несколько диалоговых окон.

По сути, Chart Advisor строит по вашим данным все возможные варианты диаграмм, позволяя сразу увидеть конечный результат и выбрать наиболее подходящий тип под вашу задачу:



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

Весьма полезная надстройка для вашего Excel 2007. К тому же бесплатная. Правда, на английском, но он там почти не нужен - одни картинки :)

Сайт проекта Chart Advisor от Microsoft Office Labs (там же можно посмотреть демонстрационное видео работы надстройки).

Ссылка на скачивание (7,2 Мб)

Update: эта функция уже встроена в новый Excel 2013 и называется там Рекомендуемые диаграммы (Recommended Charts).

Научи свой Excel говорить

Хотите научить Excel с вами разговаривать? Легко.

Щелкаем по панели быстрого доступа правой кнопкой мыши и выбираем команду Настройка панели быстрого доступа (Customize Quick Access Toolbar). Дальше в верхней части левой половины окна, в выпадающем списке вместо Часто используемых команд (Popular commands) выбираем Все команды (All commands). В полном списке всех возможных кнопок прокручиваемся по алфавиту до кнопки Проговорить ячейки (Speak cells) и добавляем ее на панель:




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




Большинство английских слов произносит хоть и коряво, но вполне различимо. Прибавьте громкости в колонках и можете начинать пугать коллег :)

P.S.
  • Русских букв не понимает, no koga eto nas ostanavlivalo, pravda?
  • Работает только в Word и Excel 2007 и 2010
  • Особой практической пользы нет (по-крайней, мере пока не добавят поддержку русского языка). Тупо, но весело. Поэтому не в Приемы, а в Блог.

Чемпионат мира по Microsoft Office в Сан-Диего

Microsoft совместно с тестовым вендером Certiport который год проводят международные соревнования по владению Microsoft Office для студентов. В этом году финал пройдет в Сан-Диего (США). Отборочный тур можно пройти до 15 июня в любом тестовом центре Certiport. Кто-нибудь хочет в Сан-Диего? :)



Update: А вот и победитель с чеком на 5к$ http://www.flickr.com/photos/certiport/6003981359/in/photostream/  

Короткая запись Range в коде VBA

Оказывается при написании макросов в коде VBA можно вполне себе успешно использовать сокращения для очень распространенного оператора Range, определяющего диапазон листа для выполнения с ним каких-либо действий. Так, например, стандартная конструкция вида:
Range("F3").Value=45

может быть коротко записана всего лишь как:
[F3]=45

Т.е. квадратные скобки заменяют оператор Range, скобки и кавычки.
Такая форма записи работает во всех вариантах, где работает стандартный оператор Range, т.е. например:

  • [a1:b3, f5, h:h].ClearContents - очищает содержимое диапазона A1:B3, ячейки F5 и столбца H,
  • Worksheet("Лист1").[D3].Font.ColorIndex = 3 - делает текст в ячейке D3 первого листа красным,
  • MsgBox [SalesData].Address - выводит на экран сообщение с адресом именованного диапазона SalesData и т.д.
Удобно.
Сколько лет программирую, а не перестаю удивляться таким вот периодически встречаемым малоизвестным фишкам. Нет предела совершенству.
Наверх