Microsoft Excel - полезные советы. Работа с диаграммой в Excel: быстрое добавление новых данных. Горячие комбинации клавиш

Вот некоторые простые способы, которые существенно улучшат пользование этой необходимой программой. Выпустив Excel 2010 , Microsoft добавил несметное количество , но они заметны далеко не сразу. «Так Просто!» предлагает тебе попробовать приемы, которые гарантированно помогут тебе в работе.

20 лайфхаков при работе с Excel

1. Теперь ты можешь выделить все ячейки одним кликом. Нужно всего лишь найти волшебную кнопку в углу листа Excel. И, конечно, не стоит забывать о традиционном методе — комбинации клавиш Ctrl + A.
2. Для того, чтобы открыть одновременно несколько файлов, нужно выделить искомые файлы и нажать Enter. Экономия времени налицо.



3. Между открытыми книгами в Excel можно легко перемещаться с помощью комбинации клавиш Ctrl + Tab.
4. Панель быстрого доступа содержит в себе три стандартные кнопки, ты можешь легко изменить их количество до нужного именно тебе. Просто перейди в меню «Файл» ⇒ «Параметры» ⇒ «Панель быстрого доступа» и выбирай любые кнопки.



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



6. Когда возникает ситуация, где надо вставить несколько пустых строк, делай так: выдели нужное количество строк или столбцов и нажми «Вставить». После этого просто выбери место, куда нужно сдвинуться ячейкам, и все готово.
7. Если тебе нужно переместить любую информацию (ячейку, строку, столбец) в Excel, выдели ее и наведи мышку на границу. После этого перемести информацию туда, куда требуется. Если необходимо скопировать информацию, сделай ту же операцию, но с зажатой клавишей Ctrl.



8. Теперь удалять пустые ячейки, так часто мешающие работе, невероятно легко. Можно избавиться от всех сразу же, просто выдели нужный столбец и перейди на вкладку «Данные» и нажмите «Фильтр». Над каждым столбцом появится стрелка, направленная вниз. Нажав на нее, ты попадешь в меню избавления от пустых полей.



9. Искать что-то стало куда удобней. Нажав сочетание клавиш Ctrl + F, ты можешь найти любые необходимые тебе данные в таблице. А если еще и научиться использовать символы «?» и «*», можно значительно расширить возможности поиска. Знак вопроса — один неизвестный символ, а астериск — несколько. Если ты не знаешь точно, какой запрос вводить, этот метод обязательно поможет. Если же тебе нужно найти вопросительный знак или астериск и ты не хочешь, чтобы вместо них Excel искал неизвестный символ, то поставь перед ними «~».
10. Неповторяющаяся информация легко выделяется с помощью уникальных записей. Для этого выбери нужный столбец и нажми «Дополнительно» слева от пункта «Фильтр». Теперь поставь галочку и выбери исходный диапазон (откуда копировать), а также диапазон, в который нужно поместить результат.



11. Создать выборку — раз плюнуть с новыми возможностями Excel. Перейди в пункт меню «Данные» ⇒ «Проверка данных» и выбери условие, которое будет определять выборку. Вводя информацию, которая не подходит под это условие, пользователь будет получать сообщение, что информация неверна.



12. Удобная навигация достигается простым нажатием Ctrl + стрелка. Благодаря этому сочетанию клавиш ты можешь легко перемещаться по крайним точкам документа. Например, Ctrl + ⇓ поставит курсор в низ листа.
13. Для транспонирования информации из столбца в столбец скопируй диапазон ячеек, который нужно транспонировать. После этого кликни правой кнопкой на нужное место и выбери специальную вставку. Как видишь, сделать это больше не является проблемой.
14. В Excel можно даже скрыть информацию! Выдели нужный диапазон ячеек, нажми «Формат» ⇒ «Скрыть или отобразить» и выбери нужное действие. Экзотическая функция, не правда ли?



15. Текст из нескольких ячеек совершенно естественно объединяется в одну. Для этого выбери ячейку, в которую ты хочешь поместить соединенный текст и нажать нажать «=». Затем выбери ячейки, ставя перед каждой символ «&», из которых ты будешь брать текст.
16. Регистр букв меняется по твоему желанию. Если тебе надо сделать все буквы в тексте прописными или строчными, можешь использовать одну из специально для этого предназначенных функций: «ПРОПИСН» — все буквы прописные, «СТРОЧН» - строчные. «ПРОПНАЧ» — первая буква каждого слова прописная. Пользуйся на здоровье!



17. Чтобы оставить нули в начале числа, всего лишь поставь перед числом апостроф «’».
18. Теперь в любимой программе есть автозамена слов — схожая с автозаменой в смартфонах. Сложные слова больше не беда, к тому же их можно подменять аббревиатурами.
19. Следи за различной информацией в правом нижнем углу окна. А нажав туда правой кнопкой мыши, можно убрать ненужные и добавить нужные строки.
20. Чтобы переименовать лист, просто нажми по нему два раза левой кнопкой мыши и введи новое название. Проще простого!

Пускай работа приносит тебе больше удовольствия и дается легко. Тебе понравилась эта познавательная статья? Отправь ее своим близким!

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

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

Мгновенное заполнение

Эта функция точно есть в Excel 2013 года. К примеру, в списке с полными ФИО нужно сократить имена и отчества. Например: из Алексеев Алексей Алексеевич в Алексеев А. А. Для этого в соседнем столбце необходимо прописать 2-3 строчки таких сокращения вручную, а дальше программа предложит автоматически повторить действия с оставшимися данными – потребуется просто нажать enter.

Преобразование строк в столбцы и обратно

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

1. Выделить область
2. Скопировать данные
3. Правой кнопкой мыши нажать на ячейку, куда должны переместиться данные, в появившемся окне выбрать значок «Транспонировать»

В старых версиях Excel такого значка нет, зато эта команда выполняется с помощью нажатия комбинации клавиш ctrl + alt + V и выбора функции «транспонировать» . И такими несложными движениями мыши в руке столбцы и строки меняются местами.

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

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

Например, учитель в школе делает таблицу средней успеваемости каждого ученика. Тогда условное форматирование прямо внутри ячеек с оценками построит «график», на котором будут выделены средние баллы каждого ученика, к примеру, от низкого к высокому. Или же выделит ячейки, в которых средний балл, например, ниже или выше 6. Параметры могут быть разными.

Чтобы функция заработала, нужно открыть вкладку «Главная» , выделить поле с ячейками, в группе инструментов «Стили» найти значок «Условное форматирование» и выбрать подходящий вариант – это может быть гистограмма, цветовая шкала или набор значков. Этой же командой можно самостоятельно установить правила выделения ячеек.

Спарклайны

1. Нажать «Вставка»
2. Открыть «Спарклайны»
3. Выбрать команду «График» или «Гистограмма»
4. В открывшемся окне указать диапазон с числами и ячейки, в которых должны появиться спарклайны, основанные на этих числах

Макросы

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

Нужно включить макрос, открыв вкладку «Разработчик» и отыскав на панели инструментов значок «Макрос» , а рядом с ней – похожий значок с красным кружочком в верхнем левом уголке . Если много раз нужно выполнять одно и тоже действие – так запустится запись макроса, и дальше компьютер сделает сам.

Прогнозы

Эта опция неспроста не афишируется разработчиками – она может предсказывать будущее! На самом деле функция может спрогнозировать, посчитать будущие значения на основе уже известных данных, которые уже прописаны. Доступ к ней прост:

1. Указать не меньше 2 ячеек с исходными данными
2. Открыть «Данные» – «Прогноз» – «Лист прогноза»
3. В окошке «Создание листа прогноза» кликнуть на график или гистограмму
4. В области «Завершение прогноза» установить дату окончания, нажать «Создать»

Заполнить пустые ячейки списка

Это избавит от монотонного ввода одинаковых фраз во множество ячеек. Конечно, можно воспользоваться старым добрым копипастом (копировать ctrl+c, вставить ctrl+v), но, если нужно заполнить не 10 ячеек, а, например, сотню-другую, и местами текст будет разным, – следующая подсказке точно пригодится.

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

Дальше выделяется столбец с нашими днями недели, во вкладке «Главная» нажимаются кнопки «Найти и выделить» , «Выделить группу ячеек» , «Пустые ячейки» . Потом в первой пустой ячейке нужно поставить знак «=» , стрелкой «вверх» на клавиатуре вернуться к заполненной ячейке (на примере в таблице – «понедельник»). Нажать ctrl+enter . Готово, теперь все пустые ячейки должны заполниться продублированными данными.

Найти ошибки в формуле

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

Часть формулы вычисляется прямо в строке формул. Для этого необходимый участок нужно выделить и нажать F9. Все просто, но есть одно «но». Если забыть вернуть все на место, то есть отменить вычисление функции, и нажать enter – посчитанная часть останется в виде числа.

Кликнуть на «Вычислить формулу» во вкладке «Формулы» . Откроется окно, где можно вычислять формулу пошагово и тем самым найти момент, где появляется ошибка, если она, конечно, есть.

«Умная» таблица

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

Необходимо выделить нужную область, во вкладке «Главная» нажать кнопку «Форматировать как таблицу» . В списке разных форм и цветов вариантов оформления найти подходящий.

Копирование с сохранением форматов

Наверняка с маркером автозаполнения знакомы все. Это маленький черный крестик в правом углу ячейки, который позволяет копировать содержимое или формулу из одной ячейки в несколько других, если потянуть его вниз или в сторону. Так вот, в этом случае копирование может нарушить «внешний вид» таблицы, ведь копируется еще и формат ячейки. Если потянуть за крест и нажать смарт-тег (значок появится внизу справа в скопированном диапазоне), формат не скопируется. Другой вариант – опция «Копировать только значения» , в этом случае дизайн таблицы тоже не будет испорчен.

Удалить пустые ячейки

Быстро расправиться со всеми ненужными пустыми ячейками очень просто:

1. Выделить столбец
2. Вкладка «данные»
3. Нажать «фильтр»

Над столбцами появится стрелка, на которую нужно нажать, а затем в открывшемся меню под цифрами 1-9 поставить галочку напротив пункта «Пустые ячейки» .

Найти отличия и совпадения двух областей

Если нужно быстро найти одинаковые или разные данные в 2-х списках, эту работу Excel проделает сам. Несколько кликов – и программа выделит схожие или отличные элементы:

Необходимо выделить списки (при этом зажать ctrl). Во вкладке «Главная» перейти к кнопке «Условное форматирование» . Дальше нажать «Правила выделения ячеек» – «Повторяющиеся значения» или «Уникальные» . Готово.

Быстрый подбор значений

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

1. Открыть «Данные» – «Работа с данными» – «Анализ «что если» – «Подбор параметра»
2. В область «Установить в ячейке» вставить ссылку на ячейку с нужной формулой
3. В области «Значение» написать нужный результат формулы
4. В области «Изменяя значение ячейки» вставить ссылку на ячейку с корректируемым значением и нажать Ок

Быстрое перемещение

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

Быстро добавить новые значения в диаграмму

Новые данные в уже готовую диаграмму можно поместить просто с помощью обычного копипаста: выделить необходимые значения, скопировать их (ctrl+c ) и вставить в диаграмму (ctrl+v ).

Расширенный поиск

Комбинация ctrl + f , как все знают, ведет в меню поиска, который найдет любую информацию в этой программе. Функция имеет несколько секретов – знаки «?» и «*» включат в поиске настоящего сыщика: с их помощью можно отыскать данные, если нет уверенности в точности запроса. Знак вопроса заменит один неизвестный символ, а астериск (этот знак в простонародье называют звездочкой) – сразу несколько неизвестных.

Когда в куче данных нужно найти именно эти знаки – перед ними ставят значок «~» . Тогда программа не примет их за неизвестные символы.

Восстановление несохраненного файла

На случай наступления конца света.

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

MS Excel 2010. Последовательность команд такая: «Файл» - «Последние» - (кнопка внизу справа).

MS Excel 2013. «Файл» - «Сведения» - «Управление версиями» - «Восстановить несохраненные книги» . И программа откроет тайный мир всех временных копий книг, которые создавались или изменялись, но не были сохранены.

Горячие комбинации клавиш

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

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

Студенты не любят MS Excel. Впрочем, MS Excel тоже не очень любит студентов. А мы знаем, как приручить этого демона и упростить учебу и работу с этим нелегким офисным приложением. Ловите, дорогие студенты: самые полезные функции, а также основные методы и приемы работы в Excel!

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

Работа с диаграммой в Excel: быстрое добавление новых данных

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

Выделите диапазон с новыми данными, скопируйте его и после этого просто вставьте в диаграмму.

Моментальное заполнение в Excel

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

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

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

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

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

Точно так же вы можете склеивать из фрагментов ФИО, извлекать имена из email и так далее.

Копирование без нарушения форматов в Excel

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

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

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

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

Кстати! Для наших читателей сейчас действует скидка 10% на

Данные из таблицы на геокарте в Excel

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

Чтобы это сделать, зайдите во вкладку «Вставка», найдите там Магазин приложений и установите там плагин Bing Maps. Добавив модуль, его можно будет потом выбрать во вкладке Вставка/Мои приложения и разместить на рабочий лист.

Теперь вам остается только выделить ячейки с нужной информацией и кликнуть кнопочку Show Locations в модуле. Теперь вы сможете наблюдать информацию на географической карте.

Быстрый переход по листам в Excel

Если в одном документе MS Excel вы создаете по много листов, которые не помещаются в ленте, вам сложно будет в них ориентироваться.

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

Превращение строки в столбец и наоборот в Excel

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

Если вы используете старую версию Эксель и там нет такой функции, вопрос легко решается специальной вставкой: Ctrl + Alt + V и выбрать опцию Транспортировать.

«Умная» таблица в Excel

Выделите область с информацией, на вкладке Главная выберите Форматировать как таблицу. Ваш список автоматически преобразуется в «умную» таблицу. Кроме красивой и яркой раскраски эта таблица обладает массой преимуществ. С ней можно:

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

Спарклайны в Excel

Спарклайнами называют мини-диаграммы, которые прописываются прямо в ячейках. Они нагладно показывают динамику введенной информации.

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

Восстановление несохраненных файлов в Excel

Иногда чисто на автомате закрываешь таблицу с огромным количеством введенных данных, не нажав кнопку Сохранить. Не переживайте – это не конец света.

Делайте так: жмите Файл/Последние, в нижнем правом углу экрана ищите кнопку Восстановить несохраненные книги.

Если у вас версия новее 2013 года, действия несколько отличаются: Файл/Сведения/Управления версиями/Восстановить несохраненные книги.

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

Сравнение 2-х участков документа на сходства и отличия в Excel

Если вам нужно быстро найти и сравнить элементы, быстрее всего это будет сделать так:

• выделяем 2 сравниваемых столбца с удержанием Ctrl,
• выбираем вкладку Главная/Условное форматирование/Правила выделения ячеек/Повторяющиеся значения,
• выбираем вариант Уникальные, который появится в новом списке.

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

Excel - не самая дружелюбная программа на свете. Обычный пользователь использует лишь 5% её возможностей и плохо представляет, какие сокровища скрывают её недра. H&F почитал советы Excel -гуру и научился сравнивать прайс-листы, прятать секретную информацию от чужих глаз и составлять аналитические отчёты в пару кликов. (О"кей, иногда этих кликов 15.)

Импорт курса валют

В Excel можно настроить постоянно обновляющийся курс валют.

Выберите в меню вкладку «Данные».

Нажмите на кнопку «Из веба».

В появившемся окне в строку «Адрес» введите http://www.cbr.ru и нажмите Enter.

Когда страница загрузится, то на таблицах, которые Excel может импортировать, появятся чёрно-жёлтые стрелки. Щелчок по такой стрелке помечает таблицу для импорта (картинка 1).

Пометьте таблицу с курсом валют и нажмите кнопку «Импорт».

Курс появится в ячейках на вашем листе.

Кликните на любую из этих ячеек правой кнопкой мыши и выберите в меню команду «Свойства диапазона» (картинка 2).

В появившемся окне выберите частоту обновления курса и нажмите «ОК».

Супертайный лист

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

Нажмите ALT+F11.

Слева у вас появится вытянутое окно (картинка 2).

В верхней части окна выберите номер листа, который хотите скрыть.

- В нижней части в самом конце списка найдите свойство «Visible» и сделайте его «xlSheetVeryHidden» (картинка 3). Теперь об этом листе никто, кроме вас, не узнает.

Запрет на изменения задним числом

Перед нами таблица (картинка 1) с незаполненными полями «Дата» и «Кол-во». Менеджер Вася сегодня укажет, сколько морковки за день он продал. Как сделать так, чтобы в будущем он не смог внести изменения в эту таблицу задним числом?

Поставьте курсор на ячейку с датой и выберите в меню пункт «Данные».

Нажмите на кнопку «Проверка данных». Появится таблица.

В выпадающем списке «Тип данных» выбираем «Другой».

В графе «Формула» пишем =А2=СЕГОДНЯ().

Убираем галочку с «Игнорировать пустые ячейки» (картинка 2).

Нажимаем кнопку «ОК». Теперь, если человек захочет ввести другую дату, появится предупреждающая надпись (картинка 3).

Также можно запретить изменять цифры в столбце «Кол-во». Ставим курсор на ячейку с количеством и повторяем алгоритм действий.

Запрет на ввод дублей

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

Выделяем ячейки А1:А10, на которые будет распространяться запрет.

Во вкладке «Данные» нажимаем кнопку «Проверка данных».

Во вкладке «Параметры» из выпадающего списка «Тип данных» выбираем вариант «Другой» (картинка 1).

В графе «Формула» вбиваем =СЧЁТЕСЛИ($A$1:$A$10;A1)<=1.

В этом же окне переходим на вкладку «Сообщение об ошибке» и там вводим текст, который будет появляться при попытке ввести дубликаты (картинка 2).

Нажимаем «ОК».

Выборочное суммирование

Перед вами таблица, из которой видно, что разные заказчики несколько раз покупали у вас разные товары на определённые суммы. Вы хотите узнать, на какую общую сумму заказчик по имени ANTON купил у вас крабового мяса (Boston Crab Meat).

В ячейку G4 вы вводите имя заказчика ANTON.

В ячейку G5 - название продукта Boston Crab Meat.

Встаёте на ячейку G7, где у вас будет подсчитана сумма, и пишете для неё формулу {=СУММ((С3:С21=G4)*(B3:B21=G5)*D3:D21)}. Сначала она пугает своими объёмами, но если писать постепенно, то её смысл становится понятен.

Сначала вводим {=СУММ и открываем скобки, в которых будет три множителя.

Первый множитель (С3:С21=G4) ищет в указанном списке клиентов упоминания ANTON.

Второй множитель (B3:B21=G5) делает то же самое с Boston Crab Meat.

Третий множитель D3:D21 отвечает за столбец стоимости, после него мы закрываем скобки.

Сводная таблица

У вас есть таблица (картинка 1), где указано, какой товар, какому заказчику, на какую сумму продал конкретный менеджер. Когда она разрастается, выбирать отдельные данные из неё очень сложно. Например, вы хотите понять, на какую сумму продано моркови или кто из менеджеров выполнил больше всего заказов. Для решения таких проблем в Excel существуют сводные таблицы. Чтобы её создать, вам нужно:

Во вкладке «Вставка» нажать кнопку «Сводная таблица».

В появившемся окне нажать «ОК» (картинка 2).

Появится окошко, в котором вы можете сформировать новую таблицу, используя только интересующие вас данные (картинка 3).

Товарный чек

Чтобы посчитать общую сумму заказа, можно поступить как обычно: добавить столбец, в котором нужно перемножить цену и количество, а потом посчитать сумму по этому столбцу (картинка 1). Если же перестать бояться формул, можно сделать это более изящно.

Выделяем ячейку C7.

Вводим =СУММ(.

Выделяем диапазон B2:B5.

Вводим звёздочку, которая в Excel ­ - знак умножения.

Выделяем диапазон C2:C5 и закрываем скобку (картинка 2).

Вместо Enter при написании формул в Excel нужно вводить Ctrl + Shift + Enter.

Сравнение прайсов

Это пример для продвинутых пользователей Excel. Допустим, у вас есть два прайса, и вы хотите сравнить их цены. На 1-й и 2-й картинке у нас прайсы от 4 и от 11 мая 2010 года. Часть товаров в них не совпадает - вот как узнать, что это за товары.

Создаём в книге ещё один лист и копируем в него списки товаров и из первого, и из второго прайса (картинка 3).

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

В меню выбираем «Данные» - «Фильтр» - «Расширенный фильтр» (картинка 4).

В появившемся окне отмечаем три вещи: а) скопировать результат в другое место; б) поместить результат в диапазон - выберите место, куда хотите записать результат, в примере это ячейка D4; в) поставьте галочку на «Только уникальные записи» (картинка 5).

Нажимаем кнопку «ОК» и, начиная с ячейки D4, получаем список без дублей (картинка 6).

Удаляем первоначальный список товаров.

Вводим в колонку сравнения формулу =D5-C5, которая будет вычислять разницу (картинка 7).

Осталось автоматически загрузить в колонки «4 мая» и «11 мая» значения из прайсов. Для этого используем функцию: =ВПР(искомое_значение; таблица; номер_столбца; интервальный _просмотр).

- «Искомое_значение» - это строчка, которую мы будем искать в таблице прайса. Легче всего искать товары по их наименованию (картинка 8).

- «Таблица» - это массив данных, в котором мы будем искать нужное нам значение. Он должен ссылаться на таблицу, содержащую прайс от 4-го числа(картинка 9).

- «Номер_столбца» - это порядковый номер столбца в диапазоне, который мы задали для поиска данных. Для поиска мы определили таблицу из двух столбцов. Цена содержится во втором из них (картинка 10).

Интервальный_просмотр. Если таблица, в которой вы ищете значение, отсортирована по возрастанию или по убыванию, надо ставить значение ИСТИНА, если не отсортирована - пишете ЛОЖЬ.

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

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

Оценка инвестиций

В Excel можно посчитать чистый дисконтированный доход (NPV), то есть сумму дисконтированных значений потока платежей на сегодняшний день. В примере рассчитана величина NPV на основе одного периода инвестиций и четырёх периодов получения доходов (строка 3 «Денежный поток»).

Формула в ячейке B6 вычисляет NPV с помощью финансовой функции: =ЧПС($B$4;$C$3:$E$3)+B3 (картинка 1).

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

В ячейке С5 результат получен благодаря формуле =C3/((1+$B$4)^C2) (картинка 2).

В ячейке C6 тот же результат получен через формулу {=СУММ(B3:E3/((1+$B$4)^B2:E2))} (картинка 3).

Сравнение инвестиционных предложений

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

В свободную ячейку нужно ввести формулу =npv(b3/12,A8:A12)+A7, где b3 - учётная ставка, 12 - число месяцев в году, A8:A12 - столбец с цифрами поэтапного возврата инвестиций, A7 - необходимая сумма вложений.

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

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