Как в экселе связать ячейки в строке

Как в экселе связать ячейки в строке

Ситуация следующая:
в Екселе создал таблицу с 5 столбцами (пять параметров)
если, например, один из столбцов сортировать по алфавиту, то во всех остальных информация остаётся на своих местах.

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

т.е. как связать все строки, и что бы они двигались вверх\вниз вместе с остальными ячейками этой же строки?

Меню пользователя dvakarandasha

Посмотреть профиль

Отправить личное сообщение для dvakarandasha

Посетить домашнюю страницу dvakarandasha

Найти ещё сообщения от dvakarandasha

3 способа склеить текст из нескольких ячеек

Надпись на заборе: «Катя + Миша + Семён + Юра + Дмитрий Васильевич +
товарищ Никитин + рыжий сантехник + Витенька + телемастер Жора +
сволочь Редулов + не вспомнить имени, длинноволосый такой +
ещё 19 мужиков + муж = любовь!»

Способ 1. Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ

В категории Текстовые есть функция СЦЕПИТЬ (CONCATENATE) , которая соединяет содержимое нескольких ячеек (до 255) в одно целое, позволяя комбинировать их с произвольным текстом. Например, вот так:
Нюанс: не забудьте о пробелах между словами — их надо прописывать как отдельные аргументы и заключать в скобки, ибо текст. Очевидно, что если нужно собрать много фрагментов, то использовать эту функцию уже не очень удобно, т.к. придется прописывать ссылки на каждую ячейку-фрагмент по отдельности. Поэтому, начиная с 2016 версии Excel, на замену функции СЦЕПИТЬ пришла ее более совершенная версия с похожим названием и тем же синтаксисом — функция СЦЕП (CONCAT) . Ее принципиальное отличие в том, что теперь в качестве аргументов можно задавать не одиночные ячейки, а целые диапазоны — текст из всех ячеек всех диапазонов будет объединен в одно целое:
Для массового объединения также удобно использовать новую функцию ОБЪЕДИНИТЬ (TEXTJOIN) , появившуюся начиная с Excel 2016. У нее следующий синтаксис: =ОБЪЕДИНИТЬ( Разделитель ; Пропускать_ли_пустые_ячейки ; Диапазон1 ; Диапазон2 . ) где

• Разделитель — символ, который будет вставлен между фрагментами
• Второй аргумент отвечает за то, нужно ли игнорировать пустые ячейки (ИСТИНА или ЛОЖЬ)
• Диапазон 1, 2, 3 . — диапазоны ячеек, содержимое которых хотим склеить

Например:

Способ 2. Символ для склеивания текста (&)

Это универсальный и компактный способ сцепки, работающий абсолютно во всех версиях Excel.

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

• Этот символ надо ставить в каждой точке соединения, т.е. на всех «стыках» текстовых строк также, как вы ставите несколько плюсов при сложении нескольких чисел (2+8+6+4+8)
• Если нужно приклеить произвольный текст (даже если это всего лишь точка или пробел, не говоря уж о целом слове), то этот текст надо заключать в кавычки. В предыдущем примере с функцией СЦЕПИТЬ о кавычках заботится сам Excel — в этом же случае их надо ставить вручную.

Вот, например, как можно собрать ФИО в одну ячейку из трех с добавлением пробелов:

Если сочетать это с функцией извлечения из текста первых букв — ЛЕВСИМВ (LEFT) , то можно получить фамилию с инициалами одной формулой:

Способ 3. Макрос для объединения ячеек без потери текста.

Имеем текст в нескольких ячейках и желание — объединить эти ячейки в одну, слив туда же их текст. Проблема в одном — кнопка Объединить и поместить в центре (Merge and Center) в Excel объединять-то ячейки умеет, а вот с текстом сложность — в живых остается только текст из верхней левой ячейки.

Чтобы объединение ячеек происходило с объединением текста (как в таблицах Word) придется использовать макрос. Для этого откройте редактор Visual Basic на вкладке Разработчик — Visual Basic (Developer — Visual Basic) или сочетанием клавиш Alt + F11 , вставим в нашу книгу новый программный модуль (меню Insert — Module) и скопируем туда текст такого простого макроса:

Sub MergeToOneCell() Const sDELIM As String = " " 'символ-разделитель Dim rCell As Range Dim sMergeStr As String If TypeName(Selection) <> "Range" Then Exit Sub 'если выделены не ячейки - выходим With Selection For Each rCell In .Cells sMergeStr = sMergeStr & sDELIM & rCell.Text 'собираем текст из ячеек Next rCell Application.DisplayAlerts = False 'отключаем стандартное предупреждение о потере текста .Merge Across:=False 'объединяем ячейки Application.DisplayAlerts = True .Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM)) 'добавляем к объед.ячейке суммарный текст End With End Sub

Теперь, если выделить несколько ячеек и запустить этот макрос с помощью сочетания клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer — Macros) , то Excel объединит выделенные ячейки в одну, слив туда же и текст через пробелы.

Ссылки по теме

• Делим текст на куски
• Объединение нескольких ячеек в одну с сохранением текста с помощью надстройки PLEX
• Что такое макросы, как их использовать, куда вставлять код макроса на VBA

Как в экселе связать ячейки в строке

можно ли связать 2 ячейки стрелкой, чтоб при перемещении одной из ячеек стрелка автоматом перерисовывалась?

Спасибо.
Ответа не нашел( может на правильно делаю поисковый запрос.

можно ли связать 2 ячейки стрелкой, чтоб при перемещении одной из ячеек стрелка автоматом перерисовывалась?

Спасибо.
Ответа не нашел( может на правильно делаю поисковый запрос. vitozet

Сообщение Подскажите пожалуйста.

можно ли связать 2 ячейки стрелкой, чтоб при перемещении одной из ячеек стрелка автоматом перерисовывалась?

Спасибо.
Ответа не нашел( может на правильно делаю поисковый запрос. Автор — vitozet
Дата добавления — 21.02.2015 в 11:45

Группа: Друзья
Ранг: Старожил
Сообщений: 2476
Замечаний: 0% ±

2010
А кнопки влияющие ячейки или зависимые ячейки не устраивают?
А кнопки влияющие ячейки или зависимые ячейки не устраивают? gling

Сообщение А кнопки влияющие ячейки или зависимые ячейки не устраивают? Автор — gling
Дата добавления — 21.02.2015 в 11:52

Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Замечаний: 0% ±

Excel 2010
Цитата gling, 21.02.2015 в 11:52, в сообщении № 2
А кнопки влияющие ячейки или зависимые ячейки не устраивают?

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

Возможно ли ячейки с текстом связать визуально?

Цитата gling, 21.02.2015 в 11:52, в сообщении № 2
А кнопки влияющие ячейки или зависимые ячейки не устраивают?

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

Возможно ли ячейки с текстом связать визуально? vitozet

Цитата gling, 21.02.2015 в 11:52, в сообщении № 2
А кнопки влияющие ячейки или зависимые ячейки не устраивают?

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

Возможно ли ячейки с текстом связать визуально? Автор — vitozet
Дата добавления — 21.02.2015 в 12:13

Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Замечаний: 0% ±

Excel 2010

Можно эти стрелки не рисовать? а чтоб они прокладывались автоматически?
И соответственно при переносе ячейки «Задача» в другое место, переносились и стрелки?

Или в екселе такой примитив не возможен?
Просто в книге в отдельном листе хотел сделать схему.

Можно эти стрелки не рисовать? а чтоб они прокладывались автоматически?
И соответственно при переносе ячейки «Задача» в другое место, переносились и стрелки?

Или в екселе такой примитив не возможен?
Просто в книге в отдельном листе хотел сделать схему.

Сообщение Можно эти стрелки не рисовать? а чтоб они прокладывались автоматически?
И соответственно при переносе ячейки «Задача» в другое место, переносились и стрелки?

Или в екселе такой примитив не возможен?
Просто в книге в отдельном листе хотел сделать схему.

Автор — vitozet
Дата добавления — 21.02.2015 в 14:06

Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
только если рисовать фигуры, а текст в них приматывать к значению в ячейках
только если рисовать фигуры, а текст в них приматывать к значению в ячейках buchlotnik
К сообщению приложен файл: 1896455.xlsx (10.2 Kb)

Сообщение только если рисовать фигуры, а текст в них приматывать к значению в ячейках Автор — buchlotnik
Дата добавления — 21.02.2015 в 14:24

Группа: Модераторы
Ранг: Местный житель
Сообщений: 16620
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Можно не ячейками, а объектами.
Идите на вкладку Вставка — СмартАрт, выбирайте тип.
Примерный итог во вложенном файле.
Что-то в этом роде, только, по-моему, лучше (я точно уже не помню) можно сделать в Ворде.
А вообще — для этого прекрасно подходит Microsoft Visio

Можно не ячейками, а объектами.
Идите на вкладку Вставка — СмартАрт, выбирайте тип.
Примерный итог во вложенном файле.
Что-то в этом роде, только, по-моему, лучше (я точно уже не помню) можно сделать в Ворде.
А вообще — для этого прекрасно подходит Microsoft Visio _Boroda_

К сообщению приложен файл: 4654654544.xlsx (21.6 Kb)

Сообщение Можно не ячейками, а объектами.
Идите на вкладку Вставка — СмартАрт, выбирайте тип.
Примерный итог во вложенном файле.
Что-то в этом роде, только, по-моему, лучше (я точно уже не помню) можно сделать в Ворде.
А вообще — для этого прекрасно подходит Microsoft Visio Автор — _Boroda_
Дата добавления — 21.02.2015 в 14:24

Python, pandas и решение трёх задач из мира Excel

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

Загрузка данных

Начнём с импорта Python-библиотеки pandas и с загрузки в датафреймы данных, которые хранятся на листах sales и states книги Excel. Такие же имена мы дадим и соответствующим датафреймам.

import pandas as pd sales = pd.read_excel('https://github.com/datagy/mediumdata/raw/master/pythonexcel.xlsx', sheet_name = 'sales') states = pd.read_excel('https://github.com/datagy/mediumdata/raw/master/pythonexcel.xlsx', sheet_name = 'states') 

Теперь воспользуемся методом .head() датафрейма sales для того чтобы вывести элементы, находящиеся в начале датафрейма:

print(sales.head()) 

Сравним то, что будет выведено, с тем, что можно видеть в Excel.

Сравнение внешнего вида данных, выводимых в Excel, с внешним видом данных, выводимых из датафрейма pandas

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

• Нумерация строк в Excel начинается с 1, а в pandas номер (индекс) первой строки равняется 0.
• В Excel столбцы имеют буквенные обозначения, начинающиеся с буквы A , а в pandas названия столбцов соответствуют именам соответствующих переменных.

Реализация возможностей Excel-функции IF в Python

В Excel существует очень удобная функция IF , которая позволяет, например, записать что-либо в ячейку, основываясь на проверке того, что находится в другой ячейке. Предположим, нужно создать в Excel новый столбец, ячейки которого будут сообщать нам о том, превышают ли 500 значения, записанные в соответствующие ячейки столбца B . В Excel такому столбцу (в нашем случае это столбец E ) можно назначить заголовок MoreThan500 , записав соответствующий текст в ячейку E1 . После этого, в ячейке E2 , можно ввести следующее:

=IF([@Sales]>500, "Yes", "No") 

Использование функции IF в Excel

Для того чтобы сделать то же самое с использованием pandas, можно воспользоваться списковым включением (list comprehension):

sales['MoreThan500'] = ['Yes' if x > 500 else 'No' for x in sales['Sales']] 

Списковые включения в Python: если текущее значение больше 500 — в список попадает Yes, в противном случае — No

Списковые включения — это отличное средство для решения подобных задач, позволяющее упростить код за счёт уменьшения потребности в сложных конструкциях вида if/else. Ту же задачу можно решить и с помощью if/else, но предложенный подход экономит время и делает код немного чище. Подробности о списковых включениях можно найти здесь.

Реализация возможностей Excel-функции VLOOKUP в Python

В нашем наборе данных, на одном из листов Excel, есть названия городов, а на другом — названия штатов и провинций. Как узнать о том, где именно находится каждый город? Для этого подходит Excel-функция VLOOKUP , с помощью которой можно связать данные двух таблиц. Эта функция работает по принципу левого соединения, когда сохраняется каждая запись из набора данных, находящегося в левой части выражения. Применяя функцию VLOOKUP , мы предлагаем системе выполнить поиск определённого значения в заданном столбце указанного листа, а затем — вернуть значение, которое находится на заданное число столбцов правее найденного значения. Вот как это выглядит:

=VLOOKUP([@City],states,2,false) 

Зададим на листе sales заголовок столбца F как State и воспользуемся функцией VLOOKUP для того чтобы заполнить ячейки этого столбца названиями штатов и провинций, в которых расположены города.

Использование функции VLOOKUP в Excel

В Python сделать то же самое можно, воспользовавшись методом merge из pandas. Он принимает два датафрейма и объединяет их. Для решения этой задачи нам понадобится следующий код:

sales = pd.merge(sales, states, how='left', on='City') 

1. Первый аргумент метода merge — это исходный датафрейм.
2. Второй аргумент — это датафрейм, в котором мы ищем значения.
3. Аргумент how указывает на то, как именно мы хотим соединить данные.
4. Аргумент on указывает на переменную, по которой нужно выполнить соединение (тут ещё можно использовать аргументы left_on и right_on , нужные в том случае, если интересующие нас данные в разных датафреймах названы по-разному).

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

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

Создание сводной таблицы в Excel

Как видите, для создания подобной таблицы достаточно перетащить поле City в раздел Rows , а поле Sales — в раздел Values . После этого Excel автоматически выведет суммарные продажи для каждого города.

Для того чтобы создать такую же сводную таблицу в pandas, нужно будет написать следующий код:

sales.pivot_table(index = 'City', values = 'Sales', aggfunc = 'sum') 

1. Здесь мы используем метод sales.pivot_table , сообщая pandas о том, что мы хотим создать сводную таблицу, основанную на датафрейме sales .
2. Аргумент index указывает на столбец, по которому мы хотим агрегировать данные.
3. Аргумент values указывает на то, какие значения мы собираемся агрегировать.
4. Аргумент aggfunc задаёт функцию, которую мы хотим использовать при обработке значений (тут ещё можно воспользоваться функциями mean , max , min и так далее).

Итоги

Из этого материала вы узнали о том, как импортировать Excel-данные в pandas, о том, как реализовать средствами Python и pandas возможности Excel-функций IF и VLOOKUP , а также о том, как воспроизвести средствами pandas функционал сводных таблиц Excel. Возможно, сейчас вы задаётесь вопросом о том, зачем вам пользоваться pandas, если то же самое можно сделать и в Excel. На этот вопрос нет однозначного ответа. Python позволяет создавать код, который поддаётся тонкой настройке и глубокому исследованию. Такой код можно использовать многократно. Средствами Python можно описывать очень сложные схемы анализа данных. А возможностей Excel, вероятно, достаточно лишь для менее масштабных исследований данных. Если вы до этого момента пользовались только Excel — рекомендую испытать Python и pandas, и узнать о том, что у вас из этого получится.

А какие инструменты вы используете для анализа данных?

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