Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как выбрать конкретный лист на основе значения ячейки на другом листе в Excel?

Властелин таблиц: 10 малоизвестных фишек для бизнеса в Excel H&F разобрался, как предпринимателю перестать бояться формул Excel и начать считать всё, что хочется.

Властелин таблиц: 10 малоизвестных фишек для бизнеса в Excel

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

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

/>

Excel позволяет установить постоянно обновляемый курс обмена валют.

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

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

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

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

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

На вашем листе курс будет отображаться в ячейках.

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

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

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

Предположим, вы хотите скрыть некоторые листы в Excel от других пользователей, работающих над книгой. Традиционный способ — щелкните правой кнопкой мыши на ярлыке листа и выберите «Скрыть» (рисунок 1) — оставляет название скрытого листа видимым для других людей. Вот шаги для полного скрытия:

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

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

Читайте так же:
Как вставить таблицу Excel в слово и сделать содержимое таблицы подходящим для страницы?

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

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

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

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

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

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

— В колонке «Формула» введите =A2=ЦЕНТР().

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

— Нажмите кнопку OK. Если теперь человек хочет ввести другую дату, появится предупреждающее сообщение (изображение 3).

Столбцу количества также можно запретить изменять свои числа. Щелкните ячейку с количеством и повторите процесс.

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

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

— Выделите ячейки A1:A10, на которые повлияет запрет.

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

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

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

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

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

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

Читайте так же:
Как выделить повторяющиеся значения разными цветами в Excel?

— В ячейке G4 введите имя клиента ANTON.

В категории «Мясо бостонского краба» название продукта — Cell G5.

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

Ищет упоминания Антона в указанном списке клиентов (C3:C21=G4).

Что касается Boston Crab Meat, то второй множитель (B3:B21=G5) также работает в качестве множителя.

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

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

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

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

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

— В появившемся окне нажмите «OK» (рисунок 2).

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

Товарный чек

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

— Выделяется клетка С7.

— Ассортимент B2:B5 выделяется на фоне остальных.

— Введите звездочку, которая в Excel представляет собой знак умножения.

Читайте так же:
Как добавить указатель мыши к определенной форме в Excel?

— Выберите диапазон С2:С5 и закройте скобку (изображение 2).

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

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

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

Сделайте новую страницу в книге и скопируйте списки товаров из обоих прайс-листов (рисунок 3).

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

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

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

— Нажимаем OK и из ячейки D4 получаем список без дубликатов (Рисунок 6).

— Удалите исходный список продуктов.

— Добавьте колонки для загрузки значений цен за 4 и 11 мая, а также колонку сравнения.

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

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

» Searchable_value» — это строка, которую мы будем искать в таблице цен. Введя ее название, вы сможете легко находить товары (рисунок 8).

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

Читайте так же:
Как вставить картинку или изображение в комментарий в Excel?

Как следует из названия, «Column_number» — это номер в диапазоне столбцов, в которых мы осуществляем поиск. Для поиска мы задали таблицу из двух столбцов. Второй (рисунок 10) содержит цену.

— interval_view. Для таблиц, в которых значения сортируются по возрастанию или убыванию, установите значение TRUE, в противном случае установите значение FALSE.

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

— В колонке «Итого» указана разница в цене товаров в двух прейскурантах. Если в колонке «Итого» указано #N/D, это означает, что указанный товар имеется только в одном из прайс-листов, и поэтому разница не может быть рассчитана.

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

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

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

— Пятая строка показывает расчет дисконтированных потоков в каждом периоде в соответствии с двумя различными формулами.

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

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

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

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

Читайте так же:
Как добавить знак плюса к нескольким положительным числам в Excel?

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector