Как excel работает: Функции и формулы в Excel с примерами

Бесконечная таблица. Почему Excel все еще актуален

Требование «знание Microsoft Office» в описаниях вакансий 2021 года похоже на мем. Но быть специалистом в Excel — круто, ведь эта программа способна на многое: от простых вычислений до сложных макросов на VBA.

В своем блоге Пакки Маккормик и его друг, фаундер и CEO Composer, Бен Роллерт написали статью о том, насколько недооценены возможности Excel, как мало мы знаем о программе и ее влиянии на программное обеспечение нового поколения. Мы выбрали главное из материала.

Война электронных таблиц

Excel не был первой цифровой электронной таблицей. В 1978 году студент Дэн Бриклин запустил VisiCalc — «видимый калькулятор». В 1980 году компания Computer Associates выпустила SuperCalc. В 1983 году Митч Капор, который поработал продакт-менеджером в VisiCalc, основал компанию Lotus и запустил 1-2-3.

Раннее програмное обеспечение для таблиц. Коллаж из оригинальной статьи на notboring.co

В это же время Билл Гейтс и команда Microsoft выпустили свою первую программу для работы с электронными таблицами — Multiplan. Ее главной целью была портативность: программу можно было запускать на более чем 50 разных компьютерах

Lotus 1-2-3 разгромил Multiplan, и Microsoft переключилась на Project Odyssey. Изначально Odyssey должно было стать лучшим ПО для ПК, чем Lotus 1-2-3, но во время разработки произошли две важные вещи:

1. Программист Дуг Кландер придумал алгоритм для одновременных вычислений в двух измерениях. Вместо того, чтобы пересчитывать каждую ячейку после любых изменений, Odyssey пересчитывала только выбранные ячейки. Это дало программе огромное преимущество в скорости и производительности.
2. Билл Гейтс и продакт-менеджер Джефф Рейкс решили, что им необходимо использовать преимущества графического интерфейса. Поэтому в середине проекта они отказались от разработки для ПК, который управлялся через интерфейс командной строки.

Именно это противоречащее здравому смыслу решение запустить программу в 1985 году на компьютере конкурента, в то время как Lotus 1-2-3 застрял на собственной MS-DOS, вывело Excel на первый план.

Excel быстро стала самой популярной в мире программой для работы с электронными таблицами по доходам (в 1991 году) и количеству проданных копий (в 1992 году) на Mac, а затем и на Windows. Сегодня Excel — это не просто программа для работы с электронными таблицами, это язык.

Почему Excel доступнее других языков программирования

Excel — самый популярный язык программирования на земле, и большинство людей, работающих в нем, даже не осознают, что они программируют. По разным оценкам, это около 750 млн пользователей из 1,2 млрд юзеров Microsoft Office.

Excel до недавнего времени не был полным по Тьюрингу. Это означает, что им нельзя было заменить «настоящий» язык программирования для многих типов вычислительных задач.

Но даже если Excel не настолько мощный, как Python и Javascript, работа в нем — программирование.

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

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

Декларативность

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

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

Инертность ментальной модели

Знаменитый UX-дизайнер Якоб Нильсен определяет ментальную модель как «то, что пользователь думает о системе». Он утверждает: «То, что люди хорошо знают, имеет тенденцию сохраняться, даже если это не полезно. Это является аргументом в пользу консервативности и отказа от новых стилей взаимодействия».

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

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

Реактивность

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

Реактивность облегчает получение быстрой обратной связи и позволяет «играться» с данными для рассмотрения различных сценариев. Excel разработан для оптимизации скорости, с которой пользователи развивают навыки работы с ним.

Естественный фулстек

Пользователи Excel — это невольные фулстек-программисты. Один человек может построить сложную модель в Excel, не прибегая к помощи сторонних специалистов.

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

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

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

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

Эффект Линди

Чем дольше что-то существует, тем дольше можно ожидать, что оно будет существовать. Частично эффект Линди можно объяснить тем, что некоторые вещи качественнее других, и люди признают и ценят это.

Excel — это программное обеспечение Линди. На его успех влияет и сетевой эффект: множество рыночных и бизнес-моделей построены в Excel, и те, кто обучались на них, будут использовать программу при создании своих компаний. Такая устойчивость мотивирует создавать на ее основе всё больше вариантов использования.

Ограничения Excel

Нет защиты от ошибок

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

Специализированное ПО в таких случаях предпочитает безопасность гибкости — обычно срабатывает запрограмированная защита от нелогичных действий.

Нет проверки достоверности данных

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

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

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

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

Неудобства структуры

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

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

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

LAMBDA: теперь все по Тьюрингу

9 февраля 2021 года генеральный директор Microsoft Сатья Наделла сделал громкое заявление в Twitter: Excel теперь завершен по Тьюрингу. Теперь в нем можно вычислять всё то же, что и на Python, Javascript или другом полном по Тьюрингу языке.

Excel formulas, the world’s most popular programming language, is now Turing-complete. Go check it out! https://t.co/qkw3Bmt1gp

— Satya Nadella (@satyanadella) February 9, 2021

Это случилось благодаря внедрению LAMBDA — возможности для пользователей определять многократно используемые функции с помощью языка формул Excel. Вызывая друг друга, функции обеспечивают рекурсию и превращают Excel в «настоящий» язык программирования.

Хотя функции LAMBDA — это, возможно, крупнейший релиз Excel за последнее десятилетие, в этом есть свои недостатки. LAMBDA увеличивает мощность, но не решает упомянутые проблемы с версиями, воспроизводимостью, доказательством и читабельностью.

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

Влияние Excel: неограниченное и вдохновляющее

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

Универсальность Excel позволяет компаниям создавать базы данных, инструменты управления отношениями с клиентами, календари, списки дел, панели управления проектами, бухгалтерские инструменты и многое другое. Это своего рода дорожная карта для разработчиков продуктов для B2B-сегмента.

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

Направление no-code и low-code продуктов привлекает тонны инвестиционных долларов, но всё еще воспринимается несерьезно. А зря: такие продукты передают творческую силу в руки пользователей и создают условия для непредсказуемого роста использования нового программного обеспечения.

Компании, построенные на базе функций Excel. Картинка из оригинальной статьи на notboring.co

Рыночная капитализация компаний на диаграмме составляет почти $0,5 трлн. Лидирует Salesforce с $193 млрд, за ней следуют несколько других единорогов, включая Asana, Tableau (приобретенный Salesforce) и Workday.

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

Figma — инструмент для дизайна без кода, позволяющий легко создавать и совместно работать над чем угодно — от логотипов до макетов веб-сайтов. Он настолько гибок, что в начале пандемии дизайнер по имени Фиона создала «WFH Town» — общее виртуальное пространство, в котором каждый мог творить или просто проводить время.

this is wild pic.twitter.com/XO0sp6eF1Z

— joe!!! (@dontdoitjoe) March 6, 2020

Bubble — это конструктор, где без кода можно создать готовые к производству веб-приложения, включая внутренние компоненты и базы данных. Он может компилировать приложение Bubble, создав электронную таблицу и связав ее с ним.

Zapier — это комбинаторный мультипликатор, который соединяет тысячи инструментов, как серию API без кода. Это позволяет синхронизировать рабочие процессы между приложениями. Например, с помощью Zapier любое действие в таблице Excel может вызвать определенное действие в Figma, Composer или Weblow, и наоборот.

Когда в 1985 году команда Project Odyssey приступила к созданию Excel, они хотели облегчить пользователям выполнение расчетов и создание графиков. Они не могли предположить, что более 750 млн человек будут использовать этот продукт. Они просто знали, что чем более гибким и удобным они его сделают, тем больше возможностей создадут.

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

Чему учит Excel

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

• Универсальность имеет значение. Невозможно предугадать, что захочет сделать пользователь. Чтобы развиваться вместе с пользователями, продакт-дизайнеру необходимо найти тонкий баланс между удобством использования и гибкостью.
• Обратная совместимость с существующими моделями. Переводя привычные для человека способы мышления и поведения в программное обеспечение, дизайнеры могут сделать кривые обучения для сложных продуктов более естественными.
• Больше функций — лучше архитектура продукта. Каждая новая часть функциональности гармонично сочетается с существующими частями. Соединения создают новые возможности.
• Создавайте для своих увлеченных пользователей. Одна из причин, по которой Excel никогда не умрет, заключается в том, что у него слишком много преданных пользователей.

Нашли ошибку? Выделите ее и нажмите Ctrl+Enter

Аналог Alt+Tab в Excel для переключения между листами в больших книгах

Язык видео: английский. Субтитры: русский, английский. (Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)

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

Инструмент «Навигация по листам» работает по аналогии с Alt+Tab в Windows:

• Панель навигации отображает все листы в книге

• Самые последние листы всегда находятся в начале списка

• Используйте горячие клавиши Alt+Стрелки или задайте свою комбинацию

Перед началом работы добавьте «Навигация по листам» в Excel

«Навигация по листам» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

– пробный период дает 14 дней полного доступа ко всем инструментам.

Как переключаться между листами больших книг Excel

Навигация по листам работает по аналогии с Alt+Tab в Windows — она позволяет переключаться между недавними листами в большой книге Excel.

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

1. Нажмите комбинацию горячих клавиш Ctrl + Alt + Направо.

2. Чтобы открыть лист, перейдите на его название клавишами стрелок (вверх, вниз, направо, налево) Нажмите Ввод ( Enter ).

   Или: выберите нужный лист мышкой.

3. Панель навигации остается поверх окна. Чтобы её закрыть, веберите лист или нажмите Esc.

Панель навигации остается поверх окна. Чтобы её закрыть, веберите лист или нажмите

По аналогии с сочетанием Alt+Tab в Windows, вы можете использовать сочетания горячих клавиш XLTools, чтобы переключаться между последними открытыми листами в книге. По мере пролистывания книги Excel, выбранные листы открываются на заднем плане.

Нажмите и удерживайте Alt Используйте стрелки для переключения листов Отпустите Alt на нужном листе Панель навигации скроектся.

• Alt + Стрелка направо для перемещения направо.

• Alt + Стрелка налево для перемещения налево.

• Alt + Стрелка вверх для перемещения вверх.

• Alt + Стрелка вниз для перемещения вниз.

Как изменить комбинацию горячих клавиш для «Навигации по листам»

Вы можете изменить комбинацию когрячих клавиш для «Навигации по листам» влюбое время. Обратите внимание, некоторые сочетания клавиш могут быть недоступны, если они зарезервированы в Excel.

Вкладка XLTools Кнопка Настройки Вкладка Горячие клавиши Задайте своё сочетание клавиш OK.

Основы Excel — Введение, Учебники для начинающих и примеры Microsoft Excel

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

Начало работы

Excel — это огромное приложение с тысячами функций и сотнями ленточных команд (меню). Когда вы открываете Excel, очень легко заблудиться. Таким образом, один из основных навыков выживания — это понимание того, как ориентироваться в Excel и получать доступ к нужным функциям.

Вот как это выглядит при открытии Excel.

На экране есть 5 важных областей.

1. Панель быстрого доступа: Это место, где можно разместить все важные инструменты. Когда вы запускаете Excel в первый раз, у него есть только 3 значка (Сохранить, Отменить, Повторить). Но вы можете добавить любую функцию Excel на панель быстрого доступа, чтобы иметь легкий доступ к ней из любого места (отсюда и название).

2. Лента: Лента похожа на расширенное меню. Он изображает все функции Excel в простой для понимания форме. Поскольку в Excel тысячи функций, они сгруппированы в несколько лент. Наиболее важные ленты — «Главная», «Вставка», «Формулы», «Макет страницы и данные».

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

4. Сетка электронных таблиц: Здесь будут храниться все ваши числа, данные, диаграммы и рисунки. Каждый файл Excel может содержать несколько листов. Но сетка электронной таблицы показывает несколько строк и столбцов активной электронной таблицы. Чтобы увидеть больше строк или столбцов, вы можете использовать полосы прокрутки слева или внизу. Если вы хотите получить доступ к другим листам, просто щелкните имя листа (или используйте сочетание клавиш CTRL+Page Up или CTRL+Page Down).

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

Начало работы с Excel — 10-минутный видеоурок

Ввод и форматирование данных, чисел и таблиц

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

Ресурсы для изучения обработки данных и форматирования

Ввод и форматирование данных в Excel
8 советов по форматированию рабочих книг
Произведите впечатление на своего начальника форматированием электронных таблиц
Знакомство с таблицами Excel
Используйте таблицы для лучшей обработки данных

Вычисление итогов и итогов с помощью формул

Условное форматирование

Условное форматирование — мощная функция Excel, которая часто используется недостаточно. Используя условное форматирование, вы можете указать Excel выделить части ваших данных, которые соответствуют любому заданному условию. Например: выделить 10 лучших клиентов, сотрудников с показателями ниже среднего и т. д. Хотя любой может настроить простые правила условного форматирования, опытный пользователь Excel может сделать гораздо больше. Они могут комбинировать формулы с условным форматированием, чтобы выделять данные, соответствующие практически любому условию.

Ресурсы для изучения расширенного условного форматирования

Что является условным форматированием
Введение в условное форматирование
5 советов по CF
Основное количество дубликатов
Подробнее

. Удивительно в Excel

Как только вы освоите основы, скорее всего, вам захочется большего. Причина проста. Любой, кто хорошо владеет Excel, всегда востребован. Ваши начальники любят вас за то, что вы легко добиваетесь поставленных целей. Ваши клиенты любят вас, потому что вы создаете впечатляющие вещи. Ваши коллеги завидуют вам, потому что ваши рабочие тетради блестящие и простые в использовании. И вы хотите большего, потому что вы видели потрясающие результаты Excel.

Здесь окупается изучение Excel. Я настоятельно рекомендую вам присоединиться к моей самой всеобъемлющей программе обучения Excel — Excel School . Это полностью онлайн-курс, который можно проходить в удобном для вас темпе, не выходя из дома (или офиса). Курс включает более 24 часов обучающих видео, более 50 загружаемых рабочих книг, подробное освещение всех важных областей использования Excel, которые сделают вас потрясающими. На сегодняшний день более 5000 человек записались в школу Excel и стали чемпионами в своей работе.

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

Полный список возможностей Excel

Содержание

1. Инструменты, калькуляторы и моделирование
2. Информационные панели и отчеты с диаграммами
3. Автоматизация заданий с помощью макросов VBA
4. Надстройка Solver и статистический анализ
5. Ввод данных и списки
6. Игры в Excel!
7. Образовательное использование с интерактивными функциями
8. Создание шпаргалок с помощью Excel
9. Диаграммы, макеты, диаграммы Ганта
10. Получение оперативных данных из Интернета
11. Excel как база данных

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

Старые добрые времена..

На самом деле, это характерно не только для Microsoft Excel, но и для большинства программ для работы с электронными таблицами, таких как Open Office или Google Sheets. Тем не менее, мы сосредоточимся на Excel и на том, что вы можете с ним сделать сегодня, поскольку он предлагает огромную гибкость, о которой вы узнаете ниже.

Начнем с основных областей использования Excel. Как мы все знаем, электронные таблицы предназначены для облегчения вычислений . Так что они содержат «формулы». Они позволяют нам выполнять базовые математические операции, такие как суммирование, умножение, нахождение среднего, а также сложные вычисления, такие как регрессионный анализ, преобразования и т. д.

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

Ниже приведен пример из наших прошлых исследований в качестве Someka:

Для получения более подробной информации см. Someka – Custom Services

Мы создали этот калькулятор для руководителя компании по разработке приложений. Он менял нужные ему параметры и сразу видит результаты.

Это очень удобно, особенно когда вы пытаетесь делать большие «модели» в Excel. Финансовое моделирование является одним из наиболее часто используемые области применения этих больших моделей . Если бы мы попытались сделать это с помощью ручки и бумаги (что когда-то было так), это было бы ужасно, я думаю:

Финансовое моделирование с помощью Excel Evolution

Финансовое моделирование также используется для проверки навыков Excel у экспертов. Они даже устраивают для него конкурс: ModelOff

У нас также есть инструмент для стартапов, позволяющий составить технико-экономическое обоснование с их собственными переменными:

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

Деловой мир требователен. Недостаточно просто произвести расчеты, настроить таблицы и написать текст. Вы должны создавать круговые диаграммы, тренды, линейные графики и многое другое. Независимо от того, готовитесь ли вы к своей презентации или проводите презентацию в своей компании, вы можете использовать функции диаграммы Excel .

Отчеты и диаграммы в Excel

Сводные таблицы

Одной из замечательных возможностей Excel являются сводные таблицы. Это расширенный инструмент Excel, который поможет вам создать динамический сводной отчет из необработанных данных очень легко. После того, как вы создадите свою таблицу, вы можете легко играть с параметрами с помощью интерфейса перетаскивания.

Это выглядит так:

Сводные таблицы весьма полезны

Инструментальные панели

Сложные модели Excel содержат множество переменных, вычислений и настроек. И вместо того, чтобы управлять всеми переменными по одной на разных листах, в разных местах, было бы очень хорошей идеей собрать их вместе, как «панель управления».

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

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

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

Допустим, вы составляете отчет о продажах для своего менеджера. Он просит вас сделать файл изменяемый , чтобы он мог видеть результаты в долларах США или евро в зависимости от ситуации. Вместо того, чтобы записывать курс Fx в расчеты, вы должны привязать его к ячейке, с которой вы сможете играть позже.

Вот так:

Логика информационной панели Excel

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

Вот пример полного набора панели инструментов:

Панель Excel от компании Someka Custom Services

Или панель для технико-экономического обоснования животноводства:

Панель технико-экономического обоснования животноводства

возможно, вы захотите проверить наш шаблон Excel:

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

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

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

Большинство пользователей, широко использующих Excel, уже пишут код. Но если вы спросите их, умеют ли они программировать, скорее всего, они скажут «нет». Конечно, написание формул — это очень малая часть того, что вы можете делать с помощью VBA. Это сильный язык программирования, который позволяет создавать небольшие сценарии (макросы), пользовательские формы, пользовательские функции, надстройки и даже игры! (которых мы коснемся ниже отдельно)

Я не буду здесь углубляться в VBA, так как это очень подробная область. Но есть некоторые базовые вещи, которые будет полезно знать тем, кто часто использует Excel:

• Вы можете записывать макросы для повторяющихся заданий : Вам не нужно писать код с нуля. Просто нажмите кнопку записи макроса, и он напишет код для вас в фоновом режиме. (Если вы хотите, вы можете изменить позже)
• Расширяет границы мира Excel. Если вы чувствуете, что вы как-то ограничены в Excel, вы больше похожи на продвинутого пользователя. Пришло время немного погрузиться в VBA.
• Вы можете создать пользовательские формы только с помощью VBA. Если вы видите что-то подобное, знайте, что он использует VBA:

.

Шаблон управления клиентскими задачами Someka

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

Неудивительно, что программное обеспечение для работы с электронными таблицами, такое как Excel, предлагает передовых математических метода для более сложных исследований. (Честно говоря, я не эксперт по статистике, но с инженерным образованием, я постараюсь объяснить основы. Не стесняйтесь поправлять меня, если я ошибаюсь)

Анализ данных трендовая концепция последних лет с развитием мощных компьютеров и улучшенного программного обеспечения. Мы собираем и записываем гораздо больше данных по сравнению с прошлым. Взгляните на этот график, чтобы понять, что я имею в виду:

Источник: Statista

Особенно эта часть:

«за последние два года было создано больше данных, чем за всю предыдущую историю человечества»

Немного пугает, не правда ли? Ладно, не будем погружаться в мир «больших данных». Вернемся к нашему скромному миру Excel.

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

Решатель

Вы когда-нибудь слышали об «оптимизации»? Когда у нас есть более одного параметра, влияющего на результат, у нас может быть только наиболее оптимизированное решение , а не максимальное решение . Это может показаться странным, но это очень актуально в нашей повседневной жизни.

Один из самых простых и популярных примеров: Задача оптимизации забора фермера

«Фермер владеет 500 метрами забора и хочет оградить как можно большую прямоугольную площадь. Как он должен использовать свой забор?

Задача оптимизации фермерского забора Решение Excel Solver

Это очень простой пример, объясняющий, что делает решатель. Но на самом деле с Solver можно запускать гораздо более сложные наборы данных.

Регрессионный анализ

Так как это немного сложная тема для этой статьи, я коснусь только поверхности.

Проще говоря, регрессионный анализ помогает найти корреляцию между переменными . Например, вы можете захотеть узнать, какова связь между количеством птиц, пролетевших над вашей головой, и деньгами, которые вы заработали сегодня. (извините за глупый пример. Нет, мне это не интересно 🙂 Вам нужно будет собрать образцы данных и провести анализ, чтобы увидеть, есть ли какая-либо корреляция.

Выглядит примерно так:

Вы вводите свои данные:

Образец данных

Запустите регрессию из Пакет инструментов анализа :

Пакет инструментов анализа Excel

И получите примерно такие результаты:

Результаты регрессионного анализа Excel

Конечно , существует гораздо более сложное программное обеспечение для анализа данных. Однако в сообществах бизнес-аналитики есть шутка:

• Какая функция наиболее часто используется в любом решении для бизнес-аналитики?
• Это «Экспорт в Excel»

Похоже, мы не прекратим использовать Excel в ближайшее время.

Возвращаясь из скучного мира анализа данных, давайте упомянем самую простую и удобную область использования Excel: Make Lists !

Это самоочевидно, поэтому я не буду вдаваться в подробности. Если вы хотите перечислить некоторые простые данные, делать заметки, создавать списки дел или что-то еще. Просто откройте эксель и запишите. Мы упоминали о «бумажной альтернативе»? О да, мы сделали.

Пример списка потенциальных клиентов:

Список потенциальных клиентов в Excel

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

PDF в Excel

Если вы хотите поиграть с данными на веб-странице, вы можете легко скопировать и вставить их в файл Excel, а затем вы можете сортировать, фильтровать или делать все, что хотите:

Например, список Fortune 500 US:

Список Fortune 500 в Excel

Все любят списки дел. И мы создали полезный список дел в Excel для бизнеса или личного использования. Проверьте это, это бесплатно:

Шаблон списка дел Excel

Мы уже упоминали об этом в разделе VBA выше. Но стоит поговорить еще немного.

Visual Basic также позволяет программировать сложные вещи, такие как игры . Но, конечно, не ждите GTA или FIFA. Такие вещи, как шахматы, судоку или монополия, вполне допустимы. Но некоторые люди пошли дальше и создали более сложные вещи, например, ролевую игру. Взгляните на это:

Источник: carywalkin.ca

Эта игра была создана бухгалтером Кэри Уолкином. Я знаю, что это выглядит не очень хорошо, но это в Excel! (можно поиграть в офисе ;))

Другой пример:

Источник: excelunusual. com

Симулятор полета в Excel?? Это то же самое, что мы используем, чтобы подвести итоги продаж? Лол, да.

Вы также можете встраивать флэш-игры в Excel (например, Super Mario, Angry Birds или что-то еще), но я не учитываю их, поскольку они не созданы с помощью VBA.

Как мы упоминали в разделе «Финансовое моделирование», Excel вполне подходит для создание динамических результатов в соответствии с входными данными. Мы получаем выгоду от этого для создания интерактивных инструментов.

Одним из примеров, который приходит мне на ум, является эта электронная таблица, подготовленная ребятами из Сан-Франциско:

Источник: exceleverest.com

Я сам не пробовал, но это учебник по Excel в Excel. Идея понравилась!

Другой аналогичный интерактивный инструмент обучения Excel от Keyskillset :

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

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

Источник: indzara.com

Шаблон плана урока для учащихся в формате Excel, который мы недавно подготовили:

План урока для учащихся Someka в Excel

Вы можете изучать Excel в Excel!

Как говорится: Практика ведет к совершенству!

Вы можете проверить свои навыки работы с Excel в Excel с помощью  Тренажер формул Excel:

На самом деле это шаблон Excel, подготовленный с помощью макросов VBA, и в основном работает как практический рабочий лист . Он состоит из 30 разделов и около 100 вопросов. Вы можете изучить VLOOKUP, IF и многие другие формулы Excel на практике. Если вам нравится идея «обучения на практике», то стоит проверить.

Кроме того, этот онлайн-курс от GoSkills также предназначен для всех и охватывает уроки для начинающих, среднего и продвинутого уровня.

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

Например, это шпаргалка:

Автор Jacob Seidelin

Эта сжатая и обобщенная информация очень полезна во многих аспектах. Когда вы пытаетесь запомнить вещи, поиск, ссылки и т. Д. И их можно легко создать с помощью Excel. Давайте поищем в Google шпаргалку, сделанную в Excel.

Это от Дейва Чайлда (cheatography.com), и я также использовал это, я впервые выучил HTML:

Создано Дэйвом Чайлдом

Последний пример — это шпаргалка Excel, созданная для ярлыков Excel:

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

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

Диаграммы Ганта могут быть немного устаревшими, поскольку гибкие методы управления проектами становятся все более популярными, они все еще широко используются. В Интернете есть несколько шаблонов диаграмм Ганта для Excel.

Пример диаграммы Ганта с сайта vertex42.com:

Источник: vertex42.com

Я только что обнаружил макет структуры отчетности, который я когда-то подготовил в Excel: Кстати, вы видели наш автоматический генератор организационных диаграмм?

Это шаблон Excel, который позволяет создавать организационные диаграммы из списков Excel одним нажатием кнопки. Это может быть полезно для владельцев малого бизнеса и отделов кадров.

Этот тип диаграмм напрямую связан с Excel, поскольку большинство компаний уже хранят свои данные в электронных таблицах . Но я также знаю людей, которые даже создают макеты своих сайтов в Excel (со ссылками на другие разделы, размещением кнопок, слайдеров и т. д.).

Иногда вам может потребоваться автоматическое обновление файлов Excel из оперативного источника данных. Например, если вы проводите анализ фондового рынка и хотите получить последние данные о ценах на некоторые акции на NYSE, вы можете подключить файл Excel к потоку данных и позволить ему автоматически получать самую свежую информацию (если только вы не хотите вводить ее самостоятельно). один за другим!)

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

• Цены на акции
• Результаты матчей по футболу, НБА, НФЛ или любым спортивным играм (с сайтов с результатами в реальном времени)
• Валютные курсы
• Полетные данные аэропортов в режиме реального времени
• Любая информация в общей базе данных (будь то внутренняя сеть вашей компании или общедоступная)

Эта тема становится все более важной, поскольку большая часть данных хранится в облачных системах. Мы не загружаем информационные биты на наши компьютеры, как раньше. Итак, Microsoft усердно работает над улучшением веб-интеграции Excel.

Рекомендуемое чтение: Может ли Excel извлекать данные с веб-сайта?

Да, не лучшая идея использовать Excel в качестве базы данных. Потому что он не предназначен для этой цели. Запросы будут занимать много времени, особенно когда объем данных увеличивается. Иногда это может быть ненадежно и не очень безопасно. Это все принято. Однако нам не всегда нужен полный комплект систем баз данных, и он может служить нам мини-складом для наших небольших данных.

Например, если вы ведете учет данных своих счетов-фактур и хотите провести некоторый анализ продаж, это может быть хорошей отправной точкой. Если позже вы захотите увидеть больше деталей, хотите записать больше разбивок, вам нужно будет перейти в «настоящую базу данных». Это может быть Access, SQL или что-то еще. Просто следите за своим файлом Excel, потому что в нем может быть не более 1 миллиона строк.

Некоторые из вас могут сказать: «Эй, этого более чем достаточно, не так ли?»

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

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

Генераторы счетов Someka в Excel

Заключение

По мере того, как Интернет становится все более доступным для всех, люди начали его использовать платформы для совместной работы на больше, чем раньше. В этом аспекте онлайн-приложения для работы с электронными таблицами, такие как Google Sheets, становятся все более популярными и составляют конкуренцию Microsoft Excel. Другие бесплатные альтернативы, такие как open office или libre office, также популярны.