Что относится к специальным методам работы с программой excel

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

1. Функция ВПР

Эта функция позволяет быстро найти нужное Вам значение в таблице. Например, нам нужно узнать финальный балл Бетт, мы пишем: =ВПР(“Beth»,A2:E6,5,0), где Beth – имя ученика, A2:E6 – диапазон таблицы, 5 – номер столбца, а 0 означает, что мы не ищем точного соответствия значению.

Функция очень удобна, однако нужно знать некоторые особенности её использования. Во-первых, ВПР ищет только слева направо, так что, если Вам понадобится искать в другом порядке, придется менять параметры сортировки целого листа. Также если Вы выберете слишком большую таблицу, поиск может занять много времени.

2. Функция ИНДЕКС

Отражает значение или ссылку на ячейку на пересечении конкретных строки и столбца в выбранном диапазоне ячеек. Например, чтобы посмотреть, кто стал четвёртым в списке самых высокооплачиваемых топ-менеджеров Уолл-стрит, набираем: =ИНДЕКС(А3:А11, 4).

Крутая фишка Excel, которая сэкономит часы работы!

3. Функция ПОИСКПОЗ

Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне (Диапазон — две или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными) ячеек и отражает относительную позицию этого элемента в диапазоне.

По отдельности ИНДЕКС и ПОИСКПОЗ не особо полезны. Но вместе они могут заменить функцию ВПР.

Например, чтобы в большой таблице найти, кто является главой Wells Fargo, пишем =ИНДЕКС(А3:А11,ПОИСКПОЗ(«Wells Fargo»,B3:B11,0). С помощью функции ВПР этого не сделать, потому что она ищет только слева направо. А сочетание двух последних позволяет сделать это с легкостью.

4. 3D-сумма

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

Теперь на вкладке TOTAL (ИТОГО) нам нужно увидеть, сколько и в какой день Вы потратили за этот период. Набираем =СУММ(‘Week1:Week7’!B2), и формула суммирует все значения в ячейке B2 на всех вкладках. Теперь, заполнив все ячейки, мы выяснили, в какой день недели тратили больше всего, а также в итоге подбили все свои расходы за эти 7 недель.

5. $

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

При этом знак $ перед «А» не даёт программе изменять формулу по горизонтали, а перед «1» – по вертикали. Если же написать «$A$1», то значение скопированной ячейки будет одинаковым в любом направлении. Очень удобный приём, когда приходится работать с большими базами данных.

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

6. ».

7. Массивы

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

Например, давайте перемножим две матрицы. Для этого используем функцию МУМНОЖ (Массив 1, Массив 2). Главное, не забудьте закрыть формулу круглой скобкой. Теперь нажмите сочетание клавиш Ctrl+Shift+Enter, и Excel покажет результат умножения в виде матрицы. То же самое касается и других функций, работающих с массивами, – вместо простого нажатия Enter для получения результата используйте Ctrl+Shift+Enter.

8. Подбор параметра

Без этой функции Excel целым легионам аналитиков, консультантов и прогнозистов пришлось бы туго. Спросите кого угодно из сферы консалтинга или продаж, и Вам расскажут, насколько полезной бывает эта возможность Excel.

Например, Вы занимаетесь продажами новой видеоигры, и Вам нужно узнать, сколько экземпляров Ваши менеджеры должны продать в третьем месяце, чтобы заработать 100 миллионов долларов. Для этого в меню «Инструменты» выберите функцию «Подбор параметра». Нам нужно, чтобы в ячейке Total revenue (Общая выручка) оказалось значение 100 миллионов долларов.

В поле set cell (Установить в ячейке) указываем ячейку, в которой будет итоговая сумма, в поле to value (Значение) – желаемую сумму, а в by Changing cell (Изменяя значение ячейки) выберите ячейку, где будет отображаться количество проданных в третьем месяце товаров. И – вуаля! – программа справилась. Параметрами и значениями в ячейках можно легко манипулировать, чтобы получить нужный Вам результат.

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

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

Сводная таблица – прекрасный способ превратить огромный бесполезный объём информации в удобные для работы данные в рекордно короткие сроки.

Источник: hr-portal.ru

Лабораторная работа № 6 «Специальные методы работы с программой Excel»

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

Рекомендуемая литература: [1–9].

1. Консолидация данных на листе.
2. Создание именованных диапазонов.
3. Поиск решения с помощью подбора параметров.
4. Выделение изменений, внесенных в книгу.
5. Вставка примечаний.
6. Ограничение доступа к документам Excel.

Пример выполнения задания:

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

Как вы уже поняли, на пятый лист будет производиться консолидация данных. Установите курсор в ячейку В4 Лист5 и наберите туда

Установите текстовой курсор между круглыми скобками и откройте Лист1, на нем выберите ячейку В4, после этого нажмите клавишу Shift и, удерживая ее, щелкните левой кнопкой мышки по наименованию Лист4. Получим:

Теперь растянем формулу:

1. Рассмотрим создание именованных диапазонов.

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

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

Имя диапазона… ,

получим диалоговое окно вида:

.

В поле «Имя» введите имя диапазона. Теперь посчитаем средний конкурс на факультете, для этого установим курсор в ячейку В13 и наберем формулу:

1. Поиск решения с помощью подбора параметра.

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

Рассмотрим данную задачу на примере поиска корней уравнения третей степени: 3х 3 +4х 2 -5х-2. Пусть параметром подбора будет х, а искомым результатом 0.

Для начала построим таблицу функции:

.

Из графика видно, что корни лежат в интервалах: [-2,5;-1,5]; [-0,5;0]; [0,5;1,5].

Теперь в ячейку H10 запишем приблизительное значение параметра из первого интервала, а именно «-2,5», а в ячейку F10 запишем нашу формулу относительно заданного параметра. Получим:

Установив курсор на ячейку F10, выполним подбор параметра. Для этого команду «подбор параметра…» надо вынести на панель с помощью настройки панели быстрого доступа, команда имеет вид . Нажмем на кнопку и получим диалоговое окно:

В первом поле ввода «Установить в ячейке:» значение уже заполнено. Во второе поле ввода «Значение:» устанавливается значение которое необходимо получить в результате подбора параметра, в нашем случае 0. В третьем поле «Изменяя значение ячейки:» записывается адрес ячейки, где хранится подбираемый параметр, в нашем случае H10. Нажав кнопку «ОК», получим:

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

Аналогично в ячейках F11 и H11 вычислим второй корень, при начальном значении параметра 0 получим:

Для третьего корня начальное значение параметра равно 1,5, имеем:

Как можно увидеть, наше уравнение имеет три корня: -2, -1/3, 1.

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

РецензированиеИсправленияВыделить исправления.

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

В результате при исправлении таких ячеек вы увидите следующее:

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

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

РецензированиеИсправленияПринять/отклонить исправления.

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

Для установки примечания выделите ячейку, к которой это примечание пишется, и выполните:

РецензированиеПримечаниеСоздать примечание

Вы увидите следующий результат:

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

Если необходимо снять примечание, то вызовите контекстное меню на данной ячейке (щелчок правой кнопки мыши) и выберите пункт «Удалить примечание»:

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

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

Для ограничения доступа к книге выполните команду:

РецензированиеИзмененияЗащитить лист.

Появится диалоговое окно следующего вида:

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

Аналогично можно защитить книгу:

РецензированиеИзмененияЗащитить книгу.

Появится выпадающее меню:

,

там осуществляем выбор вида доступа.

Если вы хотите запретить другим пользователям работать с книгой, то вы полните команду:

РецензированиеИзмененияДоступ к книге.

и на закладке «Правка» уберите флажок с пункта «Разрешить совместный доступ».

Если же вы собираетесь разрешить другим пользователям вносить исправления в свою книгу, то флажок на пункте «Разрешить совместный доступ» следует оставить, а также выполнить настройки на закладке «Подробнее»:

Источник: studfile.net

Специальные методы работы с программой Excel

Откроем новую книгу и сохраним ее с именем «Лабораторная работа №6».

Введем данные на пяти листах.

На пятый лист будет производиться консолидация данных. Установим курсор в ячейку В4 Лист5 и наберите туда

Установим текстовой курсор между круглыми скобками и откроем Лист1, на нем выберем ячейку В4, после этого нажмем клавишу Shift и, удерживая ее, щелкнем левой кнопкой мышки по наименованию Лист4.

Растянем формулу вправо.

Получили сумму ячеек В4 на листах 1-4.

2.Создание именованных диапазонов.

Добавим Лист6 в книгу. Введем данные из примера.

Выделим ячейки В3:В6 и, нажав правую кнопку мыши, выберем из контекстного меню строчку «Присвоить имя».

Введем имя АСФ и нажмем Ок.

Теперь посчитаем средний конкурс на факультете, для этого установим курсор в ячейку В13 и наберем формулу:

=СРЗНАЧА(АСФ) Получим 3,025.

3.Поиск решения с помощью подбора параметров. Задано уравнение второй степени.

Решим его с помощью подбора параметра. Пусть параметром подбора будет х, а искомым результатом 0.

Построим таблицу функции, задав значения х от -4 до 4 с шагом 0,5.

Затем построим график функции по точкам.

Из графика видно, что корни лежат на интервале [–2; 0]

Теперь в ячейку H10 запишем приблизительное значение параметра из первого интервала, а именно –2, а в ячейку F10 запишем нашу формулу относительно заданного параметра.

Установив курсор на ячейку F10, выполним подбор параметра. Для этого команду «подбор параметра…» надо вынести на панель с помощью настройки панели быстрого доступа. Нажмем на кнопку и получим диалоговое окно:

Уравнение имеет один корень.

4. Выделение изменений, внесенных в книгу.

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

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

5. Вставка примечаний.

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

Создадим примечание для ячейки Н10.

6. Ограничение доступа к документам Excel.

Для ограничения доступа к книге необходимо выполнить команду:

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

Аналогично можно защитить книгу:

Источник: znanio.ru